如何不使用中间表保留数据库前5000行匹配记录并删除其余行

可以不使用中间表、不需要提前统计总行数实现需求，单条SQL即可完成。你最初写的动态LIMIT写法由于数据库语法限制无法生效，以下是不同数据库的可行方案：

MySQL要求同表删除+子查询时需要额外嵌套一层临时表规避更新冲突，写法如下：

DELETE FROM your_table
WHERE 
  -- 保留你原有的筛选条件，比如日期限制
  $todays_date 
  AND id NOT IN (
    -- 嵌套一层临时表避免同表查询修改冲突
    SELECT id FROM (
      -- 查询出需要保留的5000行ID，ORDER BY规则可自定义，比如按创建时间排序决定保留哪部分数据
      SELECT id FROM your_table 
      WHERE $todays_date 
      ORDER BY create_time ASC 
      LIMIT 5000
    ) AS tmp
);

如果你用的是MySQL 8.0及以上版本，也可以用窗口函数实现，逻辑更清晰：

DELETE t FROM your_table t
JOIN (
  SELECT id, ROW_NUMBER() OVER (ORDER BY create_time ASC) AS rn
  FROM your_table
  WHERE $todays_date
) AS ranked ON t.id = ranked.id
WHERE ranked.rn > 5000;

PostgreSQL支持CTE直接配合删除操作，写法更简洁：

WITH ranked_rows AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY create_time ASC) AS rn
  FROM your_table
  WHERE $todays_date
)
DELETE FROM your_table
WHERE id IN (SELECT id FROM ranked_rows WHERE rn > 5000);

如果你的数据库版本不支持上述语法，也可以用标记字段的方案，不需要提前统计行数：

• 新增临时标记字段is_keep（类型为TINYINT，默认值为0）
• 执行UPDATE语句直接标记要保留的5000行：

  UPDATE your_table SET is_keep = 1 
  WHERE $todays_date 
  ORDER BY create_time ASC 
  LIMIT 5000;
  

• 执行删除语句清理未标记的多余行：

  DELETE FROM your_table WHERE $todays_date AND is_keep = 0;
  

• 筛选条件用到的字段（比如日期字段）、排序用到的字段（比如创建时间、ID）建议加索引，避免大表下执行卡顿
• ORDER BY规则需要明确，保证每次保留的5000行符合你的业务预期，避免误删需要保留的数据

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