偶尔记一下

没事看看 - MyBatis工具

MySql 5.7 UPDATE 和 DELETE 导致的 error code [1093]

错误信息

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

阅读更多

扫码向博主提问

去开通我的Chat快问

isea533

博客专家

MyBatis相关答疑
  • 擅长领域:
  • MyBatis
  • Spring Boo
  • Spring
版权声明:版权归博主所有,转载请带上本文链接!联系方式:abel533@gmail.com https://blog.csdn.net/isea533/article/details/77531148
文章标签: mysql
个人分类: 解决问题
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