MySQL8 中文参考（六十一）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

15.2.15.9 侧向派生表

通常，派生表不能引用同一FROM子句中之前表的列。从 MySQL 8.0.14 开始，可以将派生表定义为侧向派生表，以指定允许这种引用。

非侧向派生表使用第 15.2.15.8 节“派生表”中讨论的语法来指定。侧向派生表的语法与非侧向派生表相同，只是在派生表规范之前指定了关键字LATERAL。LATERAL关键字必须在每个要用作侧向派生表的表之前。

侧向派生表受到以下限制：

侧向派生表只能出现在FROM子句中，可以是用逗号分隔的表列表，也可以是连接规范（JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN或RIGHT [OUTER] JOIN）中。
如果一个侧向派生表在连接子句的右操作数中，并且包含对左操作数的引用，则连接操作必须是INNER JOIN、CROSS JOIN或LEFT [OUTER] JOIN。

如果表在左操作数中，并且包含对右操作数的引用，则连接操作必须是INNER JOIN、CROSS JOIN或RIGHT [OUTER] JOIN。
如果一个侧向派生表引用了一个聚合函数，则该函数的聚合查询不能是包含侧向派生表的FROM子句所属的查询。
根据 SQL 标准，MySQL 始终将与表函数（如JSON_TABLE()）的连接视为已使用LATERAL。这在 MySQL 的任何版本中都是正确的，这就是为什么即使在 MySQL 8.0.14 之前的版本中也可以针对此函数进行连接。在 MySQL 8.0.14 及更高版本中，LATERAL关键字是隐式的，并且不允许在JSON_TABLE()之前使用。这也符合 SQL 标准。

以下讨论显示了侧向派生表如何使得某些 SQL 操作成为可能，这些操作无法通过非侧向派生表完成，或者需要更低效的解决方法。

假设我们想解决这个问题：给定一个销售团队成员的表（其中每行描述一个销售团队成员），以及所有销售的表（其中每行描述一笔销售：销售人员、客户、金额、日期），确定每个销售人员的最大销售额及其客户。这个问题可以有两种方法来解决。

解决问题的第一种方法：对于每个销售人员，计算最大销售额，并找到提供此最大销售额的客户。在 MySQL 中，可以这样做：

SELECT
  salesperson.name,
  -- find maximum sale size for this salesperson
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS amount,
  -- find customer for this maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
         -- find maximum size, again
         (SELECT MAX(amount) AS amount
           FROM all_sales
           WHERE all_sales.salesperson_id = salesperson.id))
  AS customer_name
FROM
  salesperson;

那个查询是低效的，因为它在每个销售人员中计算最大尺寸两次（在第一个子查询中一次，在第二个子查询中一次）。

我们可以尝试通过在每个销售人员中计算最大值并在派生表中“缓存”它来实现效率提升，如这个修改后的查询所示：

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
        -- the cached maximum size
        max_sale.amount)
  AS max_sale_customer;

然而，在 SQL-92 中，该查询是非法的，因为派生表不能依赖于同一 FROM 子句中的其他表。派生表必须在查询的持续时间内保持恒定，不能包含对其他 FROM 子句表列的引用。如此编写的查询会产生以下错误：

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

在 SQL:1999 中，如果派生表前面有 LATERAL 关键字（表示“这个派生表依赖于其左侧的先前表”），则查询变得合法：

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  LATERAL
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  LATERAL
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
        -- the cached maximum size
        max_sale.amount)
  AS max_sale_customer;

一个侧向派生表不需要是恒定的，并且每当依赖的前一个表中的新行被顶层查询处理时，它就会被更新。

解决问题的第二种方法：如果 SELECT 列表中的子查询可以返回多列，则可以使用不同的解决方案：

SELECT
  salesperson.name,
  -- find maximum size and customer at same time
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
FROM
  salesperson;

那是高效的但是非法的。它不起作用，因为这样的子查询只能返回单列：

ERROR 1241 (21000): Operand should contain 1 column(s)

重写查询的一种尝试是从派生表中选择多列：

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

然而，那也不起作用。派生表依赖于 salesperson 表，因此在没有 LATERAL 的情况下失败：

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

添加 LATERAL 关键字使查询合法：

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  LATERAL
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

简而言之，LATERAL 是刚刚讨论的两种方法中所有缺点的高效解决方案。

原文：dev.mysql.com/doc/refman/8.0/en/subquery-errors.html

15.2.15.10 子查询错误

有一些错误仅适用于子查询。本节描述了这些错误。

不支持的子查询语法：

ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL doesn't yet support
'LIMIT & IN/ALL/ANY/SOME subquery'"

这意味着 MySQL 不支持以下类似语句：

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

子查询返回的列数不正确：
```
ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"
```
在这种情况下会发生此错误：
```
SELECT (SELECT column1, column2 FROM t2) FROM t1;
```
如果子查询返回多列用于行比较，则可以使用子查询。在其他情境下，子查询必须是标量操作数。参见第 15.2.15.5 节，“行子查询”。
子查询返回的行数不正确：
```
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"
```
对于子查询必须返回最多一行但返回多行的语句会发生此错误。考虑以下示例：
```
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
```
如果 SELECT column1 FROM t2 只返回一行，则前面的查询有效。如果子查询返回多于一行，则会出现错误 1242。在这种情况下，查询应重写为：
```
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
```
子查询中错误使用表：
```
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
```
在尝试在子查询中修改表并从同一表中进行选择的情况下会发生此错误：
```
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
```
您可以使用公共表达式或派生表来解决此问题。参见第 15.2.15.12 节，“子查询限制”。

在 MySQL 8.0.19 及更高版本中，当在子查询中使用TABLE时，本节描述的所有错误也适用。

对于事务性存储引擎，子查询失败会导致整个语句失败。对于非事务性存储引擎，在遇到错误之前进行的数据修改会被保留。

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-subqueries.html

15.2.15.11 优化子查询

开发仍在进行中，因此长期来看，没有可靠的优化提示。以下列表提供了一些有趣的技巧，您可能想尝试一下。另请参阅 Section 10.2.2，“优化子查询、派生表、视图引用和公共表达式”。

将子查询外部的子句移到内部。例如，使用这个查询：

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;

译文：dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html

15.2.15.12 子查询的限制

一般来说，你不能修改一个表并在子查询中从同一个表中选择。例如，这个限制适用于以下形式的语句：
```
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
```
例外：前述禁令不适用于如果对修改的表使用了派生表，并且该派生表是实现而不是合并到外部查询中。 (参见 Section 10.2.2.4, “使用合并或实现优化派生表、视图引用和公共表达式”.) 例如：
```
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);
```
这里派生表的结果被实现为一个临时表，因此在对t进行更新时，相关行已经被选择。

一般来说，你可以通过添加NO_MERGE优化器提示来影响优化器实现一个派生表。参见 Section 10.9.3, “优化器提示”。
行比较操作只部分支持：
- 对于*expr* [NOT] IN *subquery*, *expr可以是一个n元组（使用行构造器语法指定），子查询可以返回n*元组行。因此，允许的语法更具体地表达为*row_constructor* [NOT] IN *table_subquery*
- 对于*expr* *op* {ALL|ANY|SOME} *subquery*, *expr*必须是一个标量值，子查询必须是一个列子查询；它不能返回多列行。
换句话说，对于返回*n*元组行的子查询，这是支持的：
```
(*expr_1*, ..., *expr_n*) [NOT] IN *table_subquery*
```
但是这是不支持的：
```
(*expr_1*, ..., *expr_n*) *op* {ALL|ANY|SOME} *subquery*
```
之所以支持IN的行比较而不支持其他操作的原因是，IN是通过将其重写为一系列=比较和AND操作来实现的。这种方法不能用于ALL、ANY或SOME。
在 MySQL 8.0.14 之前，FROM子句中的子查询不能是相关子查询。它们在查询执行期间被整体实现（评估以产生结果集），因此不能针对外部查询的每一行进行评估。优化器延迟实现直到结果需要，这可能允许避免实现。参见 Section 10.2.2.4, “使用合并或实现优化派生表、视图引用和公共表达式”。

MySQL 不支持在某些子查询操作符中的子查询中使用LIMIT：

mysql> SELECT * FROM t1
       WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

参见第 15.2.15.10 节，“子查询错误”。

MySQL 允许子查询引用具有插入行等数据修改副作用的存储函数。例如，如果f()插入行，则以下查询可以修改数据：
```
SELECT ... WHERE x IN (SELECT f() ...);
```
这种行为是对 SQL 标准的扩展。在 MySQL 中，它可能产生不确定的结果，因为f()可能在给定查询的不同执行中由优化器选择处理的方式而执行不同次数。

对于基于语句或混合格式的复制，这种不确定性的一个影响是这样的查询可能在源数据库和其副本上产生不同的结果。

15.2.16 TABLE 语句

原文：dev.mysql.com/doc/refman/8.0/en/table.html

TABLE是 MySQL 8.0.19 中引入的 DML 语句，返回命名表的行和列。

