SQL-запрос: удалить все записи из таблицы, кроме последних N?

Я знаю, что воскрешаю довольно старый вопрос, но я недавно столкнулся с этой проблемой, но мне нужно было что-то, что масштабируется до больших чисел хорошо. Не было никаких существующих данных о производительности, и поскольку этот вопрос привлек довольно много внимания, я подумал, что опубликую то, что нашел.

решения, которые на самом деле работали были двойной подзапрос Алекса Барретта/NOT IN метод (похож на Билл Karwin это), и Quassnoi это!--3--> метод.

к сожалению, оба вышеуказанных метода создают очень большие промежуточные временные таблицы, и производительность быстро ухудшается по мере увеличения количества записей не удаление становится большим.

то, на чем я остановился, использует двойной подзапрос Алекса Барретта (спасибо!), но использует <= вместо NOT IN:

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  )

он использует OFFSET получить код Nth запись и удаляет это запись и все предыдущие записи.

так как заказ уже является предположением этой проблемы (ORDER BY id DESC),<= идеально подходит.

это намного быстрее, так как временная таблица, сгенерированная подзапросом, содержит только одну запись вместо N записей.

к сожалению, для всех ответов, данных другими людьми, вы не можете DELETE и SELECT из данной таблицы в одном запросе.

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

и MySQL не может поддерживать LIMIT в подзапросе. Это ограничения MySQL.

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

лучший ответ, который я могу придумать, чтобы сделать это в два этапа:

SELECT id FROM mytable ORDER BY id DESC LIMIT n; 

собрать документы и внести их в строку, разделенную запятыми:

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

(обычно интерполируя запятую-отдельный список в инструкцию SQL вводится некоторый риск внедрения SQL, но в этом случае значения не поступают из ненадежного источника, они, как известно, являются значениями идентификатора из самой базы данных.)

Примечание: хотя это не делает работу в один запрос, иногда более простое, get-it-done решение является наиболее эффективным.

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

Если Ваш идентификатор инкрементный, то используйте что-то вроде

delete from table where id < (select max(id) from table)-N

удалить все записи, кроме последней N вы можете использовать запрос сообщено позже.

это один запрос, но с большим количеством операторов, так что это на самом деле не запрос так, как это было задумано в исходном вопросе.

Также вам нужна переменная и встроенный (в запросе) подготовленный оператор из-за ошибки в MySQL.

надеюсь, что это может быть полезно в любом случае...

nnn - это строки в сохранить и theTable Это таблица, над которой вы работаете.

Я предполагаю, что у вас есть автоувеличение запись с именем id

SELECT @ROWS_TO_DELETE := COUNT(*) - nnn FROM `theTable`;
SELECT @ROWS_TO_DELETE := IF(@ROWS_TO_DELETE<0,0,@ROWS_TO_DELETE);
PREPARE STMT FROM "DELETE FROM `theTable` ORDER BY `id` ASC LIMIT ?";
EXECUTE STMT USING @ROWS_TO_DELETE;

хорошая вещь об этом подходе является производительность: Я проверил запрос на локальной БД с около 13 000 записей, сохраняя последнюю 1000. Он работает в 0,08 секунды.

скрипт из принятого ответа...

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

занимает 0,55 секунды. Около 7 в разы больше.

Я запускаю mySQL 5.5.25 на i7 MacBookPro с SSD

DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)

попробуйте ниже запрос:

DELETE FROM tablename WHERE id < (SELECT * FROM (SELECT (MAX(id)-10) FROM tablename ) AS a)

внутренний подзапрос вернет значение top 10, а внешний запрос удалит все записи, кроме top 10.

удалить из таблицы, где id не в (выберите id из таблицы ORDER by id, desc LIMIT 0, 10)

Это тоже должно работать:

DELETE FROM [table] INNER JOIN (SELECT [id] FROM (SELECT [id] FROM [table] ORDER BY [id] DESC LIMIT N) AS Temp) AS Temp2 ON [table].[id] = [Temp2].[id]

о :

SELECT * FROM table del 
         LEFT JOIN table keep
         ON del.id < keep.id
         GROUP BY del.* HAVING count(*) > N;

он возвращает строки с более чем n строк. Может быть полезно ?

использование id для этой задачи не является опцией во многих случаях. Например-таблица со статусами twitter. Вот вариант с указанным полем метки времени.

delete from table 
where access_time >= 
(
    select access_time from  
    (
        select access_time from table 
            order by access_time limit 150000,1
    ) foo    
)

просто хотел бросить это в микс для тех, кто использует Microsoft SQL Server вместо MySQL. Ключевое слово "Limit" не поддерживается MSSQL, поэтому вам нужно будет использовать альтернативу. Этот код работал в SQL 2008, и основан на этом so post. https://stackoverflow.com/a/1104447/993856

-- Keep the last 10 most recent passwords for this user.
DECLARE @UserID int; SET @UserID = 1004
DECLARE @ThresholdID int -- Position of 10th password.
SELECT  @ThresholdID = UserPasswordHistoryID FROM
        (
            SELECT ROW_NUMBER()
            OVER (ORDER BY UserPasswordHistoryID DESC) AS RowNum, UserPasswordHistoryID
            FROM UserPasswordHistory
            WHERE UserID = @UserID
        ) sub
WHERE   (RowNum = 10) -- Keep this many records.

DELETE  UserPasswordHistory
WHERE   (UserID = @UserID)
        AND (UserPasswordHistoryID < @ThresholdID)

по общему признанию, это не элегантно. Если вы можете оптимизировать это для Microsoft SQL, пожалуйста, поделитесь своим решением. Спасибо!

Если вам нужно удалить записи на основе некоторых других столбцов, то вот решение:

DELETE
FROM articles
WHERE id IN
    (SELECT id
     FROM
       (SELECT id
        FROM articles
        WHERE user_id = :userId
        ORDER BY created_at DESC LIMIT 500, 10000000) abc)
  AND user_id = :userId

почему бы и нет

DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

просто удалите все, кроме первой строки (порядок-DESC!), используя очень большое число в качестве второго предельного аргумента. посмотреть здесь

отвечая на это после долгого времени...Наткнулся на ту же ситуацию, и вместо того, чтобы использовать упомянутые ответы, я пришел с ниже -

DELETE FROM table_name order by ID limit 10

Это позволит удалить первые 10 записей и сохранить последние записи.