Mysql5.7 You can't specify target table 'table' for update in FROM clause

朱雀 2022-03-15 12:40 189阅读 0赞

MySql中不允许在Update/Delete中使用子查询引用操作目标表，怕引起一致性问题，如：

mysql> select id,name from t_sys_dept;
    +----+--------------+
    | id | name         |
    +----+--------------+
    |  1 | 总公司       |
    | 12 | 省公司       |
    | 13 | 一级渠道     |
    | 14 | 二级渠道     |
    +----+--------------+
    4 rows in set (0.00 sec)
    
    mysql> delete from t_sys_dept where id=(select id from t_sys_dept where id=1);
    ERROR 1093 (HY000): You can't specify target table 't_sys_dept' for update in FROM clause

直接报错`ERROR 1093 (HY000): You can't specify target table 't_sys_dept' for update in FROM clause`

官方给出了一种间接的方式，使用临时表，如下：

mysql> delete from t_sys_dept where id=(select id from (select id from t_sys_dept where id=1) a);
    ERROR 1093 (HY000): You can't specify target table 't_sys_dept' for update in FROM clause

可以看到还是报错，这是因为MySql5.7以上，会进行sql优化，优化后的sql又触发了这个错误，有两种解决方式，一种是禁用优化，如：

mysql> SET optimizer_switch = 'derived_merge=off';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from t_sys_dept where id=(select id from (select id from t_sys_dept where id=14) a);
    Query OK, 1 row affected (0.04 sec)

但这种方法有个问题是一条Sql变成了两条，在Mybatis中可使用添加`allowMultiQueries=true`的方式打开多条sql执行，但影响条数返回是有问题的，无法正常反映更新结果。

好在还有另外一种方式，在[这里][Link 1]有提到，可以在子查询中使用DISTINCT或者添加LIMIT

mysql> SET optimizer_switch = 'derived_merge=on';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from t_sys_dept where id=(select id from (select id from t_sys_dept where id=13) a); 
    ERROR 1093 (HY000): You can't specify target table 't_sys_dept' for update in FROM clause
    
    mysql> delete from t_sys_dept where id=(select id from (select distinct id from t_sys_dept where id=13) a);
    Query OK, 1 row affected (0.03 sec)

但使用临时表总是会影响性能，是否有必要用就需要做个评估了。

[Link 1]: https://bugs.mysql.com/bug.php?id=76259