TABLE *table_name* [ORDER BY *column_name*] [LIMIT *number* [OFFSET *number*]]

TABLE语句在某些方面类似于SELECT。给定名为t的表存在，以下两个语句产生相同的输出：

TABLE t;

SELECT * FROM t;

您可以使用ORDER BY和LIMIT子句对TABLE生成的行数进行排序和限制。这些与在SELECT中使用相同的子句完全相同（包括与LIMIT一起使用的可选OFFSET子句），如下所示：

mysql> TABLE t;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
|  9 |  5 |
| 10 | -4 |
| 11 | -1 |
| 13 |  3 |
| 14 |  6 |
+----+----+
7 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b;
+----+----+
| a  | b  |
+----+----+
| 10 | -4 |
| 11 | -1 |
|  1 |  2 |
| 13 |  3 |
|  9 |  5 |
| 14 |  6 |
|  6 |  7 |
+----+----+
7 rows in set (0.00 sec)

mysql> TABLE t LIMIT 3;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 6 | 7 |
| 9 | 5 |
+---+---+
3 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b LIMIT 3;
+----+----+
| a  | b  |
+----+----+
| 10 | -4 |
| 11 | -1 |
|  1 |  2 |
+----+----+
3 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b LIMIT 3 OFFSET 2;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
| 13 |  3 |
|  9 |  5 |
+----+----+
3 rows in set (0.00 sec)

TABLE在两个关键方面与SELECT不同：

TABLE总是显示表的所有列。

例外：TABLE的输出不包括不可见列。查看第 15.1.20.10 节，“不可见列”。
TABLE不允许对行进行任意过滤；也就是说，TABLE不支持任何WHERE子句。

为了限制返回的表列，过滤超出ORDER BY和LIMIT所能实现的行，或两者都使用，使用SELECT。

TABLE可以与临时表一起使用。

TABLE也可以用于取代SELECT在许多其他结构中，包括以下列出的结构：

使用诸如UNION之类的集合运算符，如下所示：

mysql> TABLE t1;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
+---+----+
3 rows in set (0.00 sec)

mysql> TABLE t2;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
| 6 | 7 |
+---+---+
3 rows in set (0.00 sec)

mysql> TABLE t1 UNION TABLE t2;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
| 1 |  2 |
| 3 |  4 |
| 6 |  7 |
+---+----+
6 rows in set (0.00 sec)

刚刚显示的UNION等效于以下语句：

mysql> SELECT * FROM t1 UNION SELECT * FROM t2;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
| 1 |  2 |
| 3 |  4 |
| 6 |  7 |
+---+----+
6 rows in set (0.00 sec)

TABLE还可以与SELECT语句、VALUES语句或两者一起在集合操作中使用。查看第 15.2.18 节，“UNION 子句”、第 15.2.4 节，“EXCEPT 子句”和第 15.2.8 节，“INTERSECT 子句”，获取更多信息和示例。另请参阅第 15.2.14 节，“使用 UNION、INTERSECT 和 EXCEPT 的集合操作”。

使用INTO填充用户变量，并使用INTO OUTFILE或INTO DUMPFILE将表数据写入文件。查看第 15.2.13.1 节，“SELECT ... INTO 语句”，获取更具体的信息和示例。
在许多情况下，您可以使用子查询。给定具有名为a的列的任何表t1，以及具有单列的第二个表t2，以下语句是可能的：
```
SELECT * FROM t1 WHERE a IN (TABLE t2);
```
假设表t1的单列命名为x，前述与以下各语句等效（在任一情况下产生完全相同的结果）：
```
SELECT * FROM t1 WHERE a IN (SELECT x FROM t2);

SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
```
查看第 15.2.15 节，“子查询”，获取更多信息。
在INSERT和REPLACE语句中，您原本会使用SELECT *。有关更多信息和示例，请参阅第 15.2.7.1 节，“INSERT ... SELECT 语句”。
TABLE在许多情况下也可以代替SELECT在CREATE TABLE ... SELECT或CREATE VIEW ... SELECT中使用。有关这些语句的更多信息和示例，请参阅其描述。

15.2.17 UPDATE 语句

原文：dev.mysql.com/doc/refman/8.0/en/update.html

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

一个UPDATE语句可以以WITH")子句开头，以定义在UPDATE中可访问的常用表达式。请参阅第 15.2.20 节，“WITH (Common Table Expressions)”")。

单表语法：

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更新满足条件的每个表中的行。每个匹配的行只更新一次，即使它多次匹配条件。对于多表语法，不能使用ORDER BY和LIMIT。

对于分区表，此语句的单表和多表形式都支持PARTITION子句作为表引用的一部分。此选项接受一个或多个分区或子分区（或两者）的列表。仅检查列出的分区（或子分区）是否匹配，并且不在任何这些分区或子分区中的行不会被更新，无论它是否满足*where_condition*。

注意

与在INSERT或REPLACE语句中使用PARTITION时不同，即使列出的分区（或子分区）中没有行与*where_condition*匹配，否则有效的UPDATE ... PARTITION语句也被认为是成功的。

有关更多信息和示例，请参见第 26.5 节，“分区选择”。

*where_condition*是一个对每个要更新的行求值为 true 的表达式。有关表达式语法，请参见第 11.5 节，“表达式”。

*table_references和where_condition*的指定如第 15.2.13 节，“SELECT 语句”所述。

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

UPDATE 语句支持以下修饰符：

使用LOW_PRIORITY修饰符，UPDATE 的执行会延迟，直到没有其他客户端从表中读取数据。这仅影响仅使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE）。
使用IGNORE修饰符，即使在更新过程中发生错误，更新语句也不会中止。对于唯一键值上发生重复键冲突的行不会被更新。将更新为会导致数据转换错误的值的行将被更新为最接近的有效值。更多信息，请参见 The Effect of IGNORE on Statement Execution。

UPDATE IGNORE 语句，包括具有ORDER BY子句的语句，被标记为不安全的用于基于语句的复制。（这是因为更新行的顺序决定了哪些行被忽略。）这样的语句在使用基于语句模式时会在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。（Bug #11758262, Bug #50439）更多信息请参见 Section 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”。

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

UPDATE t1 SET col1 = col1 + 1;

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

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

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

如果将列设置为其当前值，MySQL 会注意到这一点并且不会对其进行更新。

如果您通过将已声明为NOT NULL的列设置为NULL来更新列，并且启用了严格的 SQL 模式，则会发生错误；否则，该列将设置为列数据类型的隐式默认值，并且警告计数会增加。对于数值类型，隐式默认值为0，对于字符串类型，为空字符串（''），对于日期和时间类型，为“零”值。请参见第 13.6 节，“数据类型默认值”。

如果显式更新生成列，则唯一允许的值是DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。

UPDATE返回实际更改的行数。mysql_info() C API 函数返回匹配并更新的行数以及在UPDATE过程中发生的警告数。

您可以使用LIMIT *row_count*来限制UPDATE的范围。LIMIT子句是一个匹配行数的限制。一旦找到满足WHERE子句的*row_count*行，无论它们是否实际更改，语句都会停止。

如果UPDATE语句包含ORDER BY子句，则按照子句指定的顺序更新行。在某些情况下，这可能会避免错误。假设表t包含具有唯一索引的列id。以下语句可能会因为更新行的顺序不同而导致重复键错误：

UPDATE t SET id = id + 1;

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

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

您还可以执行涵盖多个表的UPDATE操作。但是，不能在多表UPDATE中使用ORDER BY或LIMIT。*table_references*子句列出了参与连接的表。其语法在第 15.2.13.2 节，“JOIN Clause”中描述。以下是一个示例：

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

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

如果您使用涉及InnoDB表的多表UPDATE语句，并且这些表存在外键约束，MySQL 优化器可能会以与它们的父/子关系不同的顺序处理表。在这种情况下，该语句将失败并回滚。相反，更新单个表，并依赖InnoDB提供的ON UPDATE功能，以使其他表相应地进行修改。请参阅第 15.1.20.5 节，“外键约束”。

您不能在子查询中直接更新表并从同一表中进行选择。您可以通过使用多表更新来解决此问题，其中一个表是从您实际希望更新的表派生的，并使用别名引用派生表。假设您希望更新一个名为items的表，该表是使用以下语句定义的：

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

要减少任何标记幅度为 30%或更高且库存少于一百的任何商品的零售价格，您可以尝试使用类似以下的UPDATE语句，其中在WHERE子句中使用了子查询。如下所示，此语句不起作用：

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

相反，您可以使用多表更新，其中子查询移动到要更新的表列表中，并使用别名在最外层WHERE子句中引用它，就像这样：

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

因为优化器默认尝试将派生表discounted合并到最外层查询块中，所以只有在强制实体化派生表时才起作用。您可以通过在运行更新之前将optimizer_switch系统变量的derived_merge标志设置为off，或者使用NO_MERGE优化提示来实现这一点，如下所示：

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

在这种情况下使用优化提示的优势在于它仅在使用它的查询块内部应用，因此在执行UPDATE后不需要再次更改optimizer_switch的值。

