如何清理User表重复数据并同步更新Order表关联UID？

核心需求 #

• 清理User表中重复的用户数据，仅保留每个用户的最小ID记录
• 将Order表中关联到重复用户的UID统一更新为该用户的最小ID

更新Order表UID的解决方案 #

要完成订单表的UID更新，需要先建立「用户名-最小用户ID」的映射关系，再通过关联更新替换旧的UID：

方法1：使用临时表（可读性高，适合复杂场景）

-- 第一步：创建临时表存储每个用户的最小ID
CREATE TEMPORARY TABLE user_min_id AS
SELECT Name, MIN(ID) AS min_id
FROM User
GROUP BY Name;

-- 第二步：验证更新结果（先确认无误再执行UPDATE）
SELECT 
    o.ID AS order_id,
    o.UID AS old_uid,
    umi.min_id AS new_uid,
    o.BILLING_ADDR,
    o.STATE
FROM Order o
JOIN User u ON o.UID = u.ID
JOIN user_min_id umi ON u.Name = umi.Name
WHERE o.UID != umi.min_id;

-- 第三步：执行更新
UPDATE Order o
JOIN User u ON o.UID = u.ID
JOIN user_min_id umi ON u.Name = umi.Name
SET o.UID = umi.min_id;

方法2：直接使用子查询（更简洁）

如果不需要临时表，可以直接用子查询关联更新：

-- 先验证结果
SELECT 
    o.ID AS order_id,
    o.UID AS old_uid,
    umi.min_id AS new_uid,
    o.BILLING_ADDR,
    o.STATE
FROM Order o
JOIN User u ON o.UID = u.ID
JOIN (
    SELECT Name, MIN(ID) AS min_id
    FROM User
    GROUP BY Name
) umi ON u.Name = umi.Name
WHERE o.UID != umi.min_id;

-- 执行更新
UPDATE Order o
JOIN User u ON o.UID = u.ID
JOIN (
    SELECT Name, MIN(ID) AS min_id
    FROM User
    GROUP BY Name
) umi ON u.Name = umi.Name
SET o.UID = umi.min_id;

完整操作流程 #

1. 备份数据：执行任何修改操作前，务必备份User和Order表，避免数据丢失。
2. 确认重复用户：用你已有的SQL检查重复数据：

SELECT Name, count(1) 
FROM User 
GROUP BY Name
HAVING count(1) > 1 
ORDER BY count(1) DESC;

3. 更新Order表UID：执行上述更新语句，确保所有订单关联到正确的最小用户ID。
4. 清理User表重复数据：执行你已有的删除语句（添加子查询别名，避免部分数据库报错）：

DELETE FROM `User` 
WHERE ID NOT IN (
    SELECT * FROM (
        SELECT MIN(ID) FROM User 
        GROUP BY Name
    ) AS temp_user
);

执行后预期结果 #

• User表：仅保留每个用户的最小ID记录

+----+-----------+--------+
| ID | Name      | Gender |
+----+-----------+--------+
| 1  | John Doe  | M      |
+----+-----------+--------+

• Order表：所有关联到重复用户的UID均更新为最小ID

+----+-----+---------------+-------+
| ID | UID | BILLING_ADDR  | STATE |
+----+-----+---------------+-------+
| 1  | 1   | XXX           | XX    |
| 2  | 1   | XXX           | XX    |
| 3  | 1   | XXX           | XX    |
| 4  | 1   | XXX           | XX    |
+----+-----+---------------+-------+

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