13.2.9 SELECT 语法

SELECT用于检索从一个或多个表中选择的行，并且可以包含UNION语句和子查询。

SELECT也可用于检索不参考任何表而计算出的行。

例如：

mysql> SELECT 1 + 1;
-> 2

在没有引用表的情况下，您可以将DUAL指定为虚拟表名

mysql> SELECT 1 + 1 FROM DUAL;
-> 2

DUAL纯粹是为了方便那些要求所有SELECT语句应该有FROM和其他可能的子句的人。 MySQL可能会忽略这些子句。如果没有引用表，MySQL不需要FROM DUAL。

通常，使用的子句必须按照语法描述中显示的顺序给出。例如，HAVING子句必须位于任何GROUP BY子句之后，并且位于任何ORDER BY子句之前。例外情况是INTO子句可以出现在语法描述中或紧跟在select_expr列表之后。

以下列表提供了有关其他SELECT子句的附加信息：

可以使用AS alias_name给select_expr一个别名。别名用作表达式的列名称，可用于GROUP BY，ORDER BY或HAVING子句。例如：

SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;

使用标识符对select_expr进行别名时，AS关键字是可选的。前面的例子可能是这样写的：
SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name;

但是，由于AS是可选的，因此如果忘记两个select_expr表达式之间的逗号，就会出现一个细微问题：MySQL将第二个解释为别名。例如，在以下语句中，columnb被视为别名：

SELECT columna columnb FROM mytable;
出于这个原因，在指定列别名时使用AS是一种好习惯。

在ORDER BY和GROUP BY子句中选择用于输出的列可以使用列名，列别名或列位置引用。列位置是整数并以1开头：

SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;
SELECT college, region, seed FROM tournament ORDER BY 2, 3;

如果ORDER BY发生在子查询中并且也被应用于外部查询中，则最外面的ORDER BY优先。例如，以下语句的结果按降序排列，而不是升序：

(SELECT ... ORDER BY a) ORDER BY a DESC;

不赞成使用列位置，因为语法已从SQL标准中删除。

如果使用GROUP BY，则输出行将根据GROUP BY列进行排序，就像您对同一列有ORDER BY一样。为了避免该GROUP BY产生的排序开销，添加ORDER BY NULL：

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

依靠隐式GROUP BY排序（即，不存在ASC或DESC标识符进行排序）已弃用。要生成给定的排序顺序，请为GROUP BY列使用显式的ASC或DESC指示符，或者提供ORDER BY子句。

当您使用ORDER BY或GROUP BY对SELECT中的列进行排序时，服务器仅使用max_sort_length系统变量指示的初始字节数排序值。
MySQL扩展了GROUP BY子句，以便您还可以在子句中命名的列之后指定ASC和DESC：

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;

MySQL扩展了GROUP BY的用法，允许选择在GROUP BY子句中未提及的字段。如果您没有得到您期望的查询结果，请阅读第12.19节“聚合（GROUP BY）函数”中的GROUP BY描述。
HAVING子句几乎是在发送给客户端之前的几乎最后一个应用，没有优化。（LIMIT 在HAVING之后应用。）

SQL标准要求HAVING必须仅引用GROUP BY子句中的列或聚合函数中使用的列。但是，MySQL支持此行为的扩展，并允许HAVING引用SELECT列表中的列和外部子查询中的列。

如果HAVING子句引用不明确的列，则会发生警告。在下面的语句中，col2是不明确的，因为它用作别名和列名称：

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

优先考虑标准的SQL行为，因此如果在GROUP BY中使用HAVING列名称并在输出列表中使用HAVING列名称作为别名列，则优先考虑GROUP BY列中的列。

不要对应该在WHERE子句中的项目使用HAVING。例如，不要写如下的内容：

SELECT col_name FROM tbl_name HAVING col_name > 0;

而是写成：
SELECT col_name FROM tbl_name WHERE col_name > 0;

HAVING子句可以引用集合函数，WHERE子句不能：

SELECT user, MAX(salary) FROM users GROUP BY user
HAVING MAX(salary) > 10;
MySQL允许重复列名。也就是说，可以有多个具有相同名称的select_expr。这是对标准SQL的扩展。因为MySQL也允许GROUP BY和HAVING引用select_expr值，所以这可能会导致一个不明确的情况：

SELECT 12 AS a, a FROM t GROUP BY a;

在该语句中，两列都有名字a。要确保使用正确的列进行分组，请为每个select_expr使用不同的名称。

LIMIT子句可用于限制SELECT语句返回的行数。 LIMIT需要一个或两个数字参数，它们都必须是非负整数常量，但有以下例外：

在准备好的语句中，可以使用？指定LIMIT参数。占位符标记。

在存储的程序中，可以使用整数值例程参数或局部变量来指定LIMIT参数。

有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为0（不是1）：

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

要从某个偏移量直到结果集的末尾检索所有行，可以使用一些大数目作为第二个参数。该语句检索从第96行到最后一行的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用一个参数，该值指定了从结果集开头返回的行数：

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

换句话说，LIMIT row_count等于LIMIT 0，row_count。

对于准备好的语句，您可以使用占位符。以下语句将从tbl表中返回一行：
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;

以下语句将从tbl表中返回第二行到第六行：

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

为了与PostgreSQL兼容，MySQL还支持LIMIT row_count OFFSET 语法。

