[转帖]MySql 5.7 UPDATE 和 DELETE 导致的 error code [1093]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1382 | 回复: 0   主题: [转帖]MySql 5.7 UPDATE 和 DELETE 导致的 error code [1093]        下一篇 
ad222888
注册用户
等级:新兵
经验:66
发帖:134
精华:0
注册:2016-9-25
状态:离线
发送短消息息给ad222888 加好友    发送短消息息给ad222888 发消息
发表于: IP:您无权察看 2018-8-16 14:51:23 | [全部帖] [楼主帖] 楼主

错误信息

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




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论