How Can I Delete All But the N Latest Records in MySQL?

How to delete all records except the latest N records in SQL

This is a common problem: your table is full of records, but you only want to keep the latest N records and delete the rest. While the query you asked demonstrates the intent, it fails to achieve its purpose due to limitations of MySQL.

Don’t worry, solutions exist! The following query will achieve your desired results:

<code class="language-sql">DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT N -- 保留最新的N条记录
  ) foo
);</code>

Query decomposition:

1. The target of the DELETE statement is the 'table' table, which is used to delete records.
2. The WHERE clause excludes records to be deleted by checking if the id does not exist in the subquery.
3. The subquery selects the latest N records by sorting the records in descending order (id DESC) and limiting the results.
4. The intermediate subquery (foo) is necessary to alleviate MySQL limitations.

Restrictions bypassed:

• Intermediate subqueries prevent "errors in the FROM clause updating the target table" by isolating the subquery from the DELETE operation.
• It also avoids "LIMIT & IN/ALL/ANY/SOME subquery" errors by using a subquery of subqueries.

Optimization instructions:

Depending on your use case, you may find the following optimized query more suitable:

<code class="language-sql">DELETE FROM `table`
ORDER BY id DESC
LIMIT ROWS COUNT() - N -- 只保留最新的N条记录</code>

If it meets your requirements, consider this optimization as it can significantly improve performance.

The above is the detailed content of How Can I Delete All But the N Latest Records in MySQL?. For more information, please follow other related articles on the PHP Chinese website!