如何按列值删除160万行大表中的重复数据？

嘿，针对你这张160万行的大表去重需求，我整理了几种高效的方案，分不同常用数据库来说明——先默认你要去重的判断逻辑是text、image_id、order这三列值完全一致的行（毕竟id是唯一标识，重复行肯定是这几个业务列撞了）：

因为是百万级大表，优先选性能友好的方法，避免全表扫描或者临时表过载：

方法1：临时表+唯一索引（高效首选） #

这个方法利用临时表的唯一索引自动过滤重复行，速度很快：

1. 先创建和原表结构一致的临时表，同时给需要判重的列加联合唯一索引：

CREATE TEMPORARY TABLE temp_table LIKE your_table_name;
ALTER TABLE temp_table ADD UNIQUE INDEX idx_unique (text, image_id, `order`);

注意：order是MySQL的关键字，所以要用反引号包裹起来。

2. 把原表数据插入临时表，用IGNORE关键字跳过违反唯一索引的重复行：

INSERT IGNORE INTO temp_table SELECT * FROM your_table_name;

这里重复的行会自动被忽略，每组重复行只会保留最先插入的那一行（也就是id最小的）。

3. 替换原表（记得先备份！）：

RENAME TABLE your_table_name TO old_table_name, temp_table TO your_table_name;

确认数据没问题后，就可以删掉旧表：DROP TABLE old_table_name;

方法2：窗口函数删除（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，用窗口函数逻辑更清晰，能精准控制保留哪一行：

DELETE t1 FROM your_table_name t1
JOIN (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY text, image_id, `order` ORDER BY id) AS rn
    FROM your_table_name
) t2 ON t1.id = t2.id
WHERE t2.rn > 1;

这段SQL会按text、image_id、order分组，每组保留id最小的行，删掉其他重复行。要是想保留最新的（id最大的），把ORDER BY id改成ORDER BY id DESC就行。

PostgreSQL有专属的高效去重语法，同样适合大表：

方法1：DISTINCT ON创建新表 #

DISTINCT ON是PostgreSQL的特色语法，能快速拿到每组的第一行：

CREATE TABLE new_table AS
SELECT DISTINCT ON (text, image_id, "order") *
FROM your_table_name
ORDER BY text, image_id, "order", id; -- 最后一个ORDER BY字段决定保留哪一行，这里是保留id最小的

之后替换原表，别忘了给新表补上原表的主键和索引（保证后续性能）：

ALTER TABLE your_table_name RENAME TO old_table_name;
ALTER TABLE new_table RENAME TO your_table_name;
ALTER TABLE your_table_name ADD PRIMARY KEY (id);
CREATE INDEX idx_image_order ON your_table_name (image_id, "order");

方法2：窗口函数删除 #

和MySQL逻辑类似，用窗口函数标记重复行后删除：

DELETE FROM your_table_name
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY text, image_id, "order" ORDER BY id) AS rn
        FROM your_table_name
    ) sub
    WHERE rn > 1
);

同样，调整ORDER BY id的顺序就能选择保留哪一行。

• 重中之重：操作前一定要备份原表！ 百万级数据出错很难恢复，备份后再动手才稳妥。
• 如果表有外键关联，要先处理外键约束（比如临时禁用外键检查），否则操作可能报错。
• 大表操作尽量选业务低峰期，避免影响线上服务。
• 如果你的去重逻辑不是text+image_id+order，只需要把上述SQL里的分区列改成你实际需要的列就行。

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