另一种可能性是重写子查询，使其不使用IN或EXISTS，就像这样：

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

在这种情况下，默认情况下子查询是实体化的，而不是合并的，因此不需要禁用派生表的合并。

15.2.18 UNION 子句

原文：dev.mysql.com/doc/refman/8.0/en/union.html

*query_expression_body* UNION [ALL | DISTINCT] *query_block*
    [UNION [ALL | DISTINCT] *query_expression_body*]
    [...]

*query_expression_body*:
    *See Section 15.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”*

UNION 将多个查询块的结果合并为单个结果集。此示例使用 SELECT 语句：

mysql> SELECT 1, 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
mysql> SELECT 'a', 'b';
+---+---+
| a | b |
+---+---+
| a | b |
+---+---+
mysql> SELECT 1, 2 UNION SELECT 'a', 'b';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| a | b |
+---+---+

MySQL 8.0 中的 UNION 处理与 MySQL 5.7 相比

在 MySQL 8.0 中，SELECT 和 UNION 的解析器规则进行了重构，以使其更一致（在每个上下文中统一应用相同的 SELECT 语法）并减少重复。与 MySQL 5.7 相比，这项工作产生了几个用户可见的效果，可能需要重写某些语句：

NATURAL JOIN 允许可选的 INNER 关键字（NATURAL INNER JOIN），符合标准 SQL。
允许不带括号的右深度连接（例如，... JOIN ... JOIN ... ON ... ON），符合标准 SQL。
STRAIGHT_JOIN 现在允许 USING 子句，类似于其他内连接。
解析器接受围绕查询表达式的括号。例如，(SELECT ... UNION SELECT ...) 是允许的。另请参阅第 15.2.11 节，“带括号的查询表达式”。
解析器更好地符合文档中允许放置 SQL_CACHE 和 SQL_NO_CACHE 查询修饰符的规定。
左侧嵌套联合，以前仅在子查询中允许，现在在顶层语句中也允许。例如，此语句现在被接受为有效：
```
(SELECT 1 UNION SELECT 1) UNION SELECT 1;
```
锁定子句（FOR UPDATE，LOCK IN SHARE MODE）仅允许在非 UNION 查询中使用。这意味着必须对包含锁定子句的 SELECT 语句使用括号。此语句不再被接受为有效：
```
SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
```
相反，应该这样写陈述：
```
(SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);
```

15.2.19 VALUES Statement

原文：dev.mysql.com/doc/refman/8.0/en/values.html

VALUES是 MySQL 8.0.19 中引入的 DML 语句，它将一组一个或多个行作为表返回。换句话说，它是一个表值构造函数，也可以作为独立的 SQL 语句。

VALUES *row_constructor_list* [ORDER BY *column_designator*] [LIMIT *number*]

*row_constructor_list*:
    ROW(*value_list*)[, ROW(*value_list*)][, ...]

*value_list*:
    *value*[, *value*][, ...]

*column_designator*:
    column_*index*

VALUES语句由VALUES关键字后跟一个或多个行构造函数列表组成，用逗号分隔。行构造函数由ROW()行构造函数子句组成，其值列表由一个或多个标量值包含在括号中。一个值可以是任何 MySQL 数据类型的文字值或解析为标量值的表达式。

ROW()不能是空的（但提供的每个标量值可以是NULL）。在同一个VALUES语句中，每个ROW()在其值列表中必须具有相同数量的值。

DEFAULT关键字不受VALUES支持，并导致语法错误，除非它用于在INSERT语句中提供值。

VALUES的输出是一个表：

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        5 |        7 |        9 |
|        4 |        6 |        8 |
+----------+----------+----------+
3 rows in set (0.00 sec)

从VALUES输出的表的列具有隐式命名的列column_0，column_1，column_2等，始终从0开始。这个事实可以用来使用可选的ORDER BY子句按列对行进行排序，就像这个子句在SELECT语句中的工作方式一样，如下所示：

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        4 |        6 |        8 |
|        5 |        7 |        9 |
+----------+----------+----------+
3 rows in set (0.00 sec)

在 MySQL 8.0.21 及更高版本中，VALUES语句还支持LIMIT子句以限制输出中的行数。（以前，LIMIT是允许的，但不起作用。）

VALUES语句在列值的数据类型方面是宽松的；您可以在同一列中混合类型，如下所示：

mysql> VALUES ROW("q", 42, '2019-12-18'),
 ->     ROW(23, "abc", 98.6),
 ->     ROW(27.0002, "Mary Smith", '{"a": 10, "b": 25}');
+----------+------------+--------------------+
| column_0 | column_1   | column_2           |
+----------+------------+--------------------+
| q        | 42         | 2019-12-18         |
| 23       | abc        | 98.6               |
| 27.0002  | Mary Smith | {"a": 10, "b": 25} |
+----------+------------+--------------------+
3 rows in set (0.00 sec)

重要提示

具有一个或多个ROW()实例的VALUES充当表值构造函数；尽管它可以用于在INSERT或REPLACE语句中提供值，但不要将其与也用于此目的的VALUES关键字混淆。您也不要将其与指代INSERT ... ON DUPLICATE KEY UPDATE中列值的VALUES()函数混淆。

您还应该记住ROW()是一个行值构造函数（参见 Section 15.2.15.5, “Row Subqueries”)，而VALUES ROW()是一个表值构造函数；这两者不能互换使用。

VALUES 可以在许多情况下使用 SELECT，包括以下情况：

使用 UNION，如下所示：

mysql> SELECT 1,2 UNION SELECT 10,15;
+----+----+
| 1  | 2  |
+----+----+
|  1 |  2 |
| 10 | 15 |
+----+----+
2 rows in set (0.00 sec)

mysql> VALUES ROW(1,2) UNION VALUES ROW(10,15);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|       10 |       15 |
+----------+----------+
2 rows in set (0.00 sec)

您可以将多行构建的表联合在一起，就像这样：

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
     >     UNION VALUES ROW(10,15),ROW(20,25);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        3 |        4 |
|        5 |        6 |
|       10 |       15 |
|       20 |       25 |
+----------+----------+
5 rows in set (0.00 sec)

在这种情况下，您也可以（通常更可取地）完全省略 UNION 并使用单个 VALUES 语句，就像这样：

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6), ROW(10,15), ROW(20,25);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        3 |        4 |
|        5 |        6 |
|       10 |       15 |
|       20 |       25 |
+----------+----------+

VALUES 也可以与 SELECT 语句、TABLE 语句或两者一起在联合中使用。

UNION 中的构建表必须包含相同数量的列，就像您使用 SELECT 一样。有关更多示例，请参见 Section 15.2.18, “UNION Clause”。

在 MySQL 8.0.31 及更高版本中，您可以像使用 UNION 一样使用 EXCEPT 和 INTERSECT 与 VALUES，如下所示：

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
 ->   INTERSECT 
 -> VALUES ROW(10,15), ROW(20,25), ROW(3,4);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 |        4 |
+----------+----------+
1 row in set (0.00 sec)

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
 ->   EXCEPT 
 -> VALUES ROW(10,15), ROW(20,25), ROW(3,4);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        5 |        6 |
+----------+----------+
2 rows in set (0.00 sec)

有关更多信息，请参见 Section 15.2.4, “EXCEPT Clause” 和 Section 15.2.8, “INTERSECT Clause”。

在连接中。有关更多信息和示例，请参见 Section 15.2.13.2, “JOIN Clause”。
在 INSERT 或 REPLACE 语句中代替 VALUES()，在这种情况下，其语义与此处描述的略有不同。有关详细信息，请参见 Section 15.2.7, “INSERT Statement”。
在 CREATE TABLE ... SELECT 和 CREATE VIEW ... SELECT 中代替源表。有关更多信息和示例，请参见这些语句的描述。

15.2.20 WITH（公共表达式）

原文：dev.mysql.com/doc/refman/8.0/en/with.html

公共表达式（CTE）是存在于单个语句范围内的命名临时结果集，可以在该语句中稍后引用，可能多次。以下讨论描述了如何编写使用 CTE 的语句。

公共表达式
递归公共表达式
限制公共表达式递归
递归公共表达式示例
与类似结构比较的公共表达式

有关 CTE 优化的信息，请参见第 10.2.2.4 节，“使用合并或材料化优化派生表、视图引用和公共表达式”。

其他资源

这些文章包含有关在 MySQL 中使用 CTEs 的其他信息，包括许多示例：

公共表达式

要指定公共表达式，请使用具有一个或多个逗号分隔子句的WITH")子句。每个子句提供一个生成结果集的子查询，并将一个名称与子查询关联。以下示例在WITH")子句中定义了名为cte1和cte2的 CTE，并在跟随WITH")子句的顶层SELECT中引用它们：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

在包含WITH")子句的语句中，每个 CTE 名称都可以被引用以访问相应的 CTE 结果集。

CTE 名称可以在其他 CTE 中引用，使得 CTE 可以基于其他 CTE 定义。

CTE 可以引用自身来定义递归 CTE。递归 CTE 的常见应用包括序列生成和遍历分层或树形数据。

