MySQL 5.7合并多行部分重复数据实现字段唯一约束设置

操作前务必全量备份原表，避免数据丢失，建议先在测试环境验证逻辑再操作生产库，以下示例中原表名为user_contacts，可替换为你的实际表名。

步骤1：备份原表 #

CREATE TABLE user_contacts_bak LIKE user_contacts;
INSERT INTO user_contacts_bak SELECT * FROM user_contacts;

步骤2：生成重复数据分组映射表 #

关联逻辑为：两行只要有相同的非空email或相同的非空website，就归为同一合并组，每组取最大id作为主id：

-- 建分组映射临时表
CREATE TEMPORARY TABLE group_mapping (
    id INT PRIMARY KEY,
    group_max_id INT,
    INDEX idx_group_max_id(group_max_id)
);

-- 写入分组关联关系
INSERT INTO group_mapping (id, group_max_id)
SELECT 
    t1.id,
    MAX(t2.id) AS group_max_id
FROM user_contacts t1
JOIN user_contacts t2 
ON (t1.email IS NOT NULL AND t2.email IS NOT NULL AND t1.email = t2.email)
OR (t1.website IS NOT NULL AND t2.website IS NOT NULL AND t1.website = t2.website)
GROUP BY t1.id;

步骤3：生成合并后的结果表 #

合并表提前预置email和website的唯一约束，符合你的最终要求：

CREATE TABLE user_contacts_merged (
    id INT PRIMARY KEY COMMENT '重复组的最大id',
    ids TEXT COMMENT '该组合并的所有id集合',
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    website VARCHAR(255),
    UNIQUE KEY uk_email(email),
    UNIQUE KEY uk_website(website)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 写入合并后的数据
INSERT INTO user_contacts_merged (id, ids, first_name, last_name, email, website)
SELECT 
    gm.group_max_id AS id,
    GROUP_CONCAT(DISTINCT uc.id ORDER BY uc.id SEPARATOR ',') AS ids,
    -- 取组内长度最长的first_name，长度相同取id最小的最早数据
    SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT uc.first_name ORDER BY CHAR_LENGTH(uc.first_name) DESC, uc.id ASC SEPARATOR '|'), '|', 1) AS first_name,
    -- 取组内长度最长的last_name
    SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT uc.last_name ORDER BY CHAR_LENGTH(uc.last_name) DESC, uc.id ASC SEPARATOR '|'), '|', 1) AS last_name,
    -- 取组内非空的email
    MAX(uc.email) AS email,
    -- 取组内非空的website
    MAX(uc.website) AS website
FROM group_mapping gm
JOIN user_contacts uc ON gm.id = uc.id
GROUP BY gm.group_max_id
HAVING email IS NOT NULL OR website IS NOT NULL; -- 过滤两个字段都为空的无效数据

步骤4：验证数据后替换原表 #

确认user_contacts_merged的数据符合预期后，执行替换操作：

RENAME TABLE user_contacts TO user_contacts_old, user_contacts_merged TO user_contacts;

注意事项 #

• 如果同一分组内存在多个不同的非空email/website，可将MAX()替换为按长度或id倒序取第一个的逻辑，示例：

  -- 取长度最长的email，长度相同则取id最大的最新数据
  SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT uc.email ORDER BY CHAR_LENGTH(uc.email) DESC, uc.id DESC SEPARATOR '|'), '|', 1) AS email
  

• 如果email或website字段存在大小写不一致的问题，分组前可以先转成小写统一匹配，比如将匹配条件改为LOWER(t1.email) = LOWER(t2.email)
• 50万条数据量不大，整个操作耗时在几秒到几十秒区间，不需要做分批次处理

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