关于 UPDATE 语句

UPDATE 语句用来更新表的行记录，本文介绍了相关语句的语法和示例。

UPDATE 语句

UPDATE 语句语法格式如下：

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

示例

更新所有记录

以下示例表示更新 t_insert 表中 value 列的所有值 +1。

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

obclient> 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> UPDATE analog SET ach=ach+1;
  ERROR 4012 (HY000): Timeout
  

• 通过 limit 控制单次事务更新的行数，如下示例，在 analog 表中更新前 100000 行数据的 ach 值为 ach+1。

  UPDATE analog SET ach=ach+1 LIMIT 100000;
  

• 通过 where 筛选分批更新：

  UPDATE analog SET ach=ach+1 where ach=64;
  

更新部分记录，违反约束报错

当表上有唯一性约束的时候，更新相同的记录，数据库会报错。

obclient> CREATE unique index uk_name ON t_insert(name);
Query OK, 0 rows affected 

obclient> 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 子句转变为 UPDATE 语句更新相关字段。

使用 INSERT ON DUPLICATE KEY UPDATE 避免数据插入冲突

使用 ON DUPLICATE KEY UPDATE 子句时，要求表上面要有主键或唯一约束（索引）。

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003)
  ON DUPLICATE KEY UPDATE name='UK', value=10003 ;
Query OK, 2 rows affected 

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10002 | 1970-01-01 18:05:45 |
|  2 | US   | 10003 | 1970-01-01 18:05:54 |
|  3 | UK   | 10003 | 1970-01-01 18:05:54 |
|  4 | JP   | 10005 | 1970-01-01 18:06:08 |
+----+------+-------+---------------------+
4 rows in set