如果LIMIT发生在子查询中并且也被应用于外部查询中，则最外部的LIMIT优先。例如，以下语句生成两行，而不是一行：

(SELECT ... LIMIT 1) LIMIT 2;

PROCEDURE子句命名应处理结果集中的数据的过程。有关示例，请参见第8.4.2.4节“使用PROCEDURE ANALYZE”，其中描述了ANALYZE，存储过程可用于获取有助于减小表大小的最佳列数据类型的建议。

不允许在UNION语句中使用PROCEDURE子句

注意：
PROCEDURE语法从MySQL 5.7.18开始已被弃用，并且在MySQL 8.0中被删除。

SELECT ... INTO形式的SELECT使得查询结果可以写入文件或存储在变量中。
如果对使用页锁或行锁的存储引擎使用FOR UPDATE，则查询检查的行将被写锁定，直到当前事务结束。使用LOCK IN SHARE MODE设置一个共享锁，允许其他事务读取已检查的行，但不更新或删除它们。
STRAIGHT_JOIN强制优化器按照它们在FROM子句中列出的顺序来连接表。如果优化器以非最优顺序加入表，可以使用它来加速查询。 STRAIGHT_JOIN也可以在table_references列表中使用

STRAIGHT_JOIN不适用于优化器视为const或系统表的任何表。这样的表生成单行，在查询执行的优化阶段期间被读取，并且在查询执行继续之前对其列的引用被替换为适当的列值。这些表将首先出现在EXPLAIN显示的查询计划中。

SQL_BIG_RESULT或SQL_SMALL_RESULT可以与GROUP BY或DISTINCT一起使用，以告诉优化器结果集分别有很多行或很小。对于SQL_BIG_RESULT，MySQL直接使用基于磁盘的临时表（如果它们已创建），并且倾向于使用带有 GROUP BY元素上的键的临时表进行排序。对于SQL_SMALL_RESULT，MySQL使用内存中的临时表来存储结果表而不是使用排序。这通常不需要。
SQL_BUFFER_RESULT强制将结果放入临时表中。这有助于MySQL尽早释放表锁，并有助于在需要很长时间将结果集发送到客户端的情况下。此修饰符只能用于顶级SELECT语句，不能用于子查询或UNION之后。
SQL_CALC_FOUND_ROWS告诉MySQL计算结果集中有多少行，不考虑任何LIMIT子句。然后可以使用SELECT FOUND_ROWS（）检索行数。
SQL_CACHE和SQL_NO_CACHE修饰符会影响查询缓存中查询结果的缓存（请参见第8.10.3节“MySQL查询缓存”）。 SQL_CACHE告诉MySQL将结果存储在查询缓存中（如果缓存可用并且query_cache_type系统变量的值为2或DEMAND）。使用SQL_NO_CACHE，服务器不使用查询缓存。它既不检查查询缓存以查看结果是否已被缓存，也不缓存查询结果。

对于视图，如果SQL_NO_CACHE出现在查询中的任何SELECT中，则适用。对于可缓存查询，如果SQL_CACHE出现在查询引用的视图的第一个SELECT中，则会应用SQL_CACHE。

注意：
查询缓存从MySQL 5.7.20开始已弃用，并在MySQL 8.0中删除。弃用包括SQL_CACHE和SQL_NO_CACHE。

13.2.9.1 SELECT ....INTO 语法

SELECT的SELECT ... INTO形式可以使查询结果存储在变量中或写入文件中：

SELECT ... INTO var_list选择列值并将它们存储到变量中。
SELECT ... INTO OUTFILE将选定的行写入文件。可以指定列和行终止符来产生特定的输出格式。
SELECT ... INTO DUMPFILE在没有任何格式的情况下将一行写入文件。

SELECT语法描述（见第13.2.9节“SELECT语法”）显示语句结尾附近的INTO子句。也可以在select_expr列表后立即使用INTO。

INTO子句不应该用在嵌套SELECT中，因为这样的SELECT必须将其结果返回给外部上下文。

INTO子句可以命名一个或多个变量的列表，这些变量可以是用户定义的变量，存储过程或函数参数或存储的程序局部变量。（在准备好的SELECT ... INTO OUTFILE语句中，只允许用户定义的变量;

选定的值被分配给变量。变量的数量必须与列的数量相匹配。该查询应该返回一行。如果查询不返回任何行，则会发生错误代码为1329的警告（无数据），并且变量值保持不变。如果查询返回多行，则会发生错误1172（结果由多行组成）。如果语句可能检索多行，则可以使用LIMIT 1将结果集限制为单行。

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

SELECT ... INTO OUTFILE 'file_name'形式的SELECT将所选行写入文件。该文件是在服务器主机上创建的，因此您必须具有FILE权限才能使用此语法。 file_name不能是现有的文件，除此之外，它可以防止/etc /passwd和数据库表等文件被破坏。 character_set_filesystem系统变量控制文件名的解释。

SELECT ... INTO OUTFILE语句主要用于让您快速地将表转储到服务器计算机上的文本文件中。如果要在除服务器主机之外的其他主机上创建生成的文件，通常无法使用SELECT ... INTO OUTFILE，因为无法写入文件相对于服务器主机文件系统的路径。

但是，如果MySQL客户端软件安装在远程计算机上，则可以使用客户端命令（如mysql -e“SELECT ...”> file_name）在客户端主机上生成该文件。

