delete - mysql删除最近几条记录怎么这么难

问题描述

需求

删除最近添加的几条记录

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字段是唯一索引啊（UNIQUE 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;