使用存储过程保留最新3条记录并删除其余所有记录

Got it, let's tackle this problem where you need to retain only the 3 most recent versions for each slug and delete all older records. I'll cover two approaches depending on your MySQL version, since window functions (the cleaner, more efficient method) are only available in MySQL 8.0 and above.

方法1：MySQL 8.0+ 使用窗口函数（推荐） #

窗口函数 ROW_NUMBER() makes this straightforward. We can rank each record within its slug group by version (descending), then delete any record where the rank is greater than 3.

Here's the complete stored procedure implementation:

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_old_versions`()
BEGIN
    -- Delete records ranked beyond the top 3 per slug
    DELETE t
    FROM `table_name` t
    JOIN (
        SELECT slug, version,
               -- Assign rank: 1 = newest version, 2 = second newest, etc.
               ROW_NUMBER() OVER (PARTITION BY slug ORDER BY version DESC) AS rn
        FROM `table_name`
    ) ranked ON t.slug = ranked.slug AND t.version = ranked.version
    WHERE ranked.rn > 3;
END //

先验证再删除（重要！） #

Before running the delete, always verify which records will be removed to avoid accidental data loss. Replace DELETE with SELECT to preview:

SELECT t.*
FROM `table_name` t
JOIN (
    SELECT slug, version,
           ROW_NUMBER() OVER (PARTITION BY slug ORDER BY version DESC) AS rn
    FROM `table_name`
) ranked ON t.slug = ranked.slug AND t.version = ranked.version
WHERE ranked.rn > 3;

方法2：MySQL <8.0 兼容方案 #

If you're stuck on an older MySQL version that doesn't support window functions, use a correlated subquery to count how many versions are newer than the current record. We keep records where this count is less than 3 (meaning they're in the top 3 newest).

Stored procedure for older versions:

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_old_versions`()
BEGIN
    DELETE FROM `table_name`
    WHERE (slug, version) NOT IN (
        SELECT slug, version
        FROM (
            -- Subquery to get the top 3 versions per slug
            SELECT t1.slug, t1.version
            FROM `table_name` t1
            WHERE (
                SELECT COUNT(*)
                FROM `table_name` t2
                WHERE t2.slug = t1.slug AND t2.version >= t1.version
            ) <= 3
        ) AS keep_records
    );
END //

验证删除范围 #

Again, preview the records to delete first:

SELECT *
FROM `table_name`
WHERE (slug, version) NOT IN (
    SELECT slug, version
    FROM (
        SELECT t1.slug, t1.version
        FROM `table_name` t1
        WHERE (
            SELECT COUNT(*)
            FROM `table_name` t2
            WHERE t2.slug = t1.slug AND t2.version >= t1.version
        ) <= 3
    ) AS keep_records
);

关键注意事项 #

• Backup first: Always take a backup of your table before running delete operations, just in case.
• Single slug support: If you only want to target a specific slug (like template1), add AND slug = 'template1' to the WHERE clause of the main query.
• Performance: The window function method is significantly faster for large datasets compared to the correlated subquery approach.

内容的提问来源于stack exchange，提问作者Anand Huded