如果可以使用服务器文件系统上的网络映射路径访问远程主机上文件的位置，也可以在不同于服务器主机的其他主机上创建生成的文件。在这种情况下，目标主机不需要存在mysql（或其他MySQL客户端程序）。

SELECT ... INTO OUTFILE是LOAD DATA INFILE的补充。将列值写入转换为CHARACTER SET子句中指定的字符集。如果不存在这样的子句，则使用二进制字符集转储值。实际上，没有字符集转换。如果结果集包含多个字符集中的列，则输出数据文件也会如此，并且您可能无法正确地重新加载文件。

语句的export_options部分的语法由与LOAD DATA INFILE语句一起使用的FIELDS和LINES子句组成。有关FIELDS和LINES子句的信息，包括它们的默认值和允许值，请参见第13.2.6节“LOAD DATA INFILE语法”。

FIELDS ESCAPED BY控制如何编写特殊字符。如果FIELDS ESCAPED BY字符不为空，则在必要时使用该字符，以避免输出上的以下字符前面的前缀出现歧义：

FIELDS ESCAPED BY字符
FIELDS [OPTIONALLY] ENCLOSED BY 字符
FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符
ASCII NUL（零值字节;转义字符后面实际写入的是ASCII 0，不是零值字节）

FIELDS TERMINATED BY，ENCLOSED BY，ESCAPED BY或LINES TERMINATED BY字符必须被转义，以便您可以可靠地读取文件。 ASCII NUL被转义，以便使用某些页面查看时更容易。

生成的文件不必符合SQL语法，所以不需要转义。

如果FIELDS ESCAPED BY字符为空，则不会转义字符，并将NULL输出为NULL，而不是\N。指定一个空的转义字符可能不是一个好主意，特别是如果数据中的字段值包含刚才给出的列表中的任何字符。

以下是一个使用的逗号分隔值（CSV）格式生成文件的示例：

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

如果您使用INTO DUMPFILE而不是INTO OUTFILE，则MySQL只向该文件写入一行，不会有任何列或行终止，也不会执行任何转义处理。如果要将BLOB值存储在文件中，这非常有用。

注意：
由INTO OUTFILE或INTO DUMPFILE创建的任何文件都可由服务器主机上的所有用户写入。原因在于MySQL服务器无法创建一个文件，该文件是其运行的帐户下的用户以外的任何人拥有的文件。（由于这个原因和其他原因，你不应该以root身份运行mysqld。）因此，这个文件必须是世界可写的，以便你可以操纵它的内容。

如果secure_file_priv系统变量设置为非空目录名称，则要写入的文件必须位于该目录中。

在作为Event Scheduler执行事件一部分发生的SELECT ... INTO语句的上下文中，将诊断消息（不仅是错误，还包括警告）写入错误日志，并且在Windows上写入应用程序事件日志。

13.2.9.2 JOIN 语法

MySQL支持SELECT语句的table_references部分和多表DELETE和UPDATE语句的以下JOIN语法：

表引用也称为联接表达式。

表引用（引用分区表时）可能包含PARTITION选项，包括逗号分隔的分区，子分区或二者的列表。该选项跟随表的名称并位于任何别名声明之前。此选项的作用是仅从列出的分区或子分区中选择行。列表中未命名的任何分区或子分区都将被忽略。

与标准SQL相比，在MySQL中扩展了table_factor的语法。该标准只接受table_reference，而不是它们在一对括号内的列表。

如果table_reference项目列表中的每个逗号都被视为等同于内部联接，则这是一个保守的扩展。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

其等价于：
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的（它们可以相互替换）。在标准的SQL中，它们不是等价的。 INNER JOIN与ON子句一起使用，否则使用CROSS JOIN。

通常，在仅包含内连接操作的连接表达式中可以忽略括号。 MySQL也支持嵌套连接。

可以指定索引提示以影响MySQL优化器如何使用索引。有关更多信息，请参见第8.9.4节“索引提示”。优化器提示和optimizer_switch系统变量是影响优化器使用索引的其他方式。

以下列表描述了在编写连接时要考虑的一般因素：

表引用可以使用tbl_name AS alias_name或tbl_name alias_name来别名：

SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

在FROM子句中，table_subquery也称为派生表或子查询。请参见第13.2.10.8节“派生表（FROM子句中的子查询）”。这种子查询必须包含一个别名，以便为子查询结果指定一个表名。一个简单的例子如下：

SELECT * FROM (SELECT 1, 2, 3) AS t1;

INNER JOIN和（逗号）在没有连接条件的情况下在语义上是等价的：两者都在指定的表之间生成笛卡尔积（即，第一个表中的每一行都连接到第二个表中的每一行）。

但是，逗号运算符的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等。如果在存在连接条件时将逗号连接与其他连接类型混合，则可能会出现 Unknown column 'col_name' in 'on clause' 。有关处理这个问题的信息在本节后面给出。

与ON一起使用的conditional_expr是可以在WHERE子句中使用的任何形式的条件表达式。通常，ON子句用于指定如何连接表的条件，WHERE子句限制结果集中要包含的行。
如果在LEFT JOIN中的ON或USING部分中的右表没有匹配的行，则所有列设置为NULL的行将用于右表。您可以使用这个事实来查找表中没有对应表的行：

SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;

