MySQL 中的坑

本文主要是介绍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

这篇关于MySQL 中的坑的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！