多值属性搜索与跨表插入：逗号分隔多值字段关联迁移实现

核心实现逻辑 #

• 因为TableB的mo_codes是逗号拼接的多值字符串，无法直接和TableA的整数类型mo_code做关联匹配，整体流程分三步：
  1. 将TableB中每行的逗号分隔mo_codes拆分为「单个dr_num对应单个mo_code」的结构化临时结果
  2. 将拆分得到的临时结果和TableA按mo_code做内连接，筛选出TableA中真实存在的mo_code匹配记录
  3. 将匹配成功的dr_num、mo_code结果写入新建的目标表

不同数据库的具体实现代码 #

因为不同数据库的字符串拆分函数存在差异，以下是主流数据库可直接运行的实现：

MySQL 8.0及以上版本 #

通过JSON_TABLE实现字符串拆分，避免自定义函数的额外开销：

CREATE TABLE target_new_table AS
WITH split_b AS (
    SELECT
        b.dr_num,
        -- 拆分出的字符串转整数类型匹配TableA的字段
        CAST(jt.mo_item AS UNSIGNED) AS mo_code
    FROM TableB b
    -- 将逗号拼接的字符串去空格、转成JSON数组后逐行拆分
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(REPLACE(b.mo_codes, ' ', ''), ',', '","'), '"]'),
        '$[*]' COLUMNS (
            mo_item VARCHAR(30) PATH '$'
        )
    ) jt
)
SELECT s.dr_num, s.mo_code
FROM split_b s
-- 内连接筛选TableA中存在的mo_code
INNER JOIN TableA a
    ON s.mo_code = a.mo_code;

PostgreSQL 版本 #

利用内置的string_to_array+unnest组合实现拆分，语法简洁执行效率高：

CREATE TABLE target_new_table AS
SELECT
    b.dr_num,
    CAST(sp.mo_text AS INTEGER) AS mo_code
FROM TableB b
-- 逐行关联拆分字符串数组
CROSS JOIN LATERAL unnest(
    string_to_array(REPLACE(b.mo_codes, ' ', ''), ',')
) AS sp(mo_text)
INNER JOIN TableA a
    ON sp.mo_text = a.mo_code;

SQL Server 2016及以上版本 #

通过内置STRING_SPLIT函数实现拆分：

CREATE TABLE target_new_table AS
SELECT
    b.dr_num,
    CAST(sp.value AS INT) AS mo_code
FROM TableB b
CROSS APPLY STRING_SPLIT(REPLACE(b.mo_codes, ' ', ''), ',') sp
INNER JOIN TableA a
    ON sp.value = a.mo_code;

落地注意事项 #

• 拆分逻辑中要提前用REPLACE去掉mo_codes字段里的多余空格、空值，避免类型转换时报错或者因为空格导致匹配失效
• 字段类型一定要做显式转换，不要依赖数据库的隐式类型转换，否则会导致TableA上mo_code的索引失效，拖慢大表场景下的查询速度
• 如果后续业务频繁需要这类关联匹配，长期最优方案是调整表结构：不要在单字段中存储逗号拼接的多值数据，单独建立dr_num和mo_code的关联表，从设计层面避免字符串拆分带来的性能损耗、数据一致性差的问题

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