問題描述

需求

刪除最近添加的幾條記錄

select * from delete_test order by create_time desc;+----+------+---------------------+| id | code | create_time |+----+------+---------------------+| 6 | fff | 2016-06-17 22:19:04 || 5 | eee | 2016-06-17 22:18:59 || 4 | ddd | 2016-06-17 22:18:53 || 3 | ccc | 2016-06-17 22:18:48 || 2 | bbb | 2016-06-17 22:18:42 || 1 | aaa | 2016-06-17 22:18:37 |+----+------+---------------------+#嘗試一 失敗delete from delete_test where code in (select code from delete_test order by create_time desc limit 3);ERROR 1235 (42000): This version of MySQL doesn’t yet support ’LIMIT & IN/ALL/ANY/SOME subquery’#嘗試二 失敗delete from delete_test a where exists (select code from delete_test b where a.code = b.code order by b.create_time desc limit 3);ERROR 1064 (42000): 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 exists (select code from delete_test b where a.code = b.code order by b’ at line 1#嘗試三 失敗delete from delete_test a, (select code from delete_test order by create_time desc limit 3) b where a.code = b.code;ERROR 1064 (42000): 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, (select code from delete_test order by create_time desc limit 3) b where a.co’ at line 1#嘗試四 失敗 但明明code字段是唯一索引?。║NIQUE KEY `code` (`code`)）delete a from delete_test a, (select code from delete_test order by create_time desc limit 3) b where a.code = b.code;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column#嘗試五 失敗 改用join 顯式使用id主鍵 delete a from delete_test a join (select code from delete_test order by create_time desc limit 3) b on a.code = b.code where a.id>0;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column#然后重新連接會話 沒有使用--safe-updatesmysql -uroot -p123456#刪除成功delete a from delete_test a, (select code from delete_test order by create_time desc limit 3) b where a.code = b.code and a.id>0;Query OK, 3 rows affected (0.00 sec)# 驗證已成功刪除select * from delete_test order by create_time desc;+----+------+---------------------+| id | code | create_time |+----+------+---------------------+| 3 | ccc | 2016-06-17 22:18:48 || 2 | bbb | 2016-06-17 22:18:42 || 1 | aaa | 2016-06-17 22:18:37 |+----+------+---------------------+

參考文檔http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql

問題解答

真的沒有必要這么復雜 直接

delete from delete_test order by create_time desc limit 3;

即可。

分兩步刪可以嗎，查主鍵，通過主鍵刪除

delete from delete_test where code in (select code from delete_test order by create_time desc) limit 3;