如何利用JSON存储过程参数批量插入关联表记录？

我有表ja1_surveyors与ja1_stores的关联表ja1_storesurveyor，现需编写一个存储过程，该过程接收三个参数，其中第三个为存储store_id的JSON数组。我尝试编写了如下存储过程，但存在错误，问题集中在INSERT语句的SELECT部分。所有字段类型均为INT(11)，使用的MySQL版本为5.6.41-84.1，请问我遗漏了哪些内容？

/*
    ========================================================================================
    Set the list of stores for a surveyor in a survey. Used with template to create the list
    a user sees to edit, copy and delete surveyors in a survey
    Accepts three arguments:
        arg_srvy_id     Survey key
        arg_srvr_id     Surveyor key
        STORE_LIST  JSON value holding a list of store keys assigned to this survey/surveyor
        STORE_LIST JSON should be in the form: '{store_id:val1},{store_id:val2}' etc.
    ========================================================================================
*/
DROP PROCEDURE IF EXISTS SURVEYOR_LINK_STORES;

DELIMITER //

CREATE PROCEDURE SURVEYOR_LINK_STORES( IN arg_srvy_id INT(11),IN arg_srvr_id INT(11),IN STORE_LIST JSON)
BEGIN
    /*  Remove all links for this surveyor to stores for this survey    */
    DELETE FROM `ja1_storesurveyor`
    WHERE `lnk_strsrvr_srvy_id` = arg_srvy_id AND `lnk_strsrvr_srvr_id` = arg_srvr_id;
    /*  Add links between this survey and surveyor for each key in STORE_LIST   */
    INSERT INTO `ja1_store_surveyor`
    (
        `lnk_strsrvr_srvy_id`,
        `lnk_strsrvr_srvr_id`,
        `lnk_strsrvr_store_id`
    )
    SELECT 
        arg_srvy_id,
        arg_srvr_id,
        STORE_LIST->>`$.store_id`
    FROM STORE_LIST;
END

DELIMITER ;

1. MySQL 5.6不支持原生JSON类型 #

MySQL从5.7.8版本才开始支持原生JSON数据类型，你的5.6版本无法识别JSON参数类型，必须将STORE_LIST的参数类型改为VARCHAR来接收JSON格式的字符串。

2. 关联表名拼写不一致 #

DELETE操作中使用的表名是ja1_storesurveyor，但INSERT操作里写成了ja1_store_surveyor（少了一个s），表名不匹配会导致插入失败，需要统一表名。

3. 无法直接从参数查询数据 #

你不能在SELECT语句中FROM STORE_LIST，因为这是一个存储过程参数，不是数据库表。同时MySQL5.6没有JSON_TABLE这类解析JSON数组的函数，需要通过字符串拆分的方式遍历JSON数组中的每个store_id。

4. JSON格式与解析逻辑错误 #

原注释中描述的'{store_id:val1},{store_id:val2}'不是合法的JSON格式，标准JSON数组应该是[{"store_id":val1},{"store_id":val2}]；另外即使是支持JSON的版本，STORE_LIST->>$.store_id``也只能提取数组第一个元素的store_id，无法遍历所有元素。

修正后的存储过程代码 #

/*
    ========================================================================================
    为调查中的调查员设置门店列表。用于模板创建用户编辑、复制和删除调查员的列表
    接收三个参数:
        arg_srvy_id     调查主键
        arg_srvr_id     调查员主键
        STORE_LIST      存储分配给该调查/调查员的门店主键列表的JSON字符串
        STORE_LIST格式应为: [{"store_id":1},{"store_id":2}]
    ========================================================================================
*/
DROP PROCEDURE IF EXISTS SURVEYOR_LINK_STORES;

DELIMITER //

CREATE PROCEDURE SURVEYOR_LINK_STORES( 
    IN arg_srvy_id INT(11),
    IN arg_srvr_id INT(11),
    IN STORE_LIST VARCHAR(1000) -- 适配MySQL5.6，改用VARCHAR接收JSON字符串
)
BEGIN
    DECLARE v_json_str VARCHAR(1000);
    DECLARE v_start INT DEFAULT 1;
    DECLARE v_end INT;
    DECLARE v_store_id INT;
    
    -- 删除该调查员在当前调查下的所有门店关联
    DELETE FROM `ja1_storesurveyor`
    WHERE `lnk_strsrvr_srvy_id` = arg_srvy_id AND `lnk_strsrvr_srvr_id` = arg_srvr_id;
    
    -- 预处理JSON字符串，去除首尾的[]
    SET v_json_str = TRIM(BOTH '[]' FROM STORE_LIST);
    
    -- 循环拆分JSON数组中的每个store_id对象
    WHILE v_start > 0 DO
        -- 定位当前对象的结束符}的位置
        SET v_end = LOCATE('}', v_json_str, v_start);
        IF v_end = 0 THEN
            SET v_start = 0;
        ELSE
            -- 提取当前对象中的store_id值
            SET v_store_id = SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING(v_json_str, v_start, v_end - v_start + 1), 
                    '"store_id":', 
                    -1
                ), 
                '}', 
                1
            );
            -- 插入关联记录
            INSERT INTO `ja1_storesurveyor`
            (
                `lnk_strsrvr_srvy_id`,
                `lnk_strsrvr_srvr_id`,
                `lnk_strsrvr_store_id`
            )
            VALUES (arg_srvy_id, arg_srvr_id, v_store_id);
            -- 更新起始位置，跳过当前对象和后续的逗号分隔符
            SET v_start = LOCATE(',', v_json_str, v_end) + 1;
            IF v_start = 1 THEN
                SET v_start = 0;
            END IF;
        END IF;
    END WHILE;
END //

DELIMITER ;

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