无ID列的MySQL表删除重复行失败，求正确解决方案

你的SQL执行后把整个重复组都删除的原因是：JOIN条件只匹配了分区字段，导致原表中同一组的每一行都会与子查询dups中该组的所有行关联。

举个例子：假设某组有2条重复数据，子查询dups会给这两行分别标记row_num=1和row_num=2。原表的第一行（对应row_num=1）会同时匹配dups中row_num=1和row_num=2的行，当WHERE dups.row_num !=1时，这个条件会被满足（因为存在匹配的row_num=2的行），所以原表的这一行也会被删除，最终整个组的所有数据都被清空。

方法一：无需新增ID列（MySQL 8.0+ 支持） #

利用窗口函数标记每行的序号，通过关联所有字段确保只删除重复组中序号大于1的行：

DELETE t1
FROM fake_stock_option_quotes t1
JOIN (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY symbol, expiration_date, is_call, strike_price, date
            ORDER BY (SELECT NULL) -- 若要指定保留某一行，可替换为有意义的列（如数据插入时间）
        ) AS row_num
    FROM fake_stock_option_quotes
) t2 
-- 关联所有字段确保匹配的是同一行
ON t1.symbol = t2.symbol
AND t1.expiration_date = t2.expiration_date
AND t1.is_call = t2.is_call
AND t1.strike_price = t2.strike_price
AND t1.date = t2.date
-- 这里需要补充表中其他所有字段，确保唯一匹配
AND t1.open_price = t2.open_price -- 示例字段，替换为你表中的实际列
AND t1.high_price = t2.high_price
AND t1.low_price = t2.low_price
AND t1.close_price = t2.close_price
WHERE t2.row_num > 1;

如果表中字段较多，这种写法比较繁琐，可以改用临时表中转的方式：

1. 创建临时表存储去重后的数据：

CREATE TEMPORARY TABLE temp_option_quotes
SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY symbol, expiration_date, is_call, strike_price, date
            ORDER BY (SELECT NULL)
        ) AS row_num
    FROM fake_stock_option_quotes
) t
WHERE row_num = 1;

2. 清空原表并插入去重后的数据：

TRUNCATE TABLE fake_stock_option_quotes;
INSERT INTO fake_stock_option_quotes
SELECT * FROM temp_option_quotes;

注意：临时表操作需要确保数据安全，建议先备份原表。

方法二：新增ID列（最简洁可靠） #

如果可以接受新增ID列，这是最不容易出错的方案：

1. 添加自增主键ID：

ALTER TABLE fake_stock_option_quotes ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

2. 删除重复行（保留每个组中ID最小的行）：

DELETE t1
FROM fake_stock_option_quotes t1
JOIN fake_stock_option_quotes t2
ON t1.symbol = t2.symbol
AND t1.expiration_date = t2.expiration_date
AND t1.is_call = t2.is_call
AND t1.strike_price = t2.strike_price
AND t1.date = t2.date
WHERE t1.id > t2.id;

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