mysql - 为啥sql的疑问包了一层就成功了？

Question

我有个sql的疑问，如下：

delete  FROM sys_post WHERE post_id  NOT IN (
    SELECT MIN(post_id) AS min_id FROM sys_post GROUP BY post_name 
);

上边这个语句会报错；（SQL 错误 [1093] [HY000]: You can't specify target table 'sys_post' for update in FROM clause）；

delete FROM sys_post WHERE post_id  NOT IN (
  **SELECT t.min_id FROM (**
    SELECT MIN(post_id) AS min_id FROM sys_post GROUP BY post_name 
  **) t**
);

这个就好着呢；

疑问：第二个语句只不过加了一层包裹，为啥就执行成功了？？？谢谢

Answer 1

MySql 设计如此，INSERT/UPDATE/DELETE 之类的更新语句要更新的表，和其子查询 FROM 的表，不能是同一张表。

但只有 MySql 有这个问题，其他主流数据库（比如 Oracle、SQL-Server）都没有这种约束。

官方对此的解释只有这一句话：

REF: https://dev.mysql.com/doc/refman/8.0/en/update.html

You cannot update a table and select directly from the same table in a subquery.

紧接着这句话官方给出的解决方案就是：

You can work around this by using a multi-table update in which one of the tables is derived from the table that you actually wish to update, and referring to the derived table using an alias.

也就是你这里的用个中间表起个别名包裹一下就好了。

至于为啥有这种蛋疼的约束，天知道。