Mysql 5.5 到 5.7 - 您不能在 FROM 子句中指定要更新的目标表 - 同一个表

Question

我为 MySQL 5.5 编写的一些查询不再适用于 MySQL 5.7。我不确定是否有配置设置...

以下查询不再有效:

Update Tablex set Column1 = 3 where Tablex.ID = 20 and not exists (
    Select * From ( Select * from Tablex a where a.StartTime > :Start and a.EndTime <:end ) as DerivedTable1
)

//不能在FROM子句中指定要更新的目标表Tablex

这在 MySQL 5.5 中工作得很好，基本上这个想法是 MySQL 会创建一个临时表......但现在我想不会。有人知道如何为 MySQL 5.7 更新这个吗？或者我可以使用事务并预先查询存在的条件，然后根据结果继续。

Answer 1

这确实是 MySQL 5.7 中的一个变化，描述了 in the docs :

The optimizer now handles derived tables and views in the FROM clause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans. However, for statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that previously was materialized can result in an ER_UPDATE_TABLE_USED error.

The error occurs when merging a derived table into the outer query block results in a statement that both selects from and modifies a table. (Materialization does not cause the problem because, in effect, it converts the derived table to a separate table.) To avoid this error, disable the derived_merge flag of theoptimizer_switch system variable before executing the statement:

mysql> SET optimizer_switch = 'derived_merge=off';

this thread 中提到了另一种可能的解决方法:

• Force the derived table to be materialized, e.g by adding DISTINCT after SELECT, or adding a LIMIT.

Rails 开发人员已选择此方法来修复 the similar bug .