OceanBase Cloud快速入门第33期：如何更新数据？

分布式数据库爱好者 2023-04-10

817

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

插入表数据后，可以使用 UPDATE 语句或其他语句来更新表的行记录。本文介绍了相关语句的使用方法和示例。

数据更新准备

在更新表数据前，请确认以下事项：

请确认您已连接到数据库的 MySQL 租户，连接数据库的操作请参见连接数据库。
说明
当前登录租户所属的租户模式可以由 sys 租户通过查询 oceanbase.DBA_OB_TENANTS 视图进行确认。
请确认您已拥有待操作的表的 UPDATE 权限。

使用 UPDATE 语句更新数据

通常使用 UPDATE 语句来更新表数据。

UPDATE 语句的语法格式如下：

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];

参数	是否必填	描述
table_name	是	指定需要更新数据的表。
column_name = value [, column_name = value]	是	指定需要更新的列，等号（=）后面的 value 是更新的目标值。
[ WHERE condition ]	否	条件子句指定要更新的行记录必须满足的条件。如果不填表示更新表对应列的所有记录。

更新所有记录

更新 t_insert 表中 value 列的所有值，并将所有值加 1。示例如下：

obclient [test]> UPDATE t_insert SET value = value+1;
Query OK, 4 rows affected 
Rows matched: 4  Changed: 4  Warnings: 0

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

执行 UPDATE 语句时要注意控制事务不要太大，您可以通过 LIMIT 关键字来控制数量或者通过 WHERE 关键字来控制范围。这是因为不带条件更新数据时，如果记录数达到几十万或者几百万，会有大事务产生，可能会导致执行失败。因事务过大导致的具体报错信息如下所示。

obclient [test]> UPDATE t_insert SET value = value+1;
ERROR 4012 (HY000): Timeout

更新部分记录

在更新表数据时，也可以只更新部分记录。假设 t_insert 表中的记录数较大，下面通过添加 LIMIT 关键字来控制数量或者通过添加 WHERE 关键字来控制范围。

示例：

通过 LIMIT 在 t_insert 表中，更新前 100000 行数据 value 列的值为 value+1。
```
obclient [test]> UPDATE t_insert SET value = value+1 LIMIT 100000;
```
通过 where 在 t_insert 表中，对 value 值为 10005 的所有列，将其值更新为 value+1。
```
obclient [test]>  UPDATE t_insert SET value = value+1 where value = 10005;
```

t_insert 表中更新 id = 3 所在行的 name 列的值为 UK。

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> UPDATE t_insert SET name = 'UK' WHERE id = 3;
Query OK, 0 rows affected 

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | US   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

更新记录唯一性约束冲突

当表上有唯一性约束时，更新为相同的记录，系统会报错，数据更新失败，报错示例如下。

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 17:18:06 |
|  2 | US   | 10003 | 1970-01-01 17:18:47 |
|  3 | EN   | 10004 | 1970-01-01 17:18:47 |
|  4 | JP   | 10005 | 1970-01-01 17:28:21 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> CREATE UNIQUE INDEX uk_name ON t_insert(name);
Query OK, 0 rows affected 

obclient [test]> UPDATE t_insert SET name = 'US' where id = 3;
ERROR 1062 (23000): Duplicate entry 'US' for key 'uk_name'

使用其他语句更新数据

除了显式的 UPDATE 语句外，还有其他语句也可以更新数据。例如，通过 INSERT 语句插入数据时，由于约束冲突，可以使用 ON DUPLICATE KEY UPDATE 子句将插入数据转变为更新数据的语句来更新相关字段。

使用 ON DUPLICATE KEY UPDATE 子句将插入数据转变为更新数据，示例如下：

  obclient [test]> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
  |  2 | US   | 10002 | 2022-10-12 16:29:16 |
  |  3 | EN   | 10003 | 2022-10-12 16:29:26 |
  |  4 | JP   | 10004 | 2022-10-12 17:02:52 |
  +----+------+-------+---------------------+
  4 rows in set

obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(5, 'CN', 10005) ON DUPLICATE KEY UPDATE name = VALUES(name);
Query OK, 1 row affected 

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 16:29:16 |
|  2 | US   | 10002 | 2022-10-12 15:17:17 |
|  3 | UK   | 10003 | 2022-10-12 16:29:26 |
|  4 | JP   | 10004 | 2022-10-12 17:02:52 |
|  5 | CN   | 10005 | 2022-10-12 17:27:46 |
+----+------+-------+---------------------+
5 rows in set

示例中，ON DUPLICATE KEY UPDATE name = VALUES(name) 即表示当插入的数据与表中的主键值有重复时，将表中冲突行原数据中 (3,'EN', 10003) 的 name 列的值更新为当前待插入的 name 列的值。