如何在--safe-updates模式下删除最新的一些记录 [英] how to delete the latest some records in --safe-updates mode

问题描述

例如我想删除下表中最近的三个记录

e.g. I want to delete the latest three records in below table

root@localhost:[test]10:14:02>select * from delete_test order by create_time desc;
+----+------+---------------------+
| id | code | create_time         |
+----+------+---------------------+
|  9 | fff  | 2016-06-18 10:08:28 |
|  8 | eee  | 2016-06-18 10:08:21 |
|  7 | ddd  | 2016-06-18 10:08:15 |
|  3 | ccc  | 2016-06-17 22:18:48 |
|  2 | bbb  | 2016-06-17 22:18:42 |
|  1 | aaa  | 2016-06-17 22:18:37 |
+----+------+---------------------+

我第一次尝试

root@localhost:[test]10:14:14>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'

然后我尝试了

root@localhost:[test]10:17:05>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

但是code提交的是唯一键.

然后我尝试了

root@localhost:[test]10:18:49>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

但我明确使用了primary key.

所以我想知道如何在--safe-updates模式下删除最新的一些记录?

So I want to know how to delete the latest some records in --safe-updates mode?

推荐答案

你可以做的很简单:

delete from delete_test order by create_time limit 3;

在这里工作:http://sqlfiddle.com/#!9/04f68/1

如果我没有输入正确的顺序，只需将 order by 部分更改为 order by create_time desc

If I didn't put in the right order just change the order by part to order by create_time desc

这篇关于如何在--safe-updates模式下删除最新的一些记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！