此示例查找left_tbl中所有行中id值不存在于right_tbl中的行（即，left_tbl中的所有行，right_tbl中没有对应的行）。

USING（column_list）子句命名两个表中必须存在的列表中的字段。如果表a和b都包含列c1，c2和c3，则以下联接会比较两个表中的相应列：

a LEFT JOIN b USING (c1, c2, c3)

两个表的NATURAL [LEFT] JOIN定义为在语义上等同于INNER JOIN或带有USING子句的LEFT JOIN，该子句用于命名两个表中存在的所有列。
RIGHT JOIN与LEFT JOIN类似。为了让代码可以跨数据库移植，建议使用LEFT JOIN而不是RIGHT JOIN。
STRAIGHT_JOIN与JOIN类似，只是左表总是在右表之前读取。这可以用于连接优化程序以次优顺序处理表的（少数）情况。
USING子句可以重写为比较相应列的ON子句。但是，尽管USING和ON类似，但它们并不完全相同。考虑以下两个查询：

a LEFT JOIN b USING (c1, c2, c3)

a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

关于确定哪些行满足连接条件，两个连接在语义上是相同的。

关于确定要显示哪些列以进行SELECT *扩展，这两个连接在语义上是不相同的。 USING连接选择相应列的合并值，而ON连接选择所有表中的所有列。对于USING连接，SELECT *选择这些值：

COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)

对于ON 连接，select * 选择下列值：

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

使用inner 连接时，COALESCE（a.c1，b.c1）与a.c1或b.c1相同，因为两列的值都相同。使用外连接（如LEFT JOIN）时，两列中的一列可以为NULL。该列在结果中被省略。

ON子句只能引用其操作数。

例如：

CREATE TABLE t1 (i1 INT);

CREATE TABLE t2 (i2 INT);

CREATE TABLE t3 (i3 INT);

SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

由于i3是t3中的一列，它不是ON子句的操作数，所以该语句出现错误 Unknown column 'i3' in 'on clause' 。要使连接得到处理，请按如下所示重写语句：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

JOIN的优先级高于逗号运算符（，），所以连接表达式t1，t2 JOIN t3被解释为（t1，（t2 JOIN t3）），而不是（（t1，t2）JOIN t3）。这会影响使用ON子句的语句，因为该子句只能引用连接的操作数中的列，而优先级会影响对这些操作数的解释。

例如：

CREATE TABLE t1 (i1 INT, j1 INT);

CREATE TABLE t2 (i2 INT, j2 INT);

CREATE TABLE t3 (i3 INT, j3 INT);

INSERT INTO t1 VALUES(1, 1);

INSERT INTO t2 VALUES(1, 1);

INSERT INTO t3 VALUES(1, 1);

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

JOIN优先于逗号运算符，所以ON子句的操作数是t2和t3。因为t1.i1不是任何一个操作数中的列，所以出现错误 Unknown column 't1.i1' in 'on clause'

要启用连接处理，请使用以下任一策略：

用括号将前两个表格分组，以便ON子句的操作数是（t1，t2）和t3：

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

避免使用逗号运算符并使用JOIN：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

相同的优先级解释也适用于将逗号运算符与INNER JOIN，CROSS JOIN，LEFT JOIN和RIGHT JOIN混合使用的语句，它们的优先级均高于逗号运算符。

13.2.9.3 Union 语法

SELECT ...

UNION [ALL | DISTINCT] SELECT ...

[UNION [ALL | DISTINCT] SELECT ...]

UNION用于将多个SELECT语句的结果合并为一个结果集。

第一个SELECT语句的列名用作返回结果的列名。在每个SELECT语句的相应位置列出的选定列应具有相同的数据类型。（例如，第一条语句选择的第一列应该与其他语句选择的第一列具有相同的类型。）

如果相应SELECT列的数据类型不匹配，则UNION结果中列的类型和长度会考虑所有SELECT语句检索的值。例如，请考虑以下几点：

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a |

| bbbbbbbbbb |

+---------------+

SELECT语句是正常的select语句，但有以下限制：

只有最后一条SELECT语句可以使用INTO OUTFILE。（但是，整个UNION结果写入文件。）
HIGH_PRIORITY不能与作为UNION一部分的SELECT语句一起使用。如果您为第一个SELECT指定它，它不起作用。如果您为任何后续SELECT语句指定它，则会出现语法错误。

UNION的默认行为是将重复的行从结果中删除。可选的DISTINCT关键字除默认外没有任何效果，因为它也指定删除重复行。使用可选的ALL关键字，不会发生重复行删除，结果包括所有SELECT语句中的所有匹配行。

您可以在同一查询中混合使用UNION ALL和UNION DISTINCT。混合联合类型的处理方式使得DISTINCT联合会覆盖其左侧的所有联合。 DISTINCT联合可以通过使用UNION DISTINCT显式地生成，或者通过使用UNION而没有以下DISTINCT或ALL关键字来隐式生成。

要将ORDER BY或LIMIT应用于单个SELECT，请将该子句置于包含SELECT的括号内：

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

注意：

以前的MySQL版本可能允许没有括号的语句。在MySQL 5.7中，强制执行括号的要求。

对单个SELECT语句使用ORDER BY并不意味着行在最终结果中出现的顺序，因为默认情况下UNION会生成无序的一组行。因此，在此上下文中使用ORDER BY通常与LIMIT结合使用，以便它用于确定要为SELECT检索的所选行的子集，即使它不一定会影响SELECT中这些行最终UNION结果的顺序。如果ORDER BY在SELECT中没有LIMIT出现，它会被优化掉，因为它无论如何都不会起作用。