公共表达式是 DML 语句语法的可选部分。它们使用WITH")子句定义：

*with_clause*:
    WITH [RECURSIVE]
        *cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*)
        [, *cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*)] ...

*cte_name*指定一个单个公共表达式，并且可以在包含WITH")子句的语句中作为表引用。

AS (*子查询*)部分中的*子查询*称为“CTE 的子查询”，并生成 CTE 结果集。AS后面的括号是必需的。

如果 CTE 的子查询引用其自身的名称，则该公共表达式是递归的。如果WITH")子句中的任何 CTE 是递归的，则必须包含RECURSIVE关键字。有关更多信息，请参见递归公共表达式。

给定 CTE 的列名确定如下：

如果 CTE 名称后跟着一个括号括起的名称列表，则这些名称是列名：
```
WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
```
名称列表中的名称数量必须与结果集中的列数相同。

否则，列名来自AS (*子查询*)部分中第一个SELECT的选择列表：

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

在以下情况下允许使用WITH")子句：

在SELECT、UPDATE和DELETE语句的开头。

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

在子查询（包括派生表子查询）的开头：

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

在包含SELECT语句的语句之前：

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

同一级别只允许一个WITH")子句。不允许在同一级别后跟WITH")再跟WITH")，因此这是不合法的：

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

要使语句合法，使用一个单独的WITH")子句，通过逗号分隔子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

但是，如果它们出现在不同级别，则语句可以包含多个WITH")子句：

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

WITH")子句可以定义一个或多个公共表达式，但每个 CTE 名称必须对该子句唯一。这是不合法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...

要使语句合法，定义具有唯一名称的 CTE：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE 可以引用自身或其他 CTE：

自引用 CTE 是递归的。
CTE 可以引用在同一WITH")子句中较早定义的 CTE，但不能引用稍后定义的 CTE。

这个约束排除了相互递归的 CTE，其中cte1引用cte2，而cte2引用cte1。这些引用中的一个必须是对稍后定义的 CTE 的引用，这是不允许的。
在给定查询块中，CTE 可以引用更外层级别的查询块中定义的 CTE，但不能引用更内层级别的查询块中定义的 CTE。

对于解析具有相同名称的对象引用，派生表隐藏 CTE；而 CTE 隐藏基本表、TEMPORARY表和视图。名称解析通过在同一查询块中搜索对象，然后依次在外部块中查找，直到找到具有该名称的对象为止。

与派生表类似，在 MySQL 8.0.14 之前，CTE 不能包含外部引用。这是 MySQL 在 MySQL 8.0.14 中解除的限制，而不是 SQL 标准的限制。有关递归 CTE 的特定语法考虑事项，请参见递归公共表达式。

递归公共表达式

递归公共表达式是具有引用其自身名称的子查询的表达式。例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

当执行时，该语句产生这样的结果，一个包含简单线性序列的单列：

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

递归 CTE 具有以下结构：

如果WITH子句中的任何 CTE 引用自身，则WITH子句必须以WITH RECURSIVE开头。（如果没有 CTE 引用自身，则允许使用RECURSIVE但不是必需的。）

如果忘记为递归 CTE 添加RECURSIVE，则可能会出现以下错误：
```
ERROR 1146 (42S02): Table '*cte_name*' doesn't exist
```
递归 CTE 子查询有两部分，由UNION ALL或UNION [DISTINCT]分隔：
```
SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets
```
第一个 SELECT 生成 CTE 的初始行或行，并不引用 CTE 名称。第二个 SELECT 生成额外的行并通过在其 FROM 子句中引用 CTE 名称进行递归。当这部分不再生成新行时，递归结束。因此，递归 CTE 由一个非递归的 SELECT 部分后跟一个递归的 SELECT 部分组成。

每个 SELECT 部分本身可以是多个 SELECT 语句的联合。
CTE 结果列的类型是从非递归 SELECT 部分的列类型推断出来的，所有列都是可空的。对于类型确定，递归 SELECT 部分将被忽略。
如果非递归部分和递归部分由 UNION DISTINCT 分隔，重复行将被消除。这对执行传递闭包的查询很有用，以避免无限循环。
递归部分的每次迭代仅在前一次迭代生成的行上操作。如果递归部分有多个查询块，每个查询块的迭代按照未指定的顺序进行安排，并且每个查询块操作的行是由其前一次迭代或自其前一次迭代结束以来其他查询块生成的行。

之前显示的递归 CTE 子查询有这个非递归部分，用于检索单行以生成初始行集合：

SELECT 1

CTE 子查询也有这个递归部分：

SELECT n + 1 FROM cte WHERE n < 5

在每次迭代中，SELECT 会生成一个新值，比上一行集合中的 n 的值大 1。第一次迭代操作初始行集合（1）并生成 1+1=2；第二次迭代操作第一次迭代的行集合（2）并生成 2+1=3；依此类推。直到递归结束，即当 n 不再小于 5 时。

如果 CTE 的递归部分为某列生成了比非递归部分更宽的值，可能需要扩展非递归部分中的列以避免数据截断。考虑以下语句：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

在非严格 SQL 模式下，该语句会产生如下输出：

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

str 列的值都是 'abc'，因为非递归 SELECT 确定了列宽度。因此，递归 SELECT 生成的更宽的 str 值会被截断。

在严格 SQL 模式下，该语句会产生错误：

ERROR 1406 (22001): Data too long for column 'str' at row 1

为了解决这个问题，使语句不产生截断或错误，可以在非递归的SELECT中使用CAST()来使str列变宽：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

现在该语句产生了这个结果，没有截断：

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

列通过名称访问，而不是位置，这意味着递归部分中的列可以访问非递归部分中位置不同的列，正如这个 CTE 所示：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

因为一行中的p是从前一行中的q派生的，反之亦然，所以输出的每一行中正负值交换位置：

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

一些语法约束适用于递归 CTE 子查询：

递归SELECT部分不能包含以下结构：
- 聚合函数如SUM()
- 窗口函数
- GROUP BY
- ORDER BY
- DISTINCT
在 MySQL 8.0.19 之前，递归 CTE 的递归SELECT部分也不能使用LIMIT子句。这个限制在 MySQL 8.0.19 中被解除，现在在这种情况下支持LIMIT，还有一个可选的OFFSET子句。结果集的效果与在最外层SELECT中使用LIMIT时相同，但更有效，因为在递归SELECT中使用它会在生成请求的行数后立即停止生成行。

这些约束不适用于递归 CTE 的非递归SELECT部分。对DISTINCT的禁止仅适用于UNION成员；允许UNION DISTINCT。
递归的SELECT部分必须仅在其FROM子句中引用 CTE 一次，而且不能在任何子查询中引用。它可以引用除 CTE 之外的其他表，并将它们与 CTE 连接起来。如果在这样的连接中使用，CTE 不能位于LEFT JOIN的右侧。

这些约束来自 SQL 标准，除了 MySQL 特定的排除ORDER BY、LIMIT（MySQL 8.0.18 及更早版本）和DISTINCT之外。

对于递归 CTE，EXPLAIN输出的递归SELECT部分在Extra列中显示Recursive。

EXPLAIN显示的成本估计表示每次迭代的成本，这可能与总成本有很大差异。优化器无法预测迭代次数，因为它无法预测WHERE子句何时变为 false。

CTE 的实际成本也可能受到结果集大小的影响。产生许多行的 CTE 可能需要一个足够大的内部临时表，以便从内存转换为磁盘格式，并可能遭受性能损失。如果是这样，增加允许的内存临时表大小可能会提高性能；参见第 10.4.4 节，“MySQL 中的内部临时表使用”。

限制公共表达式递归

对于递归 CTE 非常重要的是，递归的SELECT部分包含一个终止递归的条件。作为一种开发技术，防止递归 CTE 失控的方法是通过设置执行时间限制来强制终止：

cte_max_recursion_depth系统变量为 CTE 的递归级别数量设置了限制。服务器会终止任何递归超过此变量值的 CTE 的执行。
max_execution_time系统变量强制执行当前会话中执行的SELECT语句的执行超时。
MAX_EXECUTION_TIME优化提示为其中出现的SELECT语句强制执行每个查询的执行超时。

假设递归 CTE 被错误地编写为没有递归执行终止条件：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下，cte_max_recursion_depth的值为 1000，当递归超过 1000 级时，CTE 会终止。应用程序可以更改会话值以满足其需求：

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

你也可以设置全局cte_max_recursion_depth值，以影响随后开始的所有会话。

对于执行缓慢且因此递归的查询，或者有理由将cte_max_recursion_depth值设置得非常高的情况，另一种防止深度递归的方法是设置每个会话的超时时间。为此，在执行 CTE 语句之前执行类似以下语句：

SET max_execution_time = 1000; -- impose one second timeout

或者，在 CTE 语句本身中包含一个优化提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

从 MySQL 8.0.19 开始，你也可以在递归查询中使用LIMIT来对最外层的SELECT返回的最大行数进行限制，例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

