MariaDB数据清理：保留每个history_id至少2条，删除超1年旧数据

要实现每个history_id至少保留2条记录，仅删除其中超过1年的多余旧数据，可以利用MariaDB 10.6支持的窗口函数ROW_NUMBER()精准定位待删记录，具体操作如下：

1. 先验证待删除记录（避免误操作） #

先执行查询语句确认哪些记录会被删除：

SELECT id, history_id, date
FROM (
    SELECT 
        id,
        history_id,
        date,
        ROW_NUMBER() OVER (PARTITION BY history_id ORDER BY date DESC) AS rn
    FROM post_history
    WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))
) AS ranked_records
WHERE rn > 2;

• 内层子查询：先筛选出所有超过1年的记录，再按history_id分组，以date降序（最新记录排最前）为每条记录分配排名rn
• 外层筛选：仅保留排名大于2的记录——也就是每个history_id超过1年的记录中，除最新2条之外的旧数据

2. 执行删除操作 #

确认筛选结果符合预期后，执行删除语句：

DELETE FROM post_history
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY history_id ORDER BY date DESC) AS rn
        FROM post_history
        WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))
    ) AS ranked_records
    WHERE rn > 2
);

• 嵌套两层子查询是为了避开MariaDB中DELETE无法直接引用含窗口函数子查询的限制

效率优化建议 #

为提升查询和删除的速度，建议在history_id和date字段上创建联合索引：

CREATE INDEX idx_post_history_history_date ON post_history(history_id, date DESC);

逻辑说明 #

• 所有1年内的记录会被完整保留，不会被删除
• 每个history_id的超1年记录仅保留最新2条，其余旧数据被清理
• 若某个history_id的所有记录都超过1年，会自动保留最新2条，满足“至少保留2条”的要求

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