要使用ORDER BY或LIMIT子句对整个UNION结果进行排序或限制，请将各个SELECT语句括起来并在最后一个之后放置ORDER BY或LIMIT。以下示例使用两个子句：

(SELECT a FROM t1 WHERE a=10 AND B=1)

UNION

(SELECT a FROM t2 WHERE a=11 AND B=2)

ORDER BY a LIMIT 10;

--没有括号的语句等价于加括号的。

这种ORDER BY不能使用包含表名的列引用（即tbl_name.col_name格式的名称）。相反，在第一个SELECT语句中提供一个列别名并引用ORDER BY中的别名。（或者，使用列位置参考ORDER BY中的列，但不赞成使用列位置。）

另外，如果要排序的列是别名，则ORDER BY子句必须引用别名，而不是列名。下列第一条语句可以工作，但第二条语句会失败，出现 Unknown column 'a' in 'order clause' :

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

要使UNION结果中的行由每个SELECT一个接一个地检索到的行组成，请在每个SELECT中选择一个额外的列作为排序列，并在最后一个SELECT之后添加ORDER BY：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)

UNION (SELECT 2, col2a, col2b, ... FROM t2)

ORDER BY sort_col;

要额外维护单个SELECT结果中的排序顺序，请将一个辅助列添加到ORDER BY子句中：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)

UNION (SELECT 2, col2a, col2b, ... FROM t2)

ORDER BY sort_col, col1a;

使用附加列还可以确定每行来自哪个SELECT。额外的列也可以提供其他标识信息，例如指示表名称的字符串。

在ORDER BY子句中使用聚合函数的UNION查询将被拒绝，并显示ER_AGGREGATE_ORDER_FOR_UNION错误。例：

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);

13.2.10 子查询语法

这里有一个子查询的例子：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

在这个例子中，SELECT * FROM t1 ...是外部查询（或外部语句），并且（SELECT column1 FROM t2）是子查询。我们说子查询嵌套在外部查询中，事实上，可以将子查询嵌套在其他子查询中，达到相当深的程度。子查询必须始终出现在括号内。

子查询的主要优点是：

它们允许查询结构化，以便可以隔离语句的每个部分。
提供替代方法来执行操作，否则需要复杂的连接和联合。
许多人发现子查询比复杂的联接或联合更具可读性。事实上，子查询的创新给人们提供了调用早期SQL“结构化查询语言”的最初想法。

下面是一个示例语句，它显示了SQL标准中指定的并且在MySQL中支持的子查询语法的主要观点：

DELETE FROM t1 WHERE s11 > ANY

(SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS

(SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION

SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));

子查询可以返回标量（单个值），单个行，单个列或表（一列或多列一列或多列）。这些称为标量，列，行和表子查询。子查询返回特定类型的结果通常只能在特定的上下文中使用，如以下各节所述。

对于可以使用子查询的语句类型几乎没有限制。子查询可以包含许多普通SELECT可以包含的关键字或子句：DISTINCT，GROUP BY，ORDER BY，LIMIT，连接，索引提示，UNION结构，注释，函数等等。

13.2.10.1 子查询作为标量操作数

以最简单的形式，子查询是返回单个值的标量子查询。标量子查询是一个简单的操作数，并且几乎可以在单个列值或文字合法的任何位置使用它，并且您可以期望它具有所有操作数具有的特征：数据类型，长度，它可以指示为NULL，等等。例如：

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);

INSERT INTO t1 VALUES(100, 'abcde');

SELECT (SELECT s2 FROM t1);

此SELECT中的子查询返回一个单一值（'abcde'），其数据类型为CHAR，长度为5，字符集和排序规则等于CREATE TABLE时有效的默认值，并指示值该列可以是NULL。由标量子查询选择的值的可空性不会被复制，因为如果子查询结果为空，则结果为NULL。对于刚刚显示的子查询，如果t1为空，则即使s2为 NOT NULL，结果也是NULL。

有几个上下文中不能使用标量子查询。如果语句只允许一个字面值，则不能使用子查询。例如，LIMIT需要文字整数参数，LOAD DATA INFILE需要文字串文件名。您不能使用子查询来提供这些值。

13.2.10.2 比较中使用子查询

子查询最常见的用法是：

non_subquery_operand comparison_operator (subquery)

其中compare_operator是这些操作之一：

= > < >= <= <> != <=>

例如：

... WHERE 'a' = (SELECT column1 FROM t1)

MySQL也允许这个结构：

non_subquery_operand LIKE (subquery)

以下是常见形式子查询比较的一个示例，您无法使用联接进行比较。它查找表t1中列1的值等于表t2中的最大值的所有行

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);

这是另一个例子，对于一个连接来说也是不可能的，因为它涉及到一个表的聚合。它查找表t1中包含在给定列中出现两次的值的所有行：

SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

--这个是相关子查询

为了比较子查询和标量，子查询必须返回一个标量。为了比较子查询和行构造函数，子查询必须是一个行子查询，该行子查询返回与行构造函数具有相同数量值的行。

13.2.10.3 使用 ANY ,IN 或SOME 的子查询

语法：

operand comparison_operator ANY (subquery)

