SQL如何快速删除表中的冗余数据？使用CTE或临时表-SQL-PHP中文网

用CTE加ROW_NUMBER()删除重复行是最常用安全方式，需按重复字段分组并用ORDER BY指定排序依据以保留最新记录；MySQL 8.0需用DELETE JOIN语法，PostgreSQL支持DELETE USING；不支持可更新CTE时可用临时表法或DELETE自连接法。

sql如何快速删除表中的冗余数据？使用cte或临时表

用CTE加ROW_NUMBER()识别并删除重复行

直接用 DELETE 配合 CTE 是最常用也最安全的去重方式，前提是表有能定义“重复”的字段组合（比如 email 和 name），且数据库支持可更新的 CTE（PostgreSQL、SQL Server、SQLite 3.33+ 支持；MySQL 8.0+ 支持但有限制）。

关键点在于：CTE 必须包含 ROW_NUMBER() 并按重复逻辑分组排序，再在外部 DELETE 中过滤掉序号 >1 的行。

必须给 ORDER BY 显式指定排序依据，否则 ROW_NUMBER() 结果不可控，可能误删最新或最重要的记录
MySQL 8.0 中，CTE 后不能直接跟 DELETE FROM cte_name，必须写成 DELETE t1 FROM table_name t1 INNER JOIN (cte) t2 ON ... 形式
PostgreSQL 允许 DELETE FROM table USING cte WHERE table.id = cte.id AND cte.rn > 1，更直观

WITH dup AS (
  SELECT id, 
         ROW_NUMBER() OVER (
           PARTITION BY email, name 
           ORDER BY created_at DESC
         ) AS rn
  FROM users
)
DELETE FROM users 
WHERE id IN (SELECT id FROM dup WHERE rn > 1);

临时表法更适合大表或兼容性要求高的场景

当目标数据库不支持可更新 CTE（如旧版 MySQL 或某些嵌入式 SQLite），或原表数据量极大、担心 CTE 执行期间锁表太久时，用临时表是更稳妥的选择。本质是“提取唯一集 → 清空原表 → 回填”三步走。

注意：这个方法会重置自增主键（如果存在），且中间阶段原表为空，业务需停写或加锁。

临时表建议用 CREATE TEMPORARY TABLE（MySQL/PostgreSQL）或 CREATE TABLE #temp（SQL Server），避免污染正式 schema
INSERT INTO ... SELECT DISTINCT 不够用——它只去重整行，无法保留“每组中最新的一条”，得配合子查询或窗口函数先选好代表行
回填前务必确认临时表数据量与预期一致，否则清空后无法恢复

-- 示例：保留每组 email 最新一条
CREATE TEMPORARY TABLE users_dedup AS
SELECT * FROM users u1
WHERE u1.created_at = (
  SELECT MAX(u2.created_at)
  FROM users u2
  WHERE u2.email = u1.email
);
<p>TRUNCATE TABLE users;
INSERT INTO users SELECT * FROM users_dedup;</p>

DELETE + self-JOIN 在 MySQL 5.7 等老版本里最实用

如果你卡在 MySQL 5.7 或 MariaDB 10.2 这类不支持 CTE 的环境，DELETE t1 FROM table t1 JOIN table t2 是事实标准解法。它利用自连接比对字段，标记并删除“非首条”记录。

性能上，该语句容易触发全表扫描，务必确保用于判断重复的字段（如 email）上有索引，否则删除几万行可能卡住数分钟。

不能写成 DELETE FROM table WHERE id IN (SELECT ...)，MySQL 会报错 “You can’t specify target table for update in FROM clause”
t1.id > t2.id 是常见排序代理，但它假设 id 越大越新；若业务中 id 无时间含义，必须换用带时间字段的条件
一次删太多行可能撑爆 binlog 或触发锁升级，建议加 LIMIT 10000 分批执行