如何用单条高效SQL基于Mapping表更新两表关联后的字段

表结构说明 #

Table1 (Type Table) #

id | before_id | after_id
----+-----------+----------
1   | 20        | 17

Table2 (History Table) #

id | user_id | start_at           | end_at
----+---------+--------------------+--------------------
1   | 2013    | 2022-11-07 00:21:30| 2022-11-07 04:06:00

Table3 (Mapping Table) #

id | type
----+------
17  | 1
18  | 2
19  | 3
20  | 4
21  | 5
22  | 6
23  | 7

需求 #

将Table1与Table2按id关联，通过Table3的映射关系（id对应type）替换Table1的before_id和after_id，最终得到包含两表字段且替换后值的结果集；同时需保证方案在Table1、Table2数据量极大时的高效性。

方案一：单条SQL查询实现 #

可以通过多次JOIN直接查询出目标结果，无需临时表。核心是对Table3做两次关联，分别匹配before_id和after_id对应的映射值：

SELECT 
    t1.id,
    m_before.type AS before_id,
    m_after.type AS after_id,
    t2.user_id,
    t2.start_at,
    t2.end_at
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.id
JOIN Table3 m_before ON t1.before_id = m_before.id
JOIN Table3 m_after ON t1.after_id = m_after.id;

高效性优化 #

• 确保以下字段存在主键或唯一索引：
  • Table1.id、Table2.id（关联键，加速两表JOIN）
  • Table3.id（映射表的匹配键，避免全表扫描）
• 若只需部分数据，添加WHERE条件过滤，减少处理的数据量。

方案二：临时表方案 #

若数据量极大，单次JOIN压力过高，可分两步操作：

步骤1：创建临时表存储Table1与Table2的关联结果 #

CREATE TEMPORARY TABLE temp_combined
SELECT 
    t1.id,
    t1.before_id,
    t1.after_id,
    t2.user_id,
    t2.start_at,
    t2.end_at
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.id;

步骤2：关联映射表查询替换后结果 #

SELECT 
    tc.id,
    m_before.type AS before_id,
    m_after.type AS after_id,
    tc.user_id,
    tc.start_at,
    tc.end_at
FROM temp_combined tc
JOIN Table3 m_before ON tc.before_id = m_before.id
JOIN Table3 m_after ON tc.after_id = m_after.id;

临时表方案说明 #

• 临时表仅会话可见，不会占用永久存储，适合大数据量下拆分压力
• 若需更新原Table1而非仅查询，可使用UPDATE ... JOIN语法直接更新：

UPDATE Table1 t1
JOIN Table3 m_before ON t1.before_id = m_before.id
JOIN Table3 m_after ON t1.after_id = m_after.id
SET 
    t1.before_id = m_before.type,
    t1.after_id = m_after.type;

（注：更新原表前建议备份数据，且确保映射关系是一一对应的）

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