你可以在设置时间限制之外或代替设置时间限制。因此，以下 CTE 在返回一万行或运行一秒钟（1000 毫秒）后终止：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果没有执行时间限制的递归查询进入无限循环，您可以从另一个会话中使用KILL QUERY终止它。在会话本身中，用于运行查询的客户端程序可能提供了终止查询的方法。例如，在mysql中，键入Control+C会中断当前语句。

递归公共表达式示例

如前所述，递归公共表达式（CTEs）经常用于生成系列和遍历分层或树形结构数据。本节展示了这些技术的一些简单示例。

斐波那契数列生成
日期系列生成
分层数据遍历

斐波那契数列生成

斐波那契数列以数字 0 和 1（或 1 和 1）开始，之后每个数字都是前两个数字的和。如果递归SELECT生成的每行都可以访问系列中前两个数字，则递归公共表达式可以生成斐波那契数列。以下 CTE 使用 0 和 1 作为前两个数字生成了一个包含 10 个数字的系列：

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

CTE 生成了这个结果：

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

CTE 的工作原理如下：

n是一个显示列，表示该行包含第n个斐波那契数。例如，第 8 个斐波那契数是 13。
fib_n列显示斐波那契数n。
next_fib_n列显示数字n后面的下一个斐波那契数。该列为下一行提供了下一个系列值，以便该行可以在其fib_n列中生成前两个系列值的和。
当n达到 10 时，递归结束。这是一个任意选择，以限制输出为一小组行。

前面的输出显示了整个 CTE 结果。要仅选择其中的一部分，请在顶层SELECT中添加适当的WHERE子句。例如，要选择第 8 个斐波那契数，执行以下操作：

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

日期系列生成

公共表达式可以生成一系列连续的日期，这对于生成包含系列中所有日期的行的摘要非常有用，包括未在摘要数据中表示的日期。

假设销售数字表包含以下行：

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

此查询总结了每天的销售情况：

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

然而，该结果对于表跨越的日期范围中未表示的日期存在“空洞”。可以使用递归 CTE 生成该日期集合，然后与销售数据进行LEFT JOIN连接以生成表示范围内所有日期的结果。

这是生成日期范围系列的 CTE：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

CTE 生成此结果：

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

CTE 的工作原理：

非递归SELECT生成sales表跨度日期范围内最早的日期。
递归SELECT生成的每行将日期增加一天到前一行生成的日期。
当日期达到sales表跨度的日期范围内的最高日期时，递归结束。

将 CTE 与sales表进行LEFT JOIN连接，生成每个日期范围内的销售摘要：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

输出如下所示：

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

一些需要注意的要点：

查询是否低效，特别是对于递归SELECT中每行执行的包含MAX()子查询？EXPLAIN显示包含MAX()的子查询仅评估一次，并且结果被缓存。
使用COALESCE()避免在sales表中没有销售数据的日期中在sum_price列中显示NULL。

分层数据遍历

递归通用表达式对于遍历形成层次结构的数据非常有用。考虑以下创建一个小数据集的语句，该数据集显示公司中每个员工的员工姓名和 ID 号，以及员工的经理的 ID。顶层员工（CEO）的经理 ID 为NULL（没有经理）。

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

结果数据集如下所示：

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

要为每个员工生成组织结构图和每个员工的管理链（即从 CEO 到员工的路径），请使用递归 CTE：

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

CTE 生成此输出：

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

CTE 的工作原理：

非递归SELECT生成 CEO 的行（具有NULL管理者 ID 的行）。

path列扩展为CHAR(200)，以确保递归SELECT生成的较长path值有足够的空间。
递归SELECT生成的每行找到所有直接向前一行生成的员工报告的员工。对于每个这样的员工，行包括员工 ID 和姓名，以及员工的管理链。链是经理的链，员工 ID 添加到末尾。
当员工没有其他人向他们报告时，递归结束。

要查找特定员工或员工的路径，请在顶层SELECT添加WHERE子句。例如，要显示 Tarek 和 Sarah 的结果，请修改SELECT如下：

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

与类似结构比较的通用表达式

通用表达式（CTEs）在某些方面类似于派生表：

两种结构都有名称。
两种结构存在于单个语句的范围内。

由于这些相似之处，CTE 和派生表通常可以互换使用。作为一个简单的例子，以下语句是等价的：

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

然而，CTE 相对于派生表有一些优势：

派生表只能在查询中引用一次。而 CTE 可以被多次引用。要使用派生表结果的多个实例，必须多次派生结果。
一个 CTE 可以是自引用的（递归的）。
一个 CTE 可以引用另一个 CTE。
一个 CTE 在语句中的定义出现在开头时可能更容易阅读，而不是嵌入其中。

CTE 类似于使用CREATE [TEMPORARY] TABLE创建的表，但不需要显式定义或删除。对于 CTE，您无需拥有创建表的权限。

15.3 事务和锁定语句

原文：dev.mysql.com/doc/refman/8.0/en/sql-transactional-statements.html

15.3.1 START TRANSACTION、COMMIT 和 ROLLBACK 语句

15.3.2 无法回滚的语句

15.3.3 导致隐式提交的语句

15.3.4 SAVEPOINT、ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 语句

15.3.5 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE 语句

15.3.6 LOCK TABLES 和 UNLOCK TABLES 语句

15.3.7 SET TRANSACTION 语句

15.3.8 XA 事务

MySQL 通过诸如SET autocommit、START TRANSACTION、COMMIT 和 ROLLBACK等语句支持本地事务（在给定的客户端会话中）。请参阅第 15.3.1 节，“START TRANSACTION, COMMIT, and ROLLBACK Statements”。XA 事务支持使 MySQL 能够参与分布式事务。请参阅第 15.3.8 节，“XA Transactions”。

15.3.1 `START TRANSACTION`、`COMMIT`和`ROLLBACK`语句

原文：dev.mysql.com/doc/refman/8.0/en/commit.html

START TRANSACTION
    [*transaction_characteristic* [, *transaction_characteristic*] ...]

*transaction_characteristic*: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

这些语句提供了对事务的控制：

START TRANSACTION 或 BEGIN 开始一个新事务。
COMMIT 提交当前事务，使其更改永久生效。
ROLLBACK 回滚当前事务，取消其更改。
SET autocommit 禁用或启用当前会话的默认自动提交模式。

默认情况下，MySQL 运行时启用自动提交模式。这意味着，当不在事务内时，每个语句都是原子的，就好像被 START TRANSACTION 和 COMMIT 包围。您无法使用 ROLLBACK 撤消效果；但是，如果在执行语句时发生错误，则会回滚该语句。

要隐式禁用单个语句序列的自动提交模式，请使用 START TRANSACTION 语句：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

使用 START TRANSACTION，自动提交保持禁用，直到您使用 COMMIT 或 ROLLBACK 结束事务。然后自动提交模式恢复到先前的状态。

START TRANSACTION 允许多个修饰符控制事务特性。要指定多个修饰符，请用逗号分隔它们。

WITH CONSISTENT SNAPSHOT 修饰符为能够执行此操作的存储引擎启动一致性读取。这仅适用于 InnoDB。其效果与发出 START TRANSACTION 然后从任何 InnoDB 表中进行 SELECT 相同。请参阅 Section 17.7.2.3, “Consistent Nonlocking Reads”。WITH CONSISTENT SNAPSHOT 修饰符不会更改当前事务的隔离级别，因此仅在当前隔离级别允许一致性读取时才提供一致的快照。唯一允许一致性读取的隔离级别是 REPEATABLE READ。对于所有其他隔离级别，WITH CONSISTENT SNAPSHOT 子句将被忽略。当忽略 WITH CONSISTENT SNAPSHOT 子句时会生成警告。
READ WRITE 和 READ ONLY 修饰符设置事务访问模式。它们允许或禁止对事务中使用的表进行更改。READ ONLY 限制阻止事务修改或锁定对其他事务可见的事务和非事务表；事务仍然可以修改或锁定临时表。

当事务已知为只读时，MySQL 在InnoDB表上的查询会启用额外的优化。指定READ ONLY可确保在无法自动确定只读状态的情况下应用这些优化。有关更多信息，请参阅第 10.5.3 节，“优化 InnoDB 只读事务”。

如果未指定访问模式，则应用默认模式。除非默认值已更改，否则为读/写。在同一语句中不允许同时指定READ WRITE和READ ONLY。

在只读模式下，仍然可以使用 DML 语句更改使用TEMPORARY关键字创建的表。使用 DDL 语句进行的更改是不允许的，就像永久表一样。

有关事务访问模式的其他信息，包括更改默认模式的方法，请参阅第 15.3.7 节，“SET TRANSACTION 语句”。

如果启用了read_only系统变量，则使用START TRANSACTION READ WRITE显式启动事务需要CONNECTION_ADMIN权限（或已弃用的SUPER权限）。

重要提示

许多用于编写 MySQL 客户端应用程序的 API（如 JDBC）提供了自己的启动事务方法，可以（有时应该）代替从客户端发送START TRANSACTION语句。有关更多信息，请参阅第三十一章，“连接器和 API”，或您的 API 文档。

