从表中删除行，但指定的行数除外(行数限制) [英] Delete rows from table, except specified number (limit number of rows)

问题描述

如何从MYSQL表中删除所有行(200万行)，例如最新的100.

How to delete all rows from the MYSQL table (2 million of rows), keeping eg. latest 100.

选择我要保留的内容的查询将是:

The query to select what I want to keep would be:

 SELECT id FROM mytable ORDER by date DESC LIMIT 100;

因此，我需要一个查询，该查询将删除除此查询中选定的所有行.使用where in监视ID列表似乎是一个过大的杀伤力.

So I need a query which deletes all rows except selected in this query. Spedyfying a list of IDs using where in seems like an overkill.

推荐答案

DELETE FROM mytable WHERE date < (SELECT date FROM mytable ORDER BY date DESC LIMIT 100,1)

尝试

它应删除所有记录，但根据date列的最后100条记录

It should delete all records but the last 100 entries based on the date column

错字

这篇关于从表中删除行，但指定的行数除外(行数限制)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！