MySQL存储过程中通过传入映射参数关联两张表

核心思路 #

把传入的映射字符串解析成可关联的中间数据集，再通过这个数据集关联表1和表2。优先推荐MySQL 8.0+的JSON解析方案，低版本可以用字符串拆分+临时表的方式实现。

方案一：MySQL 8.0+ 用JSON_TABLE解析映射字符串 #

利用MySQL 8.0引入的JSON_TABLE函数，直接将JSON格式的映射字符串转换成行数据，无需临时表也能完成关联。

存储过程代码 #

DELIMITER //

CREATE PROCEDURE get_related_data(IN p_mapping JSON)
BEGIN
    WITH mapping_data AS (
        SELECT 
            jt.map_key AS t1_key,
            jt.t2_id
        FROM JSON_TABLE(
            p_mapping,
            '$[*]' COLUMNS (
                map_key INT PATH '$[0]',  -- 取数组第一个元素作为表1的key值
                t2_id INT PATH '$[1]'     -- 取数组第二个元素作为表2的id值
            )
        ) AS jt
    )
    SELECT 
        t1.id AS `Table 1 id`,
        t1.name,
        t2.id AS `Table 2 id`,
        t2.`header 2` AS name
    FROM table1 t1
    JOIN mapping_data md ON t1.`key` = md.t1_key
    JOIN table2 t2 ON md.t2_id = t2.id;
END //

DELIMITER ;

调用方式 #

传入符合格式的JSON数组字符串即可：

CALL get_related_data('[[4,1],[5,3]]');

方案二：MySQL 5.7及以下 用字符串拆分+临时表 #

如果你的MySQL版本不支持JSON_TABLE，可通过字符串拆分将映射关系存入临时表，再进行关联。

存储过程代码 #

DELIMITER //

CREATE PROCEDURE get_related_data_old(IN p_mapping VARCHAR(255))
BEGIN
    -- 创建临时表存储映射关系
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_mapping (
        t1_key INT,
        t2_id INT
    );
    TRUNCATE TABLE temp_mapping;

    -- 处理映射字符串，拆分出每一组映射关系
    SET @raw_str = TRIM(BOTH '[]' FROM p_mapping);
    SET @pos = LOCATE('},{', @raw_str);

    WHILE @pos > 0 DO
        SET @item = SUBSTRING(@raw_str, 1, @pos - 1);
        SET @t1_key = SUBSTRING_INDEX(@item, ',', 1);
        SET @t2_id = SUBSTRING_INDEX(@item, ',', -1);
        INSERT INTO temp_mapping VALUES (@t1_key, @t2_id);
        
        SET @raw_str = SUBSTRING(@raw_str, @pos + 3);
        SET @pos = LOCATE('},{', @raw_str);
    END WHILE;

    -- 处理最后一组映射
    SET @t1_key = SUBSTRING_INDEX(@raw_str, ',', 1);
    SET @t2_id = SUBSTRING_INDEX(@raw_str, ',', -1);
    INSERT INTO temp_mapping VALUES (@t1_key, @t2_id);

    -- 关联查询得到结果
    SELECT 
        t1.id AS `Table 1 id`,
        t1.name,
        t2.id AS `Table 2 id`,
        t2.`header 2` AS name
    FROM table1 t1
    JOIN temp_mapping md ON t1.`key` = md.t1_key
    JOIN table2 t2 ON md.t2_id = t2.id;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_mapping;
END //

DELIMITER ;

调用方式 #

传入原格式的映射字符串：

CALL get_related_data_old('[{4,1},{5,3}]');

注意事项 #

1. 优先选择方案一，JSON_TABLE的解析效率更高，代码更简洁，也更不容易因为字符串格式细微差异导致错误。
2. 确保传入的映射字符串格式严格符合要求，避免解析失败。比如方案一中的JSON数组必须是[[a,b],[c,d]]格式，方案二中的字符串必须是[{a,b},{c,d}]格式。

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