错误信息
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
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2018-8-16 14:51:23 |
赞(
操作