要显式禁用自动提交模式，请使用以下语句：

SET autocommit=0;

将autocommit变量设置为零以禁用自动提交模式后，对事务安全表（例如InnoDB或NDB）的更改不会立即生效。您必须使用COMMIT将更改存储到磁盘，或使用ROLLBACK忽略更改。

autocommit 是一个会话变量，必须为每个会话设置。要为每个新连接禁用自动提交模式，请参阅autocommit系统变量在第 7.1.8 节，“服务器系统变量”的描述。

BEGIN和BEGIN WORK可作为START TRANSACTION的别名来启动事务。START TRANSACTION是标准 SQL 语法，是启动临时事务的推荐方式，并允许BEGIN不支持的修饰符。

BEGIN语句与使用BEGIN关键字开始BEGIN ... END复合语句的方式不同。后者不会开始事务。请参见第 15.6.1 节，“BEGIN ... END 复合语句”。

注意

在所有存储程序（存储过程和函数、触发器和事件）中，解析器将BEGIN [WORK]视为BEGIN ... END块的开始。在这种情况下使用START TRANSACTION开始一个事务。

可选的WORK关键字支持COMMIT和ROLLBACK，以及CHAIN和RELEASE子句。CHAIN和RELEASE可用于对事务完成进行额外控制。completion_type系统变量的值确定默认完成行为。请参见第 7.1.8 节，“服务器系统变量”。

AND CHAIN子句导致新事务在当前事务结束后立即开始，并且新事务具有与刚终止事务相同的隔离级别。新事务还使用与刚终止事务相同的访问模式（READ WRITE或READ ONLY）。RELEASE子句导致服务器在终止当前事务后断开当前客户端会话。包括NO关键字可以抑制CHAIN或RELEASE完成，如果completion_type系统变量设置为默认导致链接或释放完成时，这可能很有用。

开始事务会导致任何待处理的事务被提交。更多信息请参见第 15.3.3 节，“导致隐式提交的语句”。

开始事务还会导致使用LOCK TABLES获取的表锁被释放，就好像执行了UNLOCK TABLES一样。开始事务不会释放使用FLUSH TABLES WITH READ LOCK获取的全局读锁。

为了获得最佳结果，事务应仅使用单个事务安全存储引擎管理的表执行。否则，可能会出现以下问题：

如果使用来自多个事务安全存储引擎（如InnoDB）的表，并且事务隔离级别不是SERIALIZABLE，那么当一个事务提交时，使用相同表的另一个正在进行的事务可能只看到第一个事务所做的一部分更改。也就是说，混合引擎的事务的原子性不能得到保证，可能会导致不一致性。（如果混合引擎事务不频繁，您可以使用SET TRANSACTION ISOLATION LEVEL根据需要在每个事务基础上将隔离级别设置为SERIALIZABLE。）
如果在事务中使用非事务安全的表，那么对这些表的更改将立即存储，而不考虑自动提交模式的状态。
如果在事务中更新非事务表后发出ROLLBACK语句，将会出现ER_WARNING_NOT_COMPLETE_ROLLBACK警告。事务安全表的更改将被回滚，但非事务安全表的更改不会被回滚。

每个事务在COMMIT时以一个块的形式存储在二进制日志中。被回滚的事务不会被记录。（例外：对非事务表的修改无法回滚。如果被回滚的事务包括对非事务表的修改，则整个事务将在结尾处使用ROLLBACK语句记录，以确保对非事务表的修改被复制。）请参阅 Section 7.4.4, “The Binary Log”。

你可以使用SET TRANSACTION语句更改事务的隔离级别或访问模式。请参阅 Section 15.3.7, “SET TRANSACTION Statement”。

回滚可能是一个缓慢的操作，可能会在用户没有明确要求的情况下隐式发生（例如，当发生错误时）。因此，SHOW PROCESSLIST在会话的State列中显示Rolling back，不仅适用于使用ROLLBACK语句执行的显式回滚，还适用于隐式回滚。

注意

在 MySQL 8.0 中，BEGIN、COMMIT和ROLLBACK不受--replicate-do-db或--replicate-ignore-db规则的影响。

当InnoDB执行事务的完全回滚时，事务设置的所有锁都会被释放。如果事务中的一个 SQL 语句由于错误（如重复键错误）而回滚，那么该语句设置的锁将在事务保持活动状态时保留。这是因为InnoDB以一种格式存储行锁，以至于事后无法知道哪个锁是由哪个语句设置的。

如果事务中的一个SELECT语句调用了一个存储函数，并且存储函数中的一个语句失败，那么该语句将会回滚。如果随后为该事务执行ROLLBACK，整个事务将会回滚。

15.3.2 无法回滚的语句

原文：dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

有些语句是无法回滚的。一般来说，这些包括数据定义语言（DDL）语句，比如创建或删除数据库的语句，创建、删除或修改表或存储过程的语句。

你应该设计你的事务不包括这样的语句。如果你在事务早期发出一个无法回滚的语句，然后稍后另一个语句失败，那么在这种情况下，通过发出一个ROLLBACK语句无法完全回滚事务的效果。

15.3.3 导致隐式提交的语句

原文：dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

本节列出的语句（以及它们的任何同义词）会隐式结束当前会话中的任何活动事务，就好像在执行该语句之前已经执行了一个COMMIT。

大多数这些语句在执行后也会导致隐式提交。其目的是在其自己的特殊事务中处理每个这样的语句。事务控制和锁定语句是例外情况：如果在执行之前发生隐式提交，则在之后不会再发生另一个提交。

数据定义语言（DDL）语句用于定义或修改数据库对象。 ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER TABLESPACE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE SPATIAL REFERENCE SYSTEM, CREATE TABLE, CREATE TABLESPACE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP SPATIAL REFERENCE SYSTEM, DROP TABLE, DROP TABLESPACE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN, RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN.

使用 TEMPORARY 关键字的 CREATE TABLE 和 DROP TABLE 语句不会提交事务。（这不适用于对临时表的其他操作，如 ALTER TABLE 和 CREATE INDEX，这些操作会导致提交。）然而，虽然没有隐式提交，但也不能回滚该语句，这意味着使用这些语句会违反事务的原子性。例如，如果使用 CREATE TEMPORARY TABLE 然后回滚事务，表仍然存在。

在 InnoDB 中，CREATE TABLE 语句被处理为单个事务。这意味着用户的 ROLLBACK 不会撤消用户在该事务期间进行的 CREATE TABLE 语句。

当创建非临时表时，CREATE TABLE ... SELECT 在执行语句之前和之后会导致隐式提交。（对于 CREATE TEMPORARY TABLE ... SELECT 不会发生提交。）
隐式使用或修改 mysql 数据库中表的语句。 ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD.
事务控制和锁定语句。 BEGIN, LOCK TABLES, 如果值尚未为 1，则 SET autocommit = 1， START TRANSACTION, UNLOCK TABLES.

只有当使用 LOCK TABLES 获取非事务表锁时，UNLOCK TABLES 才会提交事务。对于跟随 FLUSH TABLES WITH READ LOCK 的 UNLOCK TABLES 不会发生提交，因为后者不会获取表级锁。

事务不能嵌套。这是在发出START TRANSACTION语句或其同义词时为当前事务执行的隐式提交的结果。

导致隐式提交的语句不能在事务处于ACTIVE状态时用于 XA 事务。

BEGIN语句与开始BEGIN ... END复合语句的BEGIN关键字的使用不同。后者不会导致隐式提交。请参阅 Section 15.6.1, “BEGIN ... END Compound Statement”。
数据加载语句。 LOAD DATA。LOAD DATA仅对使用NDB存储引擎的表造成隐式提交。
管理语句。 ANALYZE TABLE，CACHE INDEX，CHECK TABLE，FLUSH，LOAD INDEX INTO CACHE，OPTIMIZE TABLE，REPAIR TABLE，RESET（但不包括RESET PERSIST）。
复制控制语句。START REPLICA，STOP REPLICA，RESET REPLICA，CHANGE REPLICATION SOURCE TO，CHANGE MASTER TO。在 MySQL 8.0.22 中，SLAVE 关键字被 REPLICA 替换。

15.3.4 保存点、回滚到保存点和释放保存点语句

原文：dev.mysql.com/doc/refman/8.0/en/savepoint.html

SAVEPOINT *identifier*
ROLLBACK [WORK] TO [SAVEPOINT] *identifier*
RELEASE SAVEPOINT *identifier*

InnoDB支持 SQL 语句SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT以及用于ROLLBACK的可选WORK关键字。

SAVEPOINT语句使用*identifier*设置具有名称的事务保存点。如果当前事务具有相同名称的保存点，则旧保存点将被删除，并设置一个新的保存点。

ROLLBACK TO SAVEPOINT语句将事务回滚到指定的保存点，而不终止事务。在回滚中，当前事务在设置保存点后对行所做的修改将被撤消，但InnoDB不会释放保存点后存储在内存中的行锁。（对于新插入的行，锁信息由存储在行中的事务 ID 携带；锁不会单独存储在内存中。在这种情况下，行锁在撤消时被释放。）比指定保存点设置的保存点将被删除。

