MySQL如何将多表无效company_id随机替换为有效ID并统一映射

现有hotel_company、company_places、company三张表：

• company表仅包含有效company_id（如1、2、3）
• hotel_company与company_places中存在不在company表的无效company_id（如5、6），属于生产环境导入低环境导致的数据不匹配

需求：将每个无效company_id统一映射至一个随机有效ID，在两个表中同步替换该无效ID为对应随机有效ID，同时保留原有记录结构。已知可通过SELECT company_id FROM company ORDER BY RAND()获取随机有效ID，但需实现多表多行的统一更新操作。

核心思路是先建立无效ID到随机有效ID的统一映射表，再基于该映射表批量更新两个业务表，确保同一个无效ID在所有表中替换为同一个有效ID。

1. 创建临时映射表 #

生成所有无效ID对应的随机有效ID，确保每个无效ID仅对应一个随机值：

-- 创建临时映射表，存储无效ID与随机有效ID的对应关系
CREATE TEMPORARY TABLE company_id_mapping (
    invalid_id INT PRIMARY KEY,
    valid_id INT NOT NULL
);

-- 批量插入所有无效ID及其随机映射的有效ID
INSERT INTO company_id_mapping (invalid_id, valid_id)
-- 从hotel_company提取无效ID并生成随机映射
SELECT DISTINCT hc.company_id AS invalid_id,
       (SELECT company_id FROM company ORDER BY RAND() LIMIT 1) AS valid_id
FROM hotel_company hc
WHERE hc.company_id NOT IN (SELECT company_id FROM company)
UNION
-- 从company_places提取无效ID并生成随机映射
SELECT DISTINCT cp.company_id AS invalid_id,
       (SELECT company_id FROM company ORDER BY RAND() LIMIT 1) AS valid_id
FROM company_places cp
WHERE cp.company_id NOT IN (SELECT company_id FROM company);

• 使用DISTINCT避免同一无效ID重复生成映射
• UNION合并两个表的无效ID，确保映射表覆盖所有需替换的ID

2. 更新hotel_company表 #

基于临时映射表替换无效ID：

UPDATE hotel_company hc
JOIN company_id_mapping cim ON hc.company_id = cim.invalid_id
SET hc.company_id = cim.valid_id;

3. 更新company_places表 #

同样基于临时映射表同步替换：

UPDATE company_places cp
JOIN company_id_mapping cim ON cp.company_id = cim.invalid_id
SET cp.company_id = cim.valid_id;

4. 清理临时表（可选） #

临时表为会话级，关闭数据库连接后会自动删除，也可手动清理：

DROP TEMPORARY TABLE IF EXISTS company_id_mapping;

注意事项 #

• 执行更新前建议先备份目标表数据，避免误操作
• 每次执行该流程，无效ID的随机映射结果会不同
• 若需固定映射关系，可将临时表改为永久表，手动维护映射规则

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