UPDATE语句用来更新表的行记录。

简单的UPDATE语句语法格式如下:

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

其中,column_name 是要更新的列,等号后面的 value 是要更新的目标值,必须符合列的类型定义。WHERE 条件子句指定要更新的行记录必须满足的条件,没有 WHERE 条件子句就是更新表对应列的所有记录。

示例:更新所有记录

  1. obclient> update t_insert set value=value+1 ;
  2. Query OK, 3 rows affected (0.01 sec)
  3. Rows matched: 3 Changed: 3 Warnings: 0

不带条件更新的时候,如果记录数达到几十万或者几百万,会有大事务产生,可能会失败。所以 UPDATE 要注意控制事务不要太大。

示例:更新部分记录,违反约束报错

  1. obclient> create unique index uk_name on t_insert(name);
  2. Query OK, 0 rows affected (1.99 sec)
  3. obclient> update t_insert set name='US' where id=3;
  4. ERROR-00001: unique constraint 'US' for key 'UK_NAME' violated

除了显式的 UPDATE 语句外,还有几类语句也可以更新数据。比如说 INSERT 因为约束冲突失败的时候,可以使用ON DUPCLICATE KEY UPDATE 子句转变为 UPDATE 语句更新相关字段。

关于 INSERT ON DUPLICATE KEY UPDATE 子句

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

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

  1. obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003);
  2. ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
  3. obclient>
  4. obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003)
  5. ON DUPLICATE KEY UPDATE name='UK', value=10003 ;
  6. Query OK, 2 rows affected (0.01 sec)
  7. obclient> select * from t_insert;
  8. +----+------+-------+---------------------+
  9. | id | name | value | gmt_create |
  10. +----+------+-------+---------------------+
  11. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
  12. | 2 | US | 10002 | 2020-04-03 16:05:54 |
  13. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
  14. | 4 | JP | 10004 | 2020-04-03 16:06:08 |
  15. +----+------+-------+---------------------+
  16. 4 rows in set (0.00 sec)

关于 SELECT … FOR UPDATE 子句

使用 SELECT … FOR UPDATE 可以在读取记录的时候就对记录加锁,避免其他 DML 语句对该笔记录进行同时修改,这种设计通常也称为“悲观锁策略”。

示例:使用 SELECT … FOR UPDATE 先锁定记录后修改

  1. obclient> select id,name,value from account where id=2 for update;
  2. +----+------+-------+
  3. | id | name | value |
  4. +----+------+-------+
  5. | 2 | B | 1000 |
  6. +----+------+-------+
  7. 1 row in set (0.00 sec)
  8. obclient> update account set value=value+100 where id=2;
  9. Query OK, 1 row affected (0.00 sec)
  10. Rows matched: 1 Changed: 1 Warnings: 0
  11. obclient> commit;
  12. Query OK, 0 rows affected (0.00 sec)