如果ROLLBACK TO SAVEPOINT语句返回以下错误，则表示不存在具有指定名称的保存点：

ERROR 1305 (42000): SAVEPOINT *identifier* does not exist

RELEASE SAVEPOINT语句从当前事务的保存点集中移除指定的保存点。不会发生提交或回滚。如果保存点不存在，则会报错。

如果执行COMMIT或未命名保存点的ROLLBACK，则会删除当前事务的所有保存点。

当调用存储函数或触发器被激活时，将创建一个新的保存点级别。之前级别上的保存点变得不可用，因此不会与新级别上的保存点发生冲突。当函数或触发器终止时，它创建的任何保存点都会被释放，并且之前的保存点级别会被恢复。

15.3.5 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE 语句

原文：dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

LOCK INSTANCE FOR BACKUP

UNLOCK INSTANCE

LOCK INSTANCE FOR BACKUP获取一个实例级别的备份锁，允许在在线备份期间进行 DML 操作，同时阻止可能导致不一致快照的操作。

执行LOCK INSTANCE FOR BACKUP语句需要BACKUP_ADMIN权限。当从早期版本升级到 MySQL 8.0 时，具有RELOAD权限的用户会自动被授予BACKUP_ADMIN权限。

多个会话可以同时持有备份锁。

UNLOCK INSTANCE释放当前会话持有的备份锁。如果会话终止，会话持有的备份锁也会被释放。

LOCK INSTANCE FOR BACKUP阻止创建、重命名或删除文件。REPAIR TABLE TRUNCATE TABLE、OPTIMIZE TABLE和账户管理语句被阻止。参见第 15.7.1 节，“账户管理语句”。还会阻止修改未记录在InnoDB重做日志中的InnoDB文件的操作。

LOCK INSTANCE FOR BACKUP允许执行仅影响用户创建的临时表的 DDL 操作。实际上，在持有备份锁时，可以创建、重命名或删除属于用户创建的临时表的文件。也允许创建二进制日志文件。

在实例上有效的LOCK INSTANCE FOR BACKUP语句生效期间，不应发出PURGE BINARY LOGS语句，因为这违反了备份锁的规则，会从服务器中删除文件。从 MySQL 8.0.28 开始，这是不允许的。

由LOCK INSTANCE FOR BACKUP获取的备份锁独立于事务锁和由FLUSH TABLES *tbl_name* [, *tbl_name*] ... WITH READ LOCK获取的锁，并允许以下语句序列：

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES *tbl_name* [, *tbl_name*] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;

FLUSH TABLES *tbl_name* [, *tbl_name*] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

lock_wait_timeout设置定义了LOCK INSTANCE FOR BACKUP语句在放弃之前等待获取锁的时间。

15.3.6 `LOCK TABLES` 和 `UNLOCK TABLES` 语句

原文：dev.mysql.com/doc/refman/8.0/en/lock-tables.html

LOCK {TABLE | TABLES}
    *tbl_name* [[AS] *alias*] *lock_type*
    [, *tbl_name* [[AS] *alias*] *lock_type*] ...

*lock_type*: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK {TABLE | TABLES}

MySQL 允许客户端会话明确为协作访问表的其他会话或在会话需要独占访问表时阻止其他会话修改表而获取表锁。一个会话只能为自己获取或释放锁。一个会话不能为另一个会话获取锁或释放另一个会话持有的锁。

锁可以用于模拟事务或在更新表时获得更快的速度。这在 Table-Locking Restrictions and Conditions 中有更详细的解释。

LOCK TABLES 明确为当前客户端会话获取表锁。表锁可以用于基表或视图。你必须拥有 LOCK TABLES 权限，并且对每个要锁定的对象都必须拥有 SELECT 权限。

对于视图锁定，LOCK TABLES 会将视图中使用的所有基表添加到要锁定的表集合中，并自动锁定它们。对于任何被锁定视图下的表，LOCK TABLES 会检查视图定义者（对于 SQL SECURITY DEFINER 视图）或调用者（对于所有视图）对表具有适当的权限。

如果你使用 LOCK TABLES 明确锁定一个表，那么任何触发器中使用的表也会隐式锁定，如 LOCK TABLES and Triggers 中所述。

如果你使用 LOCK TABLES 明确锁定一个表，那么任何通过外键约束相关的表会被隐式打开并锁定。对于外键检查，相关表会被采取共享只读锁（LOCK TABLES READ）。对于级联更新，涉及操作的相关表会被采取共享无写锁（LOCK TABLES WRITE）。

UNLOCK TABLES 明确释放当前会话持有的任何表锁。在获取新锁之前，LOCK TABLES 会隐式释放当前会话持有的任何表锁。

UNLOCK TABLES的另一个用途是释放使用FLUSH TABLES WITH READ LOCK语句获取的全局读锁，该语句使您可以锁定所有数据库中的所有表。请参见第 15.7.8.3 节，“FLUSH 语句”。（如果您有可以在某个时间点进行快照的文件系统（如 Veritas），这是一个非常方便的备份方式。）

LOCK TABLE是LOCK TABLES的同义词；UNLOCK TABLE是UNLOCK TABLES的同义词。

表锁仅防止其他会话进行不当读取或写入。持有WRITE锁的会话可以执行诸如DROP TABLE或TRUNCATE TABLE等表级操作。对于持有READ锁的会话，不允许执行DROP TABLE和TRUNCATE TABLE操作。

以下讨论仅适用于非TEMPORARY表。对于TEMPORARY表，允许（但会被忽略）使用LOCK TABLES。该表可以在创建它的会话中自由访问，而不受其他锁定的影响。不需要锁定，因为没有其他会话可以看到该表。

表锁定获取
表锁定释放
表锁定和事务的交互
LOCK TABLES 和触发器
表锁定的限制和条件

表锁定获取

要在当前会话中获取表锁，请使用LOCK TABLES语句，该语句获取元数据锁（参见第 10.11.4 节，“元数据锁定”）。

可用的锁类型如下：

READ [LOCAL]锁：

拥有锁的会话可以读取表（但不能写入）。
多个会话可以同时为表获取READ锁。
其他会话可以在不显式获取READ锁的情况下读取表。
LOCAL 修饰符允许其他会话在持有锁的情况下执行非冲突的 INSERT 语句（并发插入）。但是，如果在持有锁的同时使用外部进程操作数据库，则不能使用 READ LOCAL。对于 InnoDB 表，READ LOCAL 与 READ 相同。

[LOW_PRIORITY] WRITE 锁：

持有锁的会话可以读取和写入表。
只有持有锁的会话才能访问表。在锁被释放之前，其他会话无法访问它。
当持有 WRITE 锁时，其他会话对表的锁请求将被阻塞。
LOW_PRIORITY 修饰符没有影响。在 MySQL 的早期版本中，它会影响锁定行为，但现在不再有效。它现在已被弃用，使用 WRITE 而不带 LOW_PRIORITY 会产生警告。

WRITE 锁通常比 READ 锁具有更高的优先级，以确保更新尽快处理。这意味着如果一个会话获取了 READ 锁，然后另一个会话请求 WRITE 锁，后续的 READ 锁请求将等待，直到请求 WRITE 锁的会话获取并释放锁。（对于 max_write_lock_count 系统变量的小值，可能会有例外情况；请参见 Section 10.11.4, “Metadata Locking”.）

如果由于其他会话在任何表上持有的锁而导致 LOCK TABLES 语句必须等待，它将阻塞直到所有锁都可以被获取。

需要锁定表的会话必须在单个 LOCK TABLES 语句中获取所有需要的锁。在持有这些锁的情况下，会话只能访问被锁定的表。例如，在以下语句序列中，尝试访问 t2 会导致错误，因为它在 LOCK TABLES 语句中未被锁定：

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

INFORMATION_SCHEMA 数据库中的表是一个例外。即使一个会话持有使用 LOCK TABLES 获取的表锁，也可以在不显式锁定的情况下访问它们。

不能在单个查询中多次引用具有相同名称的锁定表。请改用别名，并为表和每个别名获取单独的锁：

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

第一个INSERT出现错误是因为对于一个被锁定的表有两个相同名称的引用。第二个INSERT成功是因为对表的引用使用了不同的名称。

如果您的语句通过别名引用表，那么必须使用相同的别名锁定表。不能不指定别名而锁定表：

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反，如果您使用别名锁定表，那么在语句中必须使用该别名引用它：

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

表锁定释放

当会话持有的表锁被释放时，它们会同时被释放。一个会话可以显式释放其锁，或者在某些条件下锁可能会隐式释放。

一个会话可以通过UNLOCK TABLES显式释放其锁。
如果一个会话在已经持有锁的情况下发出LOCK TABLES语句以获取锁，那么在授予新锁之前，现有的锁会被隐式释放。
如果一个会话开始一个事务（例如，使用START TRANSACTION），会执行一个隐式的UNLOCK TABLES，导致现有的锁被释放。（有关表锁定和事务之间的交互的更多信息，请参阅表锁定和事务的交互.）

