MariaDB双主键表删除首个主键重复行的技术求助

问题分析 #

你的表使用(id, name)作为复合主键，这种设计本身允许同一id对应不同name的行存在。之前用CTE执行DELETE无效，是因为MariaDB不支持直接从CTE删除原表数据——CTE仅作为临时查询结果，无法直接关联原表完成删除操作。

可行解决方案 #

方法1：通过ROW_NUMBER()子查询关联删除 #

这种方法能精准控制保留哪一行（通过ORDER BY指定优先级）：

-- 先验证要删除的行（执行删除前务必先做这一步）
SELECT p1.*
FROM product_names p1
JOIN (
    SELECT id, name,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) AS rn
    FROM product_names
) p2 ON p1.id = p2.id AND p1.name = p2.name
WHERE p2.rn > 1;

-- 确认无误后执行删除
DELETE p1
FROM product_names p1
JOIN (
    SELECT id, name,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) AS rn
    FROM product_names
) p2 ON p1.id = p2.id AND p1.name = p2.name
WHERE p2.rn > 1;

• 说明：PARTITION BY id按id分组，ORDER BY name决定每组内保留的行（示例中保留name排序最靠前的行，即name='a'的行）。如果需要按其他字段优先级保留，替换ORDER BY后的字段即可（比如smth或创建时间）。

方法2：通过聚合函数筛选保留行 #

如果只需要保留每个id对应的某一行（比如name最小/最大的行），可以用NOT IN结合聚合查询：

-- 先验证
SELECT *
FROM product_names
WHERE (id, name) NOT IN (
    SELECT id, MIN(name)
    FROM product_names
    GROUP BY id
);

-- 执行删除
DELETE FROM product_names
WHERE (id, name) NOT IN (
    SELECT id, MIN(name)
    FROM product_names
    GROUP BY id
);

• 说明：MIN(name)会选择每个id中name最小的行，若要保留name最大的行，替换为MAX(name)即可。

后续优化建议 #

删除重复行后，建议调整表结构避免后续再出现id重复的情况：

-- 删除原复合主键
ALTER TABLE product_names DROP PRIMARY KEY;
-- 将id设为主键（确保id唯一）
ALTER TABLE product_names ADD PRIMARY KEY (id);

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