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

「爱情、让人受尽委屈。」 2022-06-09 01:49 177阅读 0赞

错误信息

> Error: `1093` SQLSTATE: `HY000` ([`ER_UPDATE_TABLE_USED`][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_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_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_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][ER_UPDATE_TABLE_USED]

[ER_UPDATE_TABLE_USED]: https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_update_table_used
[https_dev.mysql.com_doc_refman_5.7_en_upgrading-from-previous-series.html]: 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/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/server-system-variables.html#sysvar_optimizer_switch