ci mysql update_MySQL 的更新语句

朴灿烈づ我的快乐病毒、 2023-01-12 12:00 119阅读 0赞

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

先来看单表更新的语法：sql

UPDATE \[LOW\_PRIORITY\] \[IGNORE\] table\_reference

SET assignment\_list

\[WHERE where\_condition\]

\[ORDER BY ...\]

\[LIMIT row\_count\]

你们可能会以为奇怪，在更新语句中竟然能用 ORDER BY 子句和 LIMIT 子句。没错，ORDER BY 子句用来指定数据行的更新顺序，LIMIT 子句限制数据更新的行数。优化

咱们结合例子来看，建立一张 test 表用来演示，它的表结构及数据以下：code

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 。blog

先看 LIMIT 子句的使用。ci

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 子句。get

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 列的值，也只更新两行，结果和咱们预期的一致。it

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 子句影响的行数。io

好比，咱们重复执行上面的更新语句，但 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 社区反馈，只是到了如今还没处理。

![aa0f821f2158008720256c8efcd825b5.png][]

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

![597c6cd319649a450c56f865659fa276.png][]

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

方法一：

UPDATE

test

SET

col1 = col1 \* 10

WHERE id IN

(SELECT

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

[aa0f821f2158008720256c8efcd825b5.png]: /images/20221119/e07a092530a9424791e5f20bce705b1c.png
[597c6cd319649a450c56f865659fa276.png]: /images/20221119/31311b176d954e2eb594f71214e2a81f.png

ci mysql update_MySQL 的更新语句

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 PDO：使用准备好的语句更新 MySQL

相关 ci mysql update_MySQL 的更新语句

相关 mysql常用语句-更新中

相关 binlog流程 mysql_mysql┃一条更新语句是怎么执行的？？？

相关 MySQL常用SQL语句（持续更新中。。。）

相关 mysql更新语句使用order by

相关 CI数据库语句查询

相关透视MySQL数据库之更新语句

相关【Mysql】 update语句更新原理

相关 mysql更新，修改语句汇总--update set,alter