如果客户端会话的连接终止，无论是正常还是异常，服务器会隐式释放会话持有的所有表锁（事务性和非事务性）。如果客户端重新连接，那么这些锁将不再生效。此外，如果客户端有一个活动事务，在断开连接时服务器会回滚该事务，如果重新连接发生，则新会话将以自动提交启用的方式开始。因此，客户端可能希望禁用自动重新连接。启用自动重新连接时，如果重新连接发生，客户端不会收到通知，但任何表锁或当前事务都会丢失。禁用自动重新连接时，如果连接断开，下一个发出的语句将出现错误。客户端可以检测错误并采取适当的措施，如重新获取锁或重做事务。请参阅自动重新连接控制。

注意

如果你在一个被锁定的表上使用 ALTER TABLE，它可能会变为未锁定状态。例如，如果你尝试第二次 ALTER TABLE 操作，结果可能是一个错误 Table '*tbl_name*' was not locked with LOCK TABLES。为了处理这个问题，在第二次修改之前再次锁定表。另请参阅 Section B.3.6.1, “ALTER TABLE 存在的问题”。

表锁定和事务的交互

LOCK TABLES 和 UNLOCK TABLES 与事务的使用交互如下：

LOCK TABLES 不是事务安全的，并在尝试锁定表之前隐式提交任何活动事务。
UNLOCK TABLES 隐式提交任何活动事务，但仅在使用 LOCK TABLES 获取表锁时。例如，在以下一组语句中，UNLOCK TABLES 释放全局读锁但不提交事务，因为没有表锁生效：
```
FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
```
开始一个事务（例如，使用 START TRANSACTION）隐式提交任何当前事务并释放现有的表锁。
FLUSH TABLES WITH READ LOCK 获取全局读锁而不是表锁，因此它不受 LOCK TABLES 和 UNLOCK TABLES 关于表锁定和隐式提交的相同行为影响。例如，START TRANSACTION 不会释放全局读锁。请参见 Section 15.7.8.3, “FLUSH 语句”。
其他隐式导致事务提交的语句不会释放现有的表锁。有关此类语句的列表，请参见 Section 15.3.3, “导致隐式提交的语句”。
使用事务表（如InnoDB表）的正确方法是使用SET autocommit = 0（而不是START TRANSACTION）开始事务，然后使用LOCK TABLES，直到显式提交事务之前不要调用UNLOCK TABLES。例如，如果你需要向表t1写入并从表t2读取，你可以这样做：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
*... do something with tables t1 and t2 here ...* COMMIT;
UNLOCK TABLES;
```
当你调用LOCK TABLES时，InnoDB内部会获取自己的表锁，而 MySQL 会获取自己的表锁。InnoDB在下一次提交时释放其内部表锁，但要释放 MySQL 的表锁，你必须调用UNLOCK TABLES。你不应该设置autocommit = 1，因为这样InnoDB会在调用LOCK TABLES后立即释放其内部表锁，很容易发生死锁。如果设置autocommit = 1，InnoDB根本不会获取内部表锁，以帮助旧应用程序避免不必要的死锁。
ROLLBACK不会释放表锁。

表锁和触发器

如果使用LOCK TABLES显式锁定一个表，那么触发器中使用的任何表也会隐式锁定：

这些锁与使用LOCK TABLES语句显式获取的锁同时获取。
触发器中使用的表的锁取决于表是否仅用于读取。如果是，那么读取锁就足够了。否则，会使用写入锁。
如果一个表被使用LOCK TABLES显式锁定以供读取，但由于可能在触发器中被修改而需要写入锁定，那么会获取写入锁定而不是读取锁定。（也就是说，由于表在触发器中的出现导致对表的显式读取锁请求被转换为写入锁请求。）

假设你使用这个语句锁定了两个表，t1和t2：

LOCK TABLES t1 WRITE, t2 READ;

如果t1或t2有任何触发器，触发器中使用的表也会被锁定。假设t1有一个定义如下的触发器：

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

LOCK TABLES语句的结果是，t1和t2被锁定，因为它们出现在语句中，而t3和t4被锁定，因为它们在触发器中使用：

根据WRITE锁请求，t1被锁定为写入。
即使请求是读取锁，t2也被锁定为写入。这是因为在触发器内插入了t2，所以读取请求被转换为写入请求。
因为只在触发器内部读取，所以t3被锁定为只读。
因为可能在触发器内更新，所以t4被锁定为写入。

表锁定的限制和条件

您可以安全地使用KILL来终止等待表锁定的会话。请参阅第 15.7.8.4 节，“KILL 语句”。

不能在存储程序中使用LOCK TABLES和UNLOCK TABLES。

不能使用LOCK TABLES锁定performance_schema数据库中的表，除了setup_*xxx*表。

由LOCK TABLES生成的锁的范围是单个 MySQL 服务器。它与 NDB Cluster 不兼容，NDB Cluster 无法跨多个mysqld实例强制执行 SQL 级别的锁。您可以在 API 应用程序中强制执行锁定。有关更多信息，请参阅第 25.2.7.10 节，“与多个 NDB Cluster 节点相关的限制”。

在LOCK TABLES语句生效期间，禁止以下语句：CREATE TABLE，CREATE TABLE ... LIKE，CREATE VIEW，DROP VIEW以及对存储函数、存储过程和事件的 DDL 语句。

对于一些操作，必须访问mysql数据库中的系统表。例如，HELP语句需要服务器端帮助表的内容，而CONVERT_TZ()可能需要读取时区表。服务器会根据需要隐式锁定系统表以进行读取，因此您无需显式锁定它们。这些表被视为刚刚描述的方式：

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

如果您想要在任何表格上明确放置一个WRITE锁定，并使用LOCK TABLES语句，那么该表格必须是唯一被锁定的；没有其他表格可以与相同语句一起被锁定。

通常情况下，您不需要锁定表格，因为所有单个UPDATE语句都是原子的；没有其他会话可以干扰任何其他当前执行的 SQL 语句。然而，在一些情况下，锁定表格可能会带来优势：

如果您将在一组MyISAM表上运行许多操作，锁定您将要使用的表格会更快。锁定MyISAM表格会加快在其上插入、更新或删除的速度，因为 MySQL 在调用UNLOCK TABLES之前不会刷新被锁定表格的键缓存。通常情况下，每个 SQL 语句后都会刷新键缓存。

锁定表格的缺点是，没有会话可以更新一个READ-锁定的表格（包括持有锁的表格），也没有会话可以访问一个WRITE-锁定的表格，除了持有锁的表格。
如果您正在使用非事务性存储引擎来创建表格，如果您希望确保在SELECT和UPDATE之间没有其他会话修改表格，您必须使用LOCK TABLES。这里展示的示例需要使用LOCK TABLES来安全执行：
```
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=*some_id*;
UPDATE customer
  SET total_value=*sum_from_previous_statement*
  WHERE customer_id=*some_id*;
UNLOCK TABLES;
```
没有LOCK TABLES，可能会导致另一个会话在执行SELECT和UPDATE语句之间插入新行到trans表中。

在许多情况下，您可以通过使用相对更新（UPDATE customer SET *value*=*value*+*new_value*）或LAST_INSERT_ID()函数来避免使用LOCK TABLES。

在某些情况下，您也可以通过使用用户级别的咨询锁函数GET_LOCK()和RELEASE_LOCK()来避免锁定表格。这些锁保存在服务器的哈希表中，并且使用pthread_mutex_lock()和pthread_mutex_unlock()实现高速。请参见第 14.14 节，“锁定函数”。

有关锁定策略的更多信息，请参见第 10.11.1 节，“内部锁定方法”。

MySQL8-中文参考-六十一-

MySQL8 中文参考（六十一）

15.2.15.9 侧向派生表

15.2.15.10 子查询错误

15.2.15.11 优化子查询

15.2.15.12 子查询的限制

15.2.16 TABLE 语句

15.2.17 UPDATE 语句

15.2.18 UNION 子句

MySQL 8.0 中的 UNION 处理与 MySQL 5.7 相比

15.2.19 VALUES Statement

15.2.20 WITH（公共表达式）

其他资源

公共表达式

递归公共表达式

限制公共表达式递归

递归公共表达式示例

斐波那契数列生成

日期系列生成

分层数据遍历

与类似结构比较的通用表达式

15.3 事务和锁定语句

15.3.1 START TRANSACTION、COMMIT和ROLLBACK语句

15.3.2 无法回滚的语句

15.3.3 导致隐式提交的语句

15.3.4 保存点、回滚到保存点和释放保存点语句

15.3.5 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE 语句

15.3.6 LOCK TABLES 和 UNLOCK TABLES 语句

表锁定获取

表锁定释放

表锁定和事务的交互

表锁和触发器

表锁定的限制和条件

15.3.1 `START TRANSACTION`、`COMMIT`和`ROLLBACK`语句

15.3.6 `LOCK TABLES` 和 `UNLOCK TABLES` 语句