如何删除mysql中第n行之后的每条记录? [英] How to delete every record after the nth row in mysql?

问题描述

在mysql中，我可以查询select * ... LIMIT 10, 30，其中10代表要跳过的记录数.

In mysql I can query select * ... LIMIT 10, 30 where 10 represents the number of records to skip.

有人知道如何在删除前十条记录之后的每条记录的删除语句中做同样的事情吗?

Does anyone know how I can do the same thing in delete statements where every record after the first 10 records get deleted?

推荐答案

考虑到MySQL中没有rowId(像在Oracle中一样)，我建议如下:

Considering there is no rowId in MySQL (like in Oracle), I would suggest the following:

alter table mytable add id int unique auto_increment not null;

这将自动按select语句的顺序对行进行编号，而没有条件或排序.

This will automatically number your rows in the order of a select statement without conditions or order-by.

select * from mytable;

然后，在检查顺序是否符合您的需求(可能是表的转储)之后

Then, after checking the order is consistent with your needs (and maybe a dump of the table)

delete from mytable where id > 10;

最后，您可能要删除该字段

Finally, you may want to remove that field

alter table mytable drop id;

这篇关于如何删除mysql中第n行之后的每条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！