MySQL实践操作技巧

同表，根据查找出来的主键id，进行删除，会报错。

比如：

DELETE FROM t_user WHERE id IN(
    SELECT id FROM t_user WHERE name = "Hello"
)

Error Code: 1093. You can't specify target table 't_user' for update in FROM clause

解决方案：

把查出来的结果生成一个临时表。

DELETE FROM t_user WHERE id IN(
    SELECT tmp.id FROM
        (SELECT id FROM t_user WHERE name = "Hello") tmp
)

但这时有可能还会报个错，提示这是不安全的操作方法。

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preference -> SQL Editor and reconnect.

这时可以通过以下语句关掉安全阀。

SET SQL_SAFE_UPDATES = 0;

再次执行删除命令就可以成功执行了。