operand IN (subquery)

operand comparison_operator SOME (subquery)

其中compare_operator是这些操作符之一：

= > < >= <= <> !=

ANY关键字必须遵循比较运算符，意思是“如果比较为TRUE，则返回子查询所在列中的任何值”。例如：

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

假设表t1中有一行包含（10）。如果表t2包含（21,14,7），则表达式为TRUE，因为t2中的值7小于10.如果表t2包含（20,10），或者表t2为空，则表达式为FALSE。如果表t2包含（NULL，NULL，NULL），表达式是未知的（即NULL）。

当与子查询一起使用时，单词IN是= ANY的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

与表达式列表一起使用时，IN和= ANY不是同义词。 IN可以带一个表达式列表，但是= ANY不能。

NOT IN不是<> ANY的别名，而是<> ALL的别名。

单词SOME是ANY的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

使用SOME这个词很少见，但这个例子说明了为什么它可能有用。对大多数人来说，英语短语“a不等于任何b”意味着“没有b等于a”，但这不是SQL语法的含义。语法的意思是“有一些b与a不相等”。使用<> SOME可以帮助确保每个人都理解查询的真正意义。

13.2.10.4 ALL 中的子查询

语法：

operand comparison_operator ALL (subquery)

ALL必须遵循比较运算符，意思是“如果比较结果为TRUE，则子查询返回的列中的所有值都返回TRUE”。例如：

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

假设表t1中有一行包含（10）。如果表t2包含（-5,0，+ 5），则表达式为TRUE，因为10大于t2中的所有三个值。如果表t2中包含（12,6，NULL，-100），则表达式为FALSE，因为表t2中存在大于10的单个值12.如果表t2包含（0，NULL ,1），则表达式是未知（即NULL）。

最后，如果表t2为空，则表达式为TRUE。因此，当表t2为空时，以下表达式为TRUE：

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

但是当表t2为空时，该表达式为NULL：

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

另外，当表t2为空时，以下表达式为NULL：

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

一般而言，包含NULL值和空表的表是“边缘案例”。编写子查询时，始终考虑是否考虑了这两种可能性。

NOT IN是<> ALL的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

13.2.10.5 行子查询

标量或列子查询返回单个值或一列值。行子查询是返回单个行的子查询变体，因此可以返回多个列值。行子查询比较的合法操作符是：

= > < >= <= <> != <=>

这里有两个例子：

SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

对于这两个查询，如果表t2包含id = 10的单个行，则子查询将返回单个行。如果此行的col3和col4值等于t1中任何行的col1和col2值，则WHERE表达式为TRUE，并且每个查询将返回这些t1行。如果t2行col3和col4值不等于任何t1行的col1和col2值，则表达式为FALSE，并且查询返回空结果集。如果子查询不产生任何行，则表达式是未知的（即NULL）。如果子查询生成多行，则会发生错误，因为行子查询最多只能返回一行。

--多行用in

表达式（1,2）和ROW（1,2）有时称为行构造函数。这两个是相同的。行构造函数和子查询返回的行必须包含相同数量的值。

行构造函数用于与返回两列或更多列的子查询进行比较。当子查询返回单个列时，这被认为是标量值而不是行，所以行构造函数不能用于返回少于两列的子查询。因此，以下查询失败，出现语法错误：

SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)

行构造函数在其他情况下是合法的。例如，以下两个语句在语义上是等价的（并且由优化器以相同的方式处理）：

SELECT * FROM t1 WHERE (column1,column2) = (1,1);

SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

以下查询将回答请求，“查找表t1中也存在于表t2中的所有行”：

SELECT column1,column2,column3 FROM t1

WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);

13.2.10.6 EXISTS 或NOT EXISTS 子查询

如果子查询返回任何行，则EXISTS子查询为TRUE，NOT EXISTS子查询为FALSE。例如：

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

传统上，EXISTS子查询以SELECT *开头，但它可以以SELECT 5或SELECT column1或任何其他开头。 MySQL在这样的子查询中忽略了SELECT列表，所以它没有区别。

对于前面的例子，如果t2包含任何行，甚至包含NULL值的行，则EXISTS条件为TRUE。这实际上是一个不太可能的例子，因为[NOT] EXISTS子查询几乎总是包含相关性。以下是一些更实际的例子：

一个或多个城市有什么样的商店？

SELECT DISTINCT store_type FROM stores WHERE EXISTS

(SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);

什么样的商店没有城市？

SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);

所有城市都有什么样的商店？

SELECT DISTINCT store_type FROM stores s1

WHERE NOT EXISTS (

SELECT * FROM cities WHERE NOT EXISTS (

SELECT * FROM cities_stores

WHERE cities_stores.city = cities.city

AND cities_stores.store_type = stores.store_type));

最后一个例子是一个双嵌套的NOT EXISTS查询。也就是说，它在NOT EXISTS子句中有一个NOT EXISTS子句。形式上，它回答了“城市是否存在一家不在商店中的商店”的问题？但简单来说一个嵌套的NOT EXISTS回答了这个问题：“对于所有的y，x都是真的吗？”

13.2.10.7 相关子查询

相关子查询是一个子查询，它包含对也出现在外部查询中的表的引用。例如：

