MySQL8存储过程JSON参数无法接受JSON字符串值的解决方法问询

问题背景 #

现有存储过程my_test_procedure接收JSON类型参数，将数据插入my_test_table表。当参数中ud字段为带引号包裹的JSON字符串时，会触发3140错误；若ud为JSON对象则正常执行。要求在MySQL代码内实现兼容，且传入的payload格式不可修改。

原存储过程代码：

DELIMITER \

DROP PROCEDURE IF EXISTS my_test_procedure \

CREATE PROCEDURE my_test_procedure(my_payload JSON)

BEGIN

    INSERT INTO my_test_table (id, ud)
    SELECT jt.id,
           jt.ud
    FROM JSON_TABLE(my_payload, '$[*]' columns (
        id int path '$.id',
        ud json path '$.ud'
        )) AS jt;
END \

DELIMITER ;

表结构DDL：

CREATE TABLE my_test_table
(
    id int,
    ud varchar(255),
    PRIMARY KEY (id)
);

报错调用示例（call_1）：

CALL my_test_procedure('[{"id":1,"ud":"{\"1\":5,\"2\":6,\"3\":7}"}]');

错误信息：

Error Code: 3140. Invalid JSON text: "Missing a comma or '}' after an object member." at position 17 in value for column '.my_payload'.

正常调用示例（call_2）：

CALL my_test_procedure('[{"id":1,"ud":{\"1\":5,\"2\":6,\"3\":7}}]'); -- 或
CALL my_test_procedure('[{"id":1,"ud":{"1":5,"2":6,"3":7}}]');

问题原因 #

原存储过程中JSON_TABLE将ud字段定义为json类型路径，当传入的ud是带引号的JSON字符串时，MySQL尝试直接将其解析为JSON对象，因字符串内部的转义格式导致解析失败，触发3140错误。而目标表的ud字段实际是varchar类型，无需强制解析为JSON对象，只需正确提取并转换为字符串格式即可。

兼容方案 #

修改存储过程，通过JSON_TYPE判断ud字段的类型：

• 若为STRING类型（即带引号的JSON字符串），使用JSON_UNQUOTE去除外层引号，提取内部的JSON内容；
• 若为OBJECT类型（即JSON对象），直接转换为字符串格式存储。

修改后的存储过程代码：

DELIMITER \

DROP PROCEDURE IF EXISTS my_test_procedure \

CREATE PROCEDURE my_test_procedure(my_payload JSON)
BEGIN
    INSERT INTO my_test_table (id, ud)
    SELECT 
        jt.id,
        CASE JSON_TYPE(jt.ud)
            WHEN 'STRING' THEN JSON_UNQUOTE(jt.ud)
            ELSE CAST(jt.ud AS CHAR)
        END AS ud
    FROM JSON_TABLE(my_payload, '$[*]' columns (
        id int path '$.id',
        ud json path '$.ud'
    )) AS jt;
END \

DELIMITER ;

验证 #

1. 执行报错调用示例（call_1）：

CALL my_test_procedure('[{"id":1,"ud":"{\"1\":5,\"2\":6,\"3\":7}"}]');

执行成功，my_test_table中id=1的ud字段值为{"1":5,"2":6,"3":7}。

2. 执行正常调用示例（call_2）：

CALL my_test_procedure('[{"id":1,"ud":{"1":5,"2":6,"3":7}}]');

执行成功，ud字段值与call_1结果一致，实现两种格式的兼容。

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