如何通过SQL在elos表中为每个用户保留最近30条记录并删除冗余数据?
表
elos按用户保留最近30条记录的实现方案 核心实现思路
整个操作的判断逻辑可以拆成两个明确的规则,从根源上避免误删数据:
- 排序规则:对每个用户的所有记录,按照
created_at倒序排列,如果存在相同创建时间的记录,将自增主键id作为次排序键,保证排序结果稳定可复现 - 保留规则:每个用户分组下,排序后位次在前30的记录予以保留,其余位次>30的记录全部纳入删除范围
分场景实现方式
支持窗口函数的现代数据库(MySQL 8.0+/PostgreSQL/SQL Server)
这类数据库可以直接用ROW_NUMBER()窗口函数快速计算每个用户的记录位次,整个流程分两步走,最大程度降低操作风险:
- 先执行查询语句,核对待删除的记录是否符合预期,避免直接执行删除导致数据丢失
SELECT e.id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY username ORDER BY created_at DESC, id DESC ) AS row_rank FROM elos ) ranked_record WHERE ranked_record.row_rank > 30; - 确认查询结果无误后,执行删除操作
DELETE FROM elos WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY username ORDER BY created_at DESC, id DESC ) AS row_rank FROM elos ) ranked_record WHERE ranked_record.row_rank > 30 );
低版本数据库(MySQL 5.x及更早,不支持窗口函数)
可以通过自定义会话变量模拟分组行号的计算逻辑,先对全表按用户、创建时间排序,逐行累计每个用户的记录数:
-- 同样先验证待删除的ID列表 SELECT e.id FROM ( SELECT id, @current_rank := IF(@same_user = username, @current_rank + 1, 1) AS row_rank, @same_user := username FROM elos, (SELECT @current_rank := 0, @same_user := '') AS init_var ORDER BY username, created_at DESC, id DESC ) e WHERE e.row_rank > 30;
核对完待删除ID列表后,直接基于查询到的ID集合执行批量删除即可。
实操注意事项
- 所有删除操作执行前,必须对
elos表做完整备份,避免逻辑疏漏导致不可恢复的数据损失 - 如果表存储的数据量较大,不要一次性执行全量删除,建议按ID范围分批删除,每次处理1000-2000条记录,避免长事务持锁影响线上正常业务读写
- 排序规则中必须带上
id作为兜底条件,防止相同created_at的记录出现随机排序,导致最终每个用户留存的记录数偏离预期
内容的提问来源于stack exchange,提问作者M A
相关产品推荐
相关产品推荐

