『You can't specify target table 't1' for update in FROM clause』 bug 解决

前几天升级了Mysql 5.7,昨天在测试一个很久没改动的系统时一条Sql报了这个bug『You can’t specify target table ‘t1’ for update in FROM clause』。
用排除法分析了一下,发现只有在Mysql 5.7上才能重现,遂上网查找了一下Mysql 5.7的Release note,发现确实是Mysql在这方面进行了更改。原文如下:
As of MySQL 5.7.6, the optimizer handles propagation of an ORDER BY clause in a derived table or view reference to the outer query block by propagating the ORDER BY clause if the following conditions apply: The outer query is not grouped or aggregated; does not specify DISTINCT, HAVING, or ORDER BY; and has this derived table or view reference as the only source in the FROM clause. Otherwise, the optimizer ignores the ORDER BY clause. Before MySQL 5.7.6, the optimizer always propagated ORDER BY, even if it was irrelevant or resulted in an invalid query.

For statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that prior to MySQL 5.7.6 was materialized can result in an ER_UPDATE_TABLE_USED error:

1 2 3 4 5 6 7

mysql> DELETE FROM t1 -> WHERE id IN (SELECT id -> FROM (SELECT t1.id -> FROM t1 INNER JOIN t2 USING (id) -> WHERE t2.status = 0) AS t); ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

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 the optimizer_switch system variable before executing the statement:

1	mysql> SET optimizer_switch = 'derived_merge=off';

Mysql 在5.7版本之后,默认的derived_merge是on,导致在更新和删除表的时候Where条件中不能出现要更新或者删除的表。解决这个问题有三种方式:
1.在my.cnf里配置

1

optimizer_switch = 'derived_merge=off'

2.修改sql语句为:

1
2
3
4
5
6

SET optimizer_switch = 'derived_merge=off';
DELETE FROM t1
WHERE id IN (SELECT id
              FROM (SELECT t1.id
                    FROM t1 INNER JOIN t2 USING (id)
                    WHERE t2.status = 0) AS t);

3.修改程序,把ID先查出来,我比较喜欢这种方式。