How to remove from the table if the counter is more than 20

I have a mysql table that should only contain 20 new records before adding additional records. New lines are added daily, so I would like to first delete any entries that are more than 20 allowed, starting from the earliest.

The table contains an id column with auto-increment, so I can easily determine which are the earliest entries.

Thanks for any help.

+3
source share
5 answers

You can specify an offset with a keyword LIMITin your query to save the very last 20 lines. However, according to the MySQL documentation, there is no easy way to limit the offset to the last; instead, they offer the following:

To get all rows from a specific offset to the end of the result set, you can use some large amount for the second parameter.

So this SQL should do the trick:

DELETE FROM table ORDER BY id DESC LIMIT 20, 18446744073709551615;
+5
source

With mysql 5, you cannot use the offset in the delete statement. Further reading here: http://dev.mysql.com/doc/refman/5.0/en/delete.html

I was able to use the following technique to save many new entries. mySQL subquery limit

+2

DELETE ORDER BY LIMIT:

DELETE FROM MyTable
ORDER BY MyTable.id DESCENDING
LIMIT 20,18446744073709551615;

20 id .

0
0

?
, . , , , , memcache

-3

Source: https://habr.com/ru/post/1747124/


All Articles