MySQL 中的坑

鬼畜一、delete语句表名不能加别名

DELETE FROM t_test a WHERE a.id = 1; 
-------------------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：delete from t_test a where a.id = 1

错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a where a.id = 1' at line 1

鬼畜二、You can't specify target table 't_test' for update in FROM clause

#去重掉最大ID的重复数据
#正确示范
DELETE FROM  
t_test 
WHERE id IN 
(SELECT * FROM
(SELECT MAX(id) did FROM t_test b GROUP BY username HAVING COUNT(*) > 1) t);
-------------------------------------------------------------------------
#错误示范，oracle正确
DELETE FROM  
t_test 
WHERE id IN 
(SELECT MAX(id) did FROM t_test b GROUP BY username HAVING COUNT(*) > 1);
-------------------------------------------------------------------------
1 queries executed, 0 success, 1 errors, 0 warnings

查询：DELETE FROM t_test WHERE id IN (SELECT MAX(id) did FROM t_test b GROUP BY username HAVING COUNT(*) > 1)

错误代码： 1093
You can't specify target table 't_test' for update in FROM clause