In general, you cannot modify a table and select from the same table in a subquery. 通常，不能修改表并从子查询中的同一表中进行选择。For example, this limitation applies to statements of the following forms:例如，此限制适用于以下形式的声明：

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if for the modified table you are using a derived table and that derived table is materialized rather than merged into the outer query. 例外：如果对修改后的表使用派生表，并且该派生表是具体化的，而不是合并到外部查询中，则上述禁止不适用。(See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.) （请参阅第8.2.2.4节，“通过合并或物化优化派生表、视图引用和公共表表达式”。）Example:例如：

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);

Here the result from the derived table is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.在这里，来自派生表的结果被具体化为一个临时表，因此t中的相关行在t的更新发生时已经被选中。

In general, you may be able to influence the optimizer to materialize a derived table by adding a NO_MERGE optimizer hint. 通常，您可以通过添加NO_MERGE优化器提示来影响优化器具体化派生表。See Section 8.9.3, “Optimizer Hints”.请参阅第8.9.3节，“优化器提示”。

Row comparison operations are only partially supported:仅部分支持行比较操作：

In other words, for a subquery that returns rows of n-tuples, this is supported:换句话说，对于返回n元组行的子查询，支持以下操作：

(expr_1, ..., expr_n) [NOT] IN table_subquery

But this is not supported:但这是不支持的：

(expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery

The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of = comparisons and AND operations. 支持IN行比较而不支持其他行比较的原因是IN是通过将其重写为一系列=比较和AND操作来实现的。This approach cannot be used for ALL, ANY, or SOME.这种方法不能用于ALL、ANY或SOME情况。

Prior to MySQL 8.0.14, subqueries in the FROM clause cannot be correlated subqueries. 在MySQL8.0.14之前，FROM子句中的子查询不能是相关子查询。They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query. 在查询执行过程中，它们被整体物化（求值以生成结果集），所以不能对外部查询的每行求值。The optimizer delays materialization until the result is needed, which may permit materialization to be avoided. 优化器延迟物化直到需要结果，这可以避免物化。See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.请参阅第8.2.2.4节，“通过合并或物化优化派生表、视图引用和公共表表达式”。

MySQL does not support LIMIT in subqueries for certain subquery operators: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'

See Section 13.2.11.10, “Subquery Errors”.请参阅第13.2.11.10节“子查询错误”。

MySQL permits a subquery to refer to a stored function that has data-modifying side effects such as inserting rows into a table. MySQL允许子查询引用具有数据修改副作用的存储函数，例如在表中插入行。For example, if f() inserts rows, the following query can modify data:例如，如果f()插入行，则以下查询可以修改数据：

SELECT ... WHERE x IN (SELECT f() ...);

This behavior is an extension to the SQL standard. 此行为是SQL标准的扩展。In MySQL, it can produce nondeterministic results because f() might be executed a different number of times for different executions of a given query depending on how the optimizer chooses to handle it.在MySQL中，它可以产生不确定的结果，因为f()对于给定查询的不同执行可能执行不同的次数，这取决于优化器选择如何处理它。

For statement-based or mixed-format replication, one implication of this indeterminism is that such a query can produce different results on the source and its replicas.对于基于语句或混合格式的复制，这种不确定性的一个含义是，这样的查询可以在源及其副本上产生不同的结果。