MySQL 8.0中如何用INSERT SELECT实现staging表到外键表的数据迁移

核心思路 #

临时表中双属性宝可梦以两行存储，需先将同一宝可梦的两个属性聚合为Type1和Type2，再关联Types表匹配对应的PokemonTypeId，最后插入目标表。

具体实现SQL #

第一步：聚合临时表中的宝可梦属性 #

使用CTE（公共表表达式）聚合同一宝可梦的属性，单属性宝可梦的Type2设为NULL：

WITH pokemon_agg AS (
    SELECT 
        DexNumber,
        Name,
        StatTotal,
        HP,
        Attack,
        Defense,
        SpAttack,
        SpDefense,
        Speed,
        MIN(Type) AS Type1,
        -- 双属性取第二个属性，单属性为NULL
        CASE WHEN COUNT(Type) = 2 THEN MAX(Type) ELSE NULL END AS Type2
    FROM pokemontemp
    GROUP BY DexNumber, Name, StatTotal, HP, Attack, Defense, SpAttack, SpDefense, Speed
)

第二步：关联Types表获取PokemonTypeId并插入目标表 #

将聚合结果与Types表关联（兼容单/双属性的匹配逻辑），再插入目标表：

INSERT IGNORE INTO 目标表名 (
    DexNumber, 
    Name, 
    StatTotal, 
    HP, 
    Attack, 
    Defense, 
    SpAttack, 
    SpDefense, 
    Speed, 
    PokemonTypeId
)
SELECT 
    pa.DexNumber,
    pa.Name,
    pa.StatTotal,
    pa.HP,
    pa.Attack,
    pa.Defense,
    pa.SpAttack,
    pa.SpDefense,
    pa.Speed,
    ty.PokemonTypeId
FROM pokemon_agg pa
JOIN Types ty 
    ON (
        -- 匹配双属性，兼容Type1/Type2顺序不一致的情况
        (ty.Type1 = pa.Type1 AND ty.Type2 = pa.Type2) 
        OR (ty.Type2 = pa.Type1 AND ty.Type1 = pa.Type2)
    )
    -- 匹配单属性
    OR (pa.Type2 IS NULL AND ty.Type1 = pa.Type1 AND ty.Type2 IS NULL);

关键说明 #

• 用MIN()和MAX()聚合属性，确保同一宝可梦的两个属性被合并，不受临时表中行顺序影响。
• 关联逻辑同时覆盖单属性、双属性场景，兼容Types表中Type1/Type2的顺序差异。
• 保留INSERT IGNORE，避免因重复DexNumber导致的插入失败。

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