错误信息
Error: 1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)
Message: You can’t specify target table ‘%s’ for update in FROM clause
在 MySql 中,由于安全原因,类似下面的 SQL 是无法执行的:
delete from table1 a where exists ( select 1 from table1 b where a.id = b.oid)
这种 SQL 会报错:
[Err] 1093 - You can’t specify target table ‘table1’ for update in FROM clause
通常会采用类似下面的方式来避免这种错误:
delete from table1 a where exists ( select 1 from (select * from table1) b where a.id = b.oid)
增加一层嵌套后,mysql 会当成临时表处理,这样就能避免上面的错误。
但是 5.7 版本的时候,其中一项优化会导致上面这种方式出错。
在 5.7.19 (或接近的版本)执行下面的 SQL:
EXPLAIN select * from (select * from mysql.user) t1
分析的结果如下:
1 SIMPLE user ALL 3 100
上面的 SQL 优化后就会变成下面这样:
select * from mysql.user
这就导致在上面 delete 中增加的一层嵌套被优化掉了,因此出现了 1093 的错误。
解决的办法就是在 UPDATE 和 DELETE 时,先执行下面的配置:
SET optimizer_switch = 'derived_merge=off';
这样就不会被优化合并了,使用这个配置后,解释上面的SQL结果如下:
1 PRIMARY ALL 3 100
2 DERIVED user ALL 3 100
如果想要针对全局配置可以使用下面的命令:
SET GLOBAL optimizer_switch = 'derived_merge=off';
如果想要数据库启动生效,还需要在my.ini(或my.conf)中的[mysqld]下面添加配置:
optimizer_switch = ‘derived_merge=off’
参考资料
derived_merge 说明:
https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
优化:
https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
参数信息:
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_switch
错误信息:
https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_update_table_used