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,
mysql语句顺序`col1`int DEFAULT NULL,
`col2`int DEFAULT NULL,
`col3`varchar(32)DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=6DEFAULT CHARSET=utf8;
id col1 col2 col3
------ ------ ------ --------
1110 hello
doubleheader2120 world
3130 world
4140 nice
5150 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
------ ------ ------ --------
1210 hello
2220 world
3130 world
4140 nice
5150 hello
再来看 ORDER BY ⼦句。
UPDATE
test
SET
col1 =3
ORDER BY id DESC
LIMIT2;
---------------------------------------------------
1 queries executed,1 success,0errors,0warnings
查询：update test set col1 =3order by id desc limit2
共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 ⼦句影响的⾏数。
⽐如，我们重复执⾏上⾯的更新语句，但 test 表的数据⼀点也没变。
UPDATE
test
SET
col1 =3
ORDER BY id DESC
LIMIT2;
---------------------------------------------------
1 queries executed,1 success,0errors,0warnings
查询：update test set col1 =3order by id desc limit2
共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 l2 = b.col2 *10
l3 ='hello';
-- 等价于下⾯的写法
UPDATE
test a,
test b
SET
WHERE b.id = a.id
l3 ='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
float类型能表示的最大正整数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
duration读音WHERE b.id = a.id
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论