SELECT * FROM t1 WHERE column1 = ANY (

SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

注意子查询包含对t1列的引用，即使子查询的FROM子句没有提到表t1也是如此。因此，MySQL在子查询之外查找，并在外部查询中找到t1。

假设表t1包含一行，其中column1 = 5和column2 = 6; 同时，表t2包含一行，其中column1 = 5且column2 = 7。简单表达式... WHERE column1 = ANY（SELECT column1 FROM t2）将为TRUE，但在本例中，子查询中的WHERE子句为FALSE 因为（5,6）不等于（5,7），所以整个表达式为FALSE。

范围规则：MySQL从内部到外部评估。例如：

SELECT column1 FROM t1 AS x

WHERE x.column1 = (SELECT column1 FROM t2 AS x

WHERE x.column1 = (SELECT column1 FROM t3

WHERE x.column2 = t3.column1));

在此语句中，x.column2必须是表t2中的列，因为SELECT column1 FROM t2 AS x ...重命名t2。它不是表t1中的列，因为SELECT column1 FROM t1 ...是更远的外部查询。

对于HAVING或ORDER BY子句中的子查询，MySQL还会在外部选择列表中查找列名。

对于某些情况，相关的子查询会进行优化。例如：

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

否则，它们效率低下，可能会变慢。将查询重写为联接可能会提高性能。

相关子查询中的聚合函数可能包含外部引用，前提是该函数只包含外部引用，并且该函数不包含在另一个函数或表达式中。

13.2.10.8 派生表（在from 子句中的子查询）

派生表是SELECT语句FROM子句中的子查询：

SELECT ... FROM (subquery) [AS] tbl_name ...

[AS] tbl_name子句是强制性的，因为FROM子句中的每个表都必须有一个名称。子查询选择列表中的任何列必须具有唯一名称。

为了说明起见，假设你有这张表：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

以下是如何使用示例表在FROM子句中使用子查询：

INSERT INTO t1 VALUES (1,'1',1.0);

INSERT INTO t1 VALUES (2,'2',2.0);

SELECT sb1,sb2,sb3 FROM (

SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1;

结果是： 2, '2', 4.0

这里是另一个例子：假设你想知道分组表中的一组总和的平均值。这不起作用：

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

但是，此查询提供了所需的信息：

SELECT AVG(sum_column1)

FROM (

SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1)

AS t1;

请注意，子查询（sum_column1）中使用的列名在外部查询中被识别。

派生表可以返回标量，列，行或表。

派生表不能是相关的子查询，或包含外部引用或对同一SELECT的其他表的引用。

优化器以这样的方式确定关于派生表的信息，即对于EXPLAIN不会实现它们的实现。

在某些情况下，使用EXPLAIN SELECT可能会修改表数据。如果外部查询访问任何表并且内部查询调用更改表的一个或多个行的存储函数，则会发生这种情况。假设数据库d1中有两个表t1和t2，并且存储函数f1修改了t2，如下所示：

CREATE DATABASE d1;

USE d1;

CREATE TABLE t1 (c1 INT);

CREATE TABLE t2 (c1 INT);

CREATE FUNCTION f1(p1 INT) RETURNS INT BEGIN INSERT INTO t2 VALUES (p1);

RETURN p1; END;

例如：

EXPLAIN SELECT f1(5)\G

EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G

不会改变t2表的值

但是，如果外部SELECT引用任何表，那么优化器也会在子查询中执行语句：

EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G

mysql> SELECT * FROM t2;

+------+

| c1 |

+------+

| 5 |

+------+

1 row in set (0.00 sec)

这也意味着EXPLAIN SELECT语句（如此处所示）可能需要很长时间才能执行，因为对于t1中的每一行，BENCHMARK（）函数都会执行一次：

EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));

13.2.10.10 子查询优化

下面有一些可以参考的优化技巧

使用影响子查询中行数或顺序的子查询子句。例如：

SELECT * FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2 ORDER BY column1);

SELECT * FROM t1 WHERE t1.column1 IN (SELECT DISTINCT column1 FROM t2);

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 LIMIT 1);

将联接替换为子查询。例如，试试这个：

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);

代替：

SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

一些子查询可以转换为连接，以与不支持子查询的旧版MySQL兼容。但是，在某些情况下，将子查询转换为连接可能会提高性能。
将子句从外部移动到子查询中。例如，使用这个查询：

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

代替查询：

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

再举一个例子，使用这个查询：

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

代替查询：

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

使用行子查询而不是相关的子查询。例如，使用这个查询：

SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

代替查询：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2);

使用 NOT (a=ANY(....)) 而不是 a <> ALL (....)
使用x = ANY（包含（1,2）的表）而不是x = 1或x = 2。
使用 =ANY 而不是 EXISTS
对于总是返回一行的不相关的子查询，IN总是慢于=。例如，使用这个查询：

SELECT * FROM t1 WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);

代替查询：

SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

这些技巧可能会导致程序变得更快或更慢。使用诸如BENCHMARK（）函数之类的MySQL工具，您可以了解什么可以帮助您处理自己的情况。

MySQL本身所做的一些优化是：

MySQL只执行一次不相关的子查询。使用EXPLAIN确保给定的子查询真的不相关。
MySQL会重写IN，ALL，ANY和SOME子查询，以试图利用子查询中的select-list列被索引的可能性。
MySQL使用索引查找函数替换以下表单的子查询，EXPLAIN将其描述为特殊连接类型（unique_subquery或index_subquery）：

... IN (SELECT indexed_column FROM single_table ...)

