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执行,但影响条数返回是有问题的,无法正常反映更新结果。
好在还有另外一种方式,在这里有提到,可以在子查询中使用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)
但使用临时表总是会影响性能,是否有必要用就需要做个评估了。