今天上来发个MySQL 的更新语句

本文将和大家分享 MySQL 更新语句的一些小众语法，及笔者在使用多表关联更新遇到的一些问题。

先来看单表更新的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

大家可能会觉得奇怪，在更新语句中居然能用 ORDER BY 子句和 LIMIT 子句。没错，ORDER BY 子句用来指定数据行的更新顺序，LIMIT 子句限制数据更新的行数。

我们结合例子来看，创建一张 test 表用来演示，它的表结构及数据如下：

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  `col3` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


    id    col1    col2  col3    
------  ------  ------  --------
     1       1      10  hello   
     2       1      20  world   
     3       1      30  world   
     4       1      40  nice    
     5       1      50  hello   

test 表有 5 行数据，其中 col1 列的值完全一样，都是数值 1 。

先看 LIMIT 子句的使用。

UPDATE 
  test 
SET
  col1 = 2 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：update test set col1 = 2 limit 2

共 2 行受到影响

上面的语句将 col2 列的值改为数值 2，但是只改变其中的两行。我们通过观察执行更新后的 test 表的数据，确实只更新了两行。

 

   id    col1    col2  col3    
------  ------  ------  --------
     1       2      10  hello   
     2       2      20  world   
     3       1      30  world   
     4       1      40  nice    
     5       1      50  hello   

再来看 ORDER BY 子句。

UPDATE 
  test 
SET
  col1 = 3 
ORDER BY id DESC 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：update test set col1 = 3 order by id desc limit 2

共 2 行受到影响

这回我们指定了按照 id 列的逆序更新 col1 列的值，也只更新两行，结果和我们预期的一致。

   

id    col1    col2  col3    
------  ------  ------  --------
     1       2      10  hello   
     2       2      20  world   
     3       1      30  world   
     4       3      40  nice    
     5       3      50  hello   

不过，需要注意的是，如果更新的行的原来的值和要更新的值一致，那么 MySQL 并不会真正执行更新操作，但仍会计入受 LIMIT 子句影响的行数。

比如，我们以名笔法律网（www.mingbi.net）为例子重复执行上面的更新语句，但 test 表的数据一点也没变。

UPDATE 
  test 
SET
  col1 = 3 
ORDER BY id DESC 
LIMIT 2;

---------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings

查询：update test set col1 = 3 order by id desc limit 2

共 0 行受到影响

另外，ORDER BY 子句和 LIMIT 子句不能用在多表关联更新语句中。

看下面这个例子，是关于列的更新顺序。对于单表的更新，执行顺序通常是从左到右。

UPDATE 
  test a 
SET
  col1 = col1 * 10,
  col2 = col1 
WHERE id = 1; 

猜猜看，上面这条更新语句，执行之后 id = 1 的行的 col2 字段的值是等于 col1 更新前的值，还是更新后的值？

答案是后者，即更新后的值。这和标准 SQL 不太一样。

再来看多表关联更新的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

注意，如果多表关联通过 JOIN 来实现，而不是把关联的条件放到 WHERE 子句中，那么 JOIN 子句要放在 SET 子句之前。

UPDATE 
  test a 
  INNER JOIN test b 
    ON b.id = a.id SET a.col2 = b.col2 * 10 
WHERE a.col3 = 'hello';

-- 等价于下面的写法
UPDATE 
  test a,
  test b 
SET
  a.col2 = b.col2 * 10 
WHERE b.id = a.id 
  AND a.col3 = 'hello' ;

有时候执行多表关联更新时会遇到 ERROR 1093 (HY000): You can't specify target table 'xxx' for update in FROM clause 这个错误提示，其实不止更新语句，删除语句也会有这个问题。

这个问题是怎么产生的呢？实际上是因为要更新的目标表同时存在子查询里面，请看下面这个例子。

UPDATE 
  test 
SET
  col1 = col1 * 10 
WHERE id IN 
  (SELECT 
    MIN(id) AS id 
  FROM
    test 
  GROUP BY col3 
  HAVING COUNT(*) = 1);
  
---------------------------------------------------------------
错误代码： 1093
You can't specify target table 'test' for update in FROM clause
这个问题很早就存在了，在 2006 年的时候就有用户向 MySQL 社区反馈，只是到了现在还没处理。

好消息是 MariaDB 在 10.3.2 版本开始支持这类更新语句，相信在 MySQL 后续的版本中，也会加入这一支持。

这个问题在现阶段怎么解决呢？官方文档给出的建议是使用派生表（在 FROM 子句后面可替代表的子查询称作派生表）。

方法一：

UPDATE 
  test 
SET
  col1 = col1 * 10 
WHERE id IN 
  (SELECT 
    id 
  FROM
    (SELECT 
      MIN(id) AS id 
    FROM
      test 
    GROUP BY col3 
    HAVING COUNT(*) = 1) t)

这种改写方式能凑效是因为 MySQL 的优化器将派生表物化了（物化的操作可理解为将查询结果存到内部临时表中），因此更新的目标表和子查询里面的表就不是同一个。

方法二：

UPDATE 
  test a,
  (SELECT 
    MIN(id) AS id 
  FROM
    test 
  GROUP BY col3 
  HAVING COUNT(*) = 1) b 
SET
  col1 = col1 * 10 
WHERE b.id = a.id