MySQL如何高效执行多join操作：处理逗号分隔answer_ids映射需求

前提假设 #

我们先约定表名和字段名：

• 原题存储答题记录的表名为quiz_record
• 答案映射表名为answer_map，包含两个字段：answer_id INT（答案ID）、answer_text VARCHAR(255)（答案文本）

方案1：MySQL 8.0及以上版本（性能最优） #

利用JSON_TABLE拆分逗号分隔的answer_ids字段，再关联映射表，最后聚合拼接成目标格式字符串，相比循环逐行处理性能提升至少5倍以上，30万行数据单次处理耗时一般在10秒以内。

完整SQL示例： #

-- 临时调大聚合字符串长度限制，避免结果被截断
SET SESSION group_concat_max_len = 102400;

WITH all_answers AS (
    -- 先把20个answer_ids字段拆成每行对应（记录ID、题号、单个answer_id）的结构
    SELECT 
        qr.id,
        v.question_num,
        a.answer_id
    FROM quiz_record qr
    -- 批量把20个answer_ids字段转成行数据
    CROSS JOIN (
        SELECT 1 AS question_num, answer_ids1 AS ids FROM quiz_record UNION ALL
        SELECT 2 AS question_num, answer_ids2 AS ids FROM quiz_record UNION ALL
        SELECT 3 AS question_num, answer_ids3 AS ids FROM quiz_record UNION ALL
        -- 按顺序补全4到19的字段即可，此处省略重复写法
        SELECT 20 AS question_num, answer_ids20 AS ids FROM quiz_record
    ) v
    -- 拆分逗号分隔的ids为单个answer_id
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(v.ids, ',', '","'), '"]'),
        '$[*]' COLUMNS (answer_id INT PATH '$')
    ) AS a
    WHERE v.ids IS NOT NULL AND v.ids != ''
)
-- 关联映射表后聚合拼接成目标格式
SELECT 
    aa.id,
    GROUP_CONCAT(
        CONCAT('answer', aa.question_num, '=', am.answer_text) 
        ORDER BY aa.question_num 
        SEPARATOR '&'
    ) AS result_str
FROM all_answers aa
LEFT JOIN answer_map am ON aa.answer_id = am.answer_id
-- 先按题号聚合，单个题目的多个答案用逗号分隔
GROUP BY aa.id, aa.question_num
-- 再按记录ID聚合，拼接成完整的参数字符串
GROUP BY aa.id;

方案2：MySQL 5.x版本兼容方案 #

如果使用的是不支持JSON_TABLE的低版本MySQL，可以用FIND_IN_SET函数实现关联，性能略低于8.0的方案，但依然远优于逐行循环的实现：

SET SESSION group_concat_max_len = 102400;

SELECT
    qr.id,
    CONCAT(
        -- 逐个拼接20个题目的答案，用IFNULL处理空值避免出现NULL段
        'answer1=', IFNULL((SELECT GROUP_CONCAT(am.answer_text SEPARATOR ',') FROM answer_map am WHERE FIND_IN_SET(am.answer_id, qr.answer_ids1)), ''),
        '&answer2=', IFNULL((SELECT GROUP_CONCAT(am.answer_text SEPARATOR ',') FROM answer_map am WHERE FIND_IN_SET(am.answer_id, qr.answer_ids2)), ''),
        -- 按顺序补全3到19的字段即可，此处省略重复写法
        '&answer20=', IFNULL((SELECT GROUP_CONCAT(am.answer_text SEPARATOR ',') FROM answer_map am WHERE FIND_IN_SET(am.answer_id, qr.answer_ids20)), '')
    ) AS result_str
FROM quiz_record qr;

性能优化建议 #

• 给answer_map的answer_id字段设置主键/唯一索引，关联查询速度会有量级提升
• 如果是高频查询场景，可以将生成的结果字符串预存在原表的冗余字段中，每次数据更新时同步刷新即可

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