除非涉及NULL值或空集，否则MySQL使用涉及MIN（）或MAX（）的表达式增强以下表单的表达式：

value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

例如，WHERE 子句：

WHERE 5 > ALL (SELECT x FROM t)

能会像这样被优化器处理：

WHERE 5 > (SELECT MAX(x) FROM t)

13.2.10.11 重写子查询为join

有时候还有其他方法来测试一组值中的成员资格，而不是使用子查询。此外，在某些情况下，在没有子查询的情况下重写查询，使用其中一些技术而不是使用子查询可能更有效。其中之一是IN（）构造：

例如，这个查询：

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

可以被重写为：

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

查询：

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

可以被重写为：

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

LEFT [OUTER] JOIN可以比等效的子查询更快，因为服务器可能能够更好地进行优化 - 这并非MySQL服务器特有的事实。在SQL-92之前，外连接不存在，所以子查询是做某些事情的唯一方法。今天，MySQL服务器和许多其他现代数据库系统提供了广泛的外部联接类型。

13.2.11 UPDATE 语法

UPDATE是修改表中的行的DML语句。

单一表语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET assignment_list

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

value: {expr | DEFAULT}

assignment: col_name = value

assignment_list:

assignment [, assignment] ...

多表语法：

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

对于单表语法，UPDATE语句用新值更新指定表中现有行的列。 SET子句指示要修改哪些列以及应该给出哪些值。每个值都可以作为表达式给出，或者使用关键字DEFAULT将列明确设置为默认值。 WHERE子句（如果给出）指定标识要更新哪些行的条件。没有WHERE子句，所有行都被更新。如果指定了ORDER BY子句，则按照指定的顺序更新行。 LIMIT子句对可更新的行数设置了限制。

对于多表语法，UPDATE更新table_references中命名的满足条件的每个表中的行。即使匹配条件多次，每个匹配行也只更新一次。对于多表语法，不能使用ORDER BY和LIMIT。

您只需要在UPDATE中引用的实际更新的列的UPDATE权限。对于读取但未修改的任何列，只需要SELECT权限。

UPDATE语句支持下列修饰符：

使用LOW_PRIORITY修饰符时，UPDATE的执行会延迟，直到没有其他客户端正在从表中读取。这只影响只使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE）。
使用IGNORE修饰符，即使在更新期间发生错误，更新语句也不会中止。不会更新在唯一键值上出现重复键冲突的行。行更新为将导致数据转换错误的值更新为最接近的有效值。

UPDATE IGNORE语句（包括具有ORDER BY子句的语句）被标记为不安全的基于语句的复制。（这是因为更新行的顺序决定忽略哪些行）。当使用基于语句的模式时，这些语句会在错误日志中产生警告，并在使用MIXED时使用基于行的格式写入二进制日志模式。（bug＃11758262，bug＃50439）

如果从表中访问要更新的表中的列，UPDATE将使用该列的当前值。例如，以下语句将col1设置为比当前值多一个：

UPDATE t1 SET col1 = col1 + 1;

下列语句中的第二个赋值将col2设置为当前（更新的）col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为不同于标准SQL。

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

单表UPDATE分配通常从左到右进行评估。对于多表更新，不保证分配按任何特定顺序执行。

如果您将列设置为当前的值，MySQL会注意到这一点，并且不会更新它。

如果通过设置为NULL更新已声明为NOT NULL的列，则如果启用严格SQL模式则会发生错误; 否则，该列将设置为列数据类型的隐式默认值，并且警告计数会递增。数字类型的隐式缺省值为0，字符串类型为空字符串（''），日期和时间类型为“零”值。

如果显式更新生成的列，则唯一允许的值是DEFAULT。

您可以使用LIMIT row_count来限制UPDATE的范围。 LIMIT子句是行匹配限制。只要找到满足WHERE子句的row_count行，该语句就会停止，无论它们是否真的发生了更改。

如果UPDATE语句包含ORDER BY子句，则按照该子句指定的顺序更新行。这在某些情况下可能会有用，否则可能会导致错误。假设表t包含具有唯一索引的列ID。以下语句可能会因重复键错误而失败，具体取决于更新行的顺序：

UPDATE t SET id = id + 1;

例如，如果表中ID列中包含1和2，并且在2更新为3之前1更新为2，则会发生错误。为避免此问题，请添加ORDER BY子句，以使具有较大id值的行在具有较小值的行之前被更新：

UPDATE t SET id = id + 1 ORDER BY id DESC;

您也可以执行覆盖多个表的UPDATE操作。但是，您不能将ORDER BY或LIMIT用于多表UPDATE。 table_references子句列出了连接中涉及的表。其语法在第13.2.9.2节“JOIN语法”中描述。这里是一个例子：

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

上例显示使用逗号运算符的内连接，但多表UPDATE语句可以使用SELECT语句中允许的任何类型的连接，例如LEFT JOIN。

如果您使用涉及有外键约束的InnoDB表的多表UPDATE语句，则MySQL优化器可能会按照与其父/子关系不同的顺序处理表。在这种情况下，语句失败并回滚。相反，更新单个表并依赖InnoDB提供的ON UPDATE功能来使其他表被相应地修改。

Section 14.8.1.6, “InnoDB and FOREIGN KEY Constraints”.

ITPub博客

13.2 数据操作语句（2）

最新文章