Let there be a table of messages with the publication date createad_at . The task is to execute such a DELETE query, as a result of which the table would have the last 5 records left.

 CREATE TABLE IF NOT EXISTS posts ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, created_at date NOT NULL, PRIMARY KEY (id) ); INSERT INTO posts VALUES (1, 'первая запись', '2012-11-01'), (2, 'вторая запись', '2012-11-02'), (3, 'третья запись', '2012-11-03'), (4, 'четвертая запись', '2012-11-04'), (5, 'пятая запись', '2012-11-05'), (6, 'шестая запись', '2012-11-06'), (7, 'седьмая запись', '2012-11-07'), (8, 'восьмая запись', '2012-11-08'), (9, 'девятая запись', '2012-11-09'), (10, 'десятая запись', '2012-11-10'), (11, 'одинадцатая запись', '2012-11-11'), (12, 'двенадцатая запись', '2012-11-12'), (13, 'тринадцатая запись', '2012-11-13'), (14, 'четырнадцатая запись', '2012-11-14'), (15, 'пятнадцатая запись', '2012-11-15'), (16, 'шестнадцатая запись', '2012-11-16'), (17, 'семнадцатая запись', '2012-11-17'), (18, 'восемнадцатая запись', '2012-11-18'), (19, 'девятнадцатая запись', '2012-11-19'), (20, 'двадцатая запись', '2012-11-20');

It would be nice to solve the problem with a single request. It is assumed that the number of records in the table is arbitrary.

    3 answers 3

    Unfortunately, limit in mysql does not allow you to substitute variables or subqueries directly into it. Also, MySQL does not allow the use of offset in the DELETE statement. But MySQL allows you to use bind variables when preparing queries.

     prepare stmt from "delete from posts order by created_at limit ?"; set @cnt=(select count(1)-5 from posts); execute stmt using @cnt;

      The first thing that comes to mind is to simply sort the records in direct order and delete 20 - 5 = 15 records

       DELETE FROM posts ORDER BY created_at LIMIT 15

      However, in fact, this is a two-query solution to the problem, since we will not always have 20 records, an additional query will be required to calculate the number of records in the table.

      You can get the created_at date fifth from the end of the record table and delete all the records before this date.

       DELETE FROM posts WHERE posts.created_at < (SELECT created_at FROM posts ORDER BY created_at DESC LIMIT 5, 1)

      However, in MySQL, the last query will not work and will return an error

      1093 - It is not allowed to specify the table 'posts' in the list of FROM tables to make changes to it

      In order to use this solution, you will have to use table self-join and execute the multi-table DELETE

       DELETE posts FROM posts JOIN (SELECT created_at FROM posts ORDER BY created_at DESC LIMIT 5, 1) AS delpst ON posts.created_at <= delpst.created_at

      There are variations in the solution, for example, you can pre-create a VIEW view that will "know" the date of the fifth record from the end of the sequence, or use the session variable in which this date has been previously saved.

      • Are you sure that dates can not be repeated for different records? Otherwise, you can leave the wrong number of records. I would merge and then delete by null id - splash58
      • @ splash58 Yes, I agree. I had the original version with id, but at the last minute I forwarded it to created_at. In principle, now MySQL allows you to save the date and time values ​​with milliseconds, you can rely on this accuracy for some time. However, if the data flow is too large, it is better to replace the created_at in the queries with id. - cheops
      • And at the same time, please note that your request in your scheme will not work :) - splash58
      • @ splash58 thanks! corrected. - cheops

      Option number 2 -

       START TRANSACTION; CREATE TEMPORARY TABLE limited ( id INT ); INSERT INTO limited SELECT id FROM posts ORDER BY created_at DESC LIMIT 5; DELETE FROM posts WHERE id NOT IN (SELECT id FROM limited); DROP TEMPORARY TABLE limited; COMMIT;
      • So returns error # 1235 - This version of MySQL does not yet support the 'LIMIT & IN / ALL / ANY / SOME subquery'. In MySQL, it will not be possible to use subqueries in DELETE with reference to the table from which records are deleted. And it seems that support will never be introduced (this message was received on the latest MySQL 5.7 and the situation has not changed for 10 years). - cheops
      • @cheops, and if the subquery name? - Mirdin
      • So it’s not possible to deceive either, apparently MySQL has some kind of cursor removal - not allowing normal use of nested queries. They did a great job before selling Sun to bring the DBMS into compliance with the SQL standard. However, the subqueries in DELETE and UPDATE could not be fixed. - cheops
      • @Ok let's go on the other side - Mirdin
      • @Mirdin slightly corrected the second option so that it was executed in MySQL. In principle, the solution is also, albeit not in a single request. You can also not to create a temporary table, act through CREATE VIEW to create a view and focus on it. - cheops