SQL两列关联ID映射表替换值的高效实现方法咨询

问题场景 #

现有两张表，每张表均包含2个字段：

• 第一张表table_1存储ID对数据
• 第二张表table_2为ID映射表，存储的ID可能出现在第一张表的任意一列或同时出现在两列中，用于映射到目标ID

table_1样例数据 #

col_a        | col_b
--------------------
42           | 555
555          | 73
61           | 22
444          | 444

table_2样例数据 #

col_x        | col_y
--------------------
555          | 2999
444          | 3333

需求 #

查询table_1的col_a、col_b字段，若字段值存在于table_2的col_x中，则替换为对应的col_y值，期望输出结果：

col_a        | col_b
--------------------
42           | 2999
2999         | 73
61           | 22
3333         | 3333

现有实现 #

目前通过两次LEFT JOIN关联映射表实现（原SQL存在笔误，第二个字段别名误写为col_a）：

select
  coalesce(join_a.col_y, table_1.col_a) as col_a,
  coalesce(join_b.col_y, table_1.col_b) as col_b
from
  table_1
    left join table_2 join_a on table_1.col_a = join_a.col_x
    left join table_2 join_b on table_1.col_b = join_b.col_x

实际业务中存在多张类似table_2的映射表，且ID为字符串类型，可直接通过ID特征判断其归属的映射表，需要更高效的实现方式。

更优实现方案 #

当前写法每一列都要单独关联一次映射表，当映射表数量多、需要替换的列多时，JOIN次数会线性增长，性能很差。结合可以通过ID特征判断映射表归属的前提，有两种效率远高于多次LEFT JOIN的方案：

方案1：长表单次关联+行列转换 #

核心逻辑是先把多列ID拆成「行唯一标识、列名、原始ID」的长表结构，把所有映射表按规则合并成一张总映射表（可以直接加ID特征过滤，只加载需要匹配的映射规则，不需要全量匹配所有映射表数据），只做一次LEFT JOIN拿到映射结果后，再转回多列结构。
这种方案的JOIN次数固定为1次，不会随列数、映射表数量增长，性能提升非常明显。
参考SQL（支持UNPIVOT/PIVOT语法的数据库可直接用，不支持的可以用UNION ALL完成列转行）：

with t1_long as (
  -- 把col_a、col_b拆成行，保留每行的唯一标识rid
  select rid, col_name, ori_id
  from (select rowid as rid, col_a, col_b from table_1) t
  unpivot (ori_id for col_name in (col_a, col_b)) u
),
all_mapping as (
  -- 合并所有映射表，这里可以加ID特征判断过滤，减少匹配数据量
  select col_x, col_y from table_2
  -- 其他映射表直接UNION ALL拼接即可，比如：
  -- union all select col_x, col_y from table_3 where col_x like 'test_prefix%'
)
select col_a, col_b
from (
  select
    l.rid,
    l.col_name,
    coalesce(m.col_y, l.ori_id) as final_id
  from t1_long l
  left join all_mapping m on l.ori_id = m.col_x
) mapped
pivot (max(final_id) for col_name in (col_a, col_b)) p

方案2：自定义确定性标量函数 #

如果数据库支持自定义函数，且映射规则匹配逻辑清晰，这个方案是代码最简洁、性能最高的：写一个确定性标量函数，输入原始ID，内部先按ID特征判断归属的映射表，查询返回映射后的值，无匹配则返回原ID。
因为函数声明为确定性，数据库会自动缓存相同输入的返回结果，重复ID不需要重复查映射表，完全避免多表JOIN的开销。
参考实现：

-- 定义ID映射函数
create function map_id(ori_id varchar(255))
returns varchar(255)
deterministic -- 标记为确定性函数，开启结果缓存
begin
  declare mapped_val varchar(255);
  -- 按ID特征匹配对应映射表
  if ori_id like 'old_%' then
    select col_y into mapped_val from table_2 where col_x = ori_id limit 1;
  -- 其他特征分支匹配其他映射表
  -- elseif ori_id like 't_%' then
  --   select col_y into mapped_val from table_3 where col_x = ori_id limit 1;
  else
    set mapped_val = ori_id;
  end if;
  return coalesce(mapped_val, ori_id);
end;

-- 查询时直接调用函数即可，不需要任何JOIN
select
  map_id(col_a) as col_a,
  map_id(col_b) as col_b
from table_1

选型建议：如果映射表数据量极大（千万级以上），优先选方案1，单次JOIN+提前过滤映射数据的开销可控；如果映射表数据量中等及以下，优先选方案2，维护成本和性能表现都更好。

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