有效地删除Mysql表中除前n行之外的所有行

I have a large, high traffic table with about 10000 entries and i need a command to clear all but the top (highest ids) n entries.

我有一个大的,高流量的表,有大约10000个条目,我需要一个命令来清除除顶部(最高ID)n条目之外的所有条目。

I wanted something like this, but it caused a syntax error:

我想要这样的东西,但它导致语法错误:

ALTER TABLE table 
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN (MAX(id)-n),
PARTITION p1 VALUES LESS THAN (MAXVALUE));
ALTER TABLE table DROP PARTITION p0;

The only problem is i need to clear all but the top values a lot and stacking partitions doesn't work. Also, a scheduler doesn't fit in well with what im doing. How can i efficiently remove all but the n highest id rows in a Mysql table?

唯一的问题是我需要清除除了顶部值以外的所有值,堆叠分区不起作用。此外,调度程序不适合我正在做的事情。如何有效地删除Mysql表中除了n个最高id行之外的所有行?

3 个解决方案

#1

You could use something like this:

你可以使用这样的东西:

DELETE
FROM ids
WHERE id NOT IN (select * from (select id from ids order by id desc limit 2) s)

Please see fiddle here.

请看这里的小提琴。

Or also this:

或者这个:

DELETE ids.*
FROM
  ids LEFT JOIN (select id from ids order by id desc limit 2) s
  ON ids.id = s.id
WHERE s.id is null

Fiddle here.

#2

If you want to do it efficiently, then copy the rows into another table, truncate the original table, and insert the rows back in.

如果要有效地执行此操作,请将行复制到另一个表中,截断原始表,然后将行重新插入。

create temporary table tosave as
    (select *
     from mytable
     order by id desc
     limit n
    );

truncate table mytable;

insert into mytable
    select * from tosave

The truncate is more efficient because it does not log the deletes (as described here).

truncate更有效,因为它不记录删除(如此处所述)。

#3

I would go with this:

我会这样做:

delete from `table`
where `key` not in (select `key` from `table` order by <...> limit n,100000)

#1