OceanBase Cloud快速入门第35期：如何替换数据？

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/

可以使用 REPLACE INTO 语句来插入或者更新数据，本文介绍了相关语句的使用方法和示例。

数据替换准备

在替换表数据前，请确认以下事项：

• 请确认您已连接到数据库的 MySQL 租户，连接数据库的操作请参见 连接数据库。

  说明

  当前登录租户所属的租户模式可以由 sys 租户通过查询 oceanbase.DBA_OB_TENANTS 视图进行确认。

• 请确认您已拥有待操作的表的 INSERT、UPDATE 和 DELETE 权限。

替换数据

通常使用 REPLACE INTO 语句替换表中的一条或多条记录。

REPLACE INTO 语句语法格式如下：

REPLACE INTO table_name VALUES(list_of_values);

REPLACE INTO 语句会根据替换数据与表的主键或唯一键进行判断：

• 如果没有主键或唯一键冲突，则插入记录。

• 如果存在主键或唯一键冲突，则先删除已有记录，再插入新行记录。

  说明

  目标表建议有主键或者唯一索引，否则容易插入重复的记录。

表中无数据记录或者有数据记录但无主键、无唯一键冲突

当表中无数据记录，或者表中有数据记录但无主键或唯一键冲突时，可以使用 REPLACE INTO 语句替换数据，结果等价于通过 INSERT 语句插入数据。

示例：

• 创建 t_replace 表后，使用 REPLACE INTO 语句插入一行数据。

  obclient [test]> CREATE TABLE t_replace(
      id number NOT NULL PRIMARY KEY
      , name varchar(10) NOT NULL
      , value number
      ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
  );
  Query OK, 0 rows affected 
  
  obclient [test]> REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
  Query OK, 1 row affected 
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  +----+------+-------+---------------------+
  1 row in set
  

  从示例结果可以看出，t_replace 表创建后未插入数据，执行 REPLACE INTO 语句后，表中插入了一条记录。

• 再次使用 REPLACE INTO 语句，插入一行数据。

  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  +----+------+-------+---------------------+
  1 row in set
  
  obclient [test]> REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'US',2002,current_timestamp ());
  Query OK, 1 row affected 
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  +----+------+-------+---------------------+
  2 rows in set
  

  从示例结果可以看出，t_replace 表中已有一条记录，由于 (2,'US',2002,current_timestamp ()) 数据与表内的记录不违反唯一性约束，故执行结果是在 t_replace 表中插入一条记录。

• 使用查询语句充当 REPLACE INTO 语句的 VALUES 子句来插入多条数据。将表 t_insert 中的数据插入到表 t_replace 中。

  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  +----+------+-------+---------------------+
  2 rows in set
  
  obclient [test]> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  7 | EN   |  1007 | 2022-10-13 14:36:36 |
  |  8 | JP   |  1008 | 2022-10-13 14:36:36 |
  +----+------+-------+---------------------+
  2 rows in set
  
  obclient [test]> REPLACE INTO t_replace
       SELECT id,name,value,gmt_create FROM t_insert;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  |  7 | EN   |  1007 | 2022-10-13 14:36:36 |
  |  8 | JP   |  1008 | 2022-10-13 14:36:36 |
  +----+------+-------+---------------------+
  4 rows in set
  

表中有数据记录且存在主键或唯一键冲突

当表中有数据记录且插入的数据存在主键或唯一键冲突时，使用 REPLACE INTO 语句可以将表中已有的存在冲突的数据删除，替换为新的数据。

在 t_replace 表中插入一条记录，示例如下：

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-10-13 14:06:58 |
|  2 | US   |  2002 | 2022-10-13 14:17:56 |
|  7 | EN   |  1007 | 2022-10-13 14:36:36 |
|  8 | JP   |  1008 | 2022-10-13 14:36:36 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'EN',2002,current_timestamp ());
Query OK, 2 rows affected

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-10-13 14:06:58 |
|  2 | EN   |  2002 | 2022-10-13 14:44:33 |
|  7 | EN   |  1007 | 2022-10-13 14:36:36 |
|  8 | JP   |  1008 | 2022-10-13 14:36:36 |
+----+------+-------+---------------------+
4 rows in set

在示例中，由于表 t_replace 的 id 列为主键列，需要满足唯一性约束，而插入的数据 (2,'EN',2002,current_timestamp ()) 违反唯一性约束，系统删除了原来的行记录 (2,'US',2002,current_timestamp ())，插入 (2,'EN',2002,current_timestamp ()) 作为新的行记录。

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/