MySQL 8.0存储过程可通过声明IN p_ids JSON参数接收JSON数组字符串,调用时传'[1,2,5]';需用JSON_TABLE展开为行集处理,或用JSON_CONTAINS高效匹配,避免FIND_IN_SET和动态SQL。

存储过程里怎么传入JSON数组参数
MySQL 8.0 存储过程本身不支持数组类型参数,但可以接收 JSON 类型的字符串(如 '[1,2,5]' 或 '[{"id":1,"name":"a"}]'),前提是调用时传入的是合法 JSON 文本。关键点是:必须显式声明参数为 JSON 类型,否则会被当普通字符串处理,后续 JSON 函数会返回 NULL。
常见错误现象:JSON_VALID(@param) 返回 0,或 JSON_LENGTH(@param) 报错“Invalid JSON text”,基本都是因为参数定义成了 VARCHAR 却传了未转义的 JSON 字符串(比如双引号没被正确包裹)。
- 正确声明方式:
IN p_ids JSON,调用时用CALL sp_process_json('[1,2,5]'); - 若只能传字符串(如旧客户端限制),需先用
CAST(p_str AS JSON)转换,但要加IF NOT JSON_VALID(p_str) THEN SIGNAL ...防崩 - 避免在存储过程中拼接 JSON 字符串(如
CONCAT('[', id_list, ']')),极易产生非法格式
用 JSON_TABLE 展开数组并循环处理每一项
JSON_TABLE 是 MySQL 8.0.4+ 唯一能将 JSON 数组「真正展开为多行」的机制,不能用 JSON_EXTRACT 替代——后者只返回一个 JSON 片段,不是结果集。
容易踩的坑:直接在 SELECT 列表里写 JSON_TABLE(...) 会报错 FUNCTION does not exist 或语法错误;它必须出现在 FROM 子句中,且需配合别名。
- 基础写法(处理整数 ID 数组):
SELECT jt.id FROM JSON_TABLE(p_ids, '$[*]' COLUMNS (id INT PATH '$')) AS jt;
- 处理对象数组时,路径是相对于每个元素的:
COLUMNS (user_id INT PATH '$.id', name VARCHAR(50) PATH '$.name') - 字段可能缺失?加
NULL ON ERROR(8.0.21+)或DEFAULT NULL ON EMPTY,否则整行被过滤掉 - 想在存储过程中对每行做
INSERT或UPDATE?直接INSERT INTO t SELECT ... FROM JSON_TABLE(...),别写游标循环——性能差十倍以上
WHERE 条件里高效匹配 JSON 数组中的值
如果只是判断某字段值是否在传入的 JSON 数组里(例如 user.id IN (1,2,5)),用 JSON_CONTAINS() 比 JSON_TABLE 更轻量、更快。
将包含oralBroadcastingList、materialList、bgmInfo等字段的JSON视频素材转换为剪映API可用的自然语言描述,用于生成视频制作指令,包括素材匹配、时间轴计算、字幕同步、特殊效果处理等。
注意类型一致性:数字字段和 JSON 数组里的数字比较时,JSON_CONTAINS(p_ids, CAST(user.id AS JSON)) 必须显式 CAST,否则隐式转换可能失败(尤其遇到 DECIMAL 或带前导零的字符串)。
- 安全写法:
WHERE JSON_CONTAINS(p_ids, CAST(t.id AS JSON))
- 字符串字段(如
status)要包一层JSON_QUOTE():JSON_CONTAINS(p_statuses, JSON_QUOTE(t.status)) - 别在大表上对 JSON 列本身做
JSON_CONTAINS(json_col, ...)——没索引,全表扫描;应把数组参数当“查找列表”,而非查 JSON 字段内容 - 数组很大(>200 项)?先
SET @arr = p_ids;缓存,避免每次 WHERE 都解析同一变量
为什么不用 FIND_IN_SET 或动态拼 SQL
FIND_IN_SET() 看似简单,但在存储过程中用它匹配 JSON 数组是典型误用:它只认逗号分隔的纯字符串(如 '1,2,5'),而 JSON 数组是 '[1,2,5]',直接传进去永远返回 0。
更危险的是拼接 SQL 字符串再用 PREPARE 执行:虽然能绕过参数限制,但引入 SQL 注入风险(尤其当数组来自用户输入),且无法利用查询缓存,执行计划每次重编译。
- 绝对禁止:
SET @sql = CONCAT('SELECT * FROM t WHERE id IN (', p_csv, ')'); - JSON 函数全是内置校验的,
JSON_VALID()可在入口兜底,JSON_CONTAINS()自动跳过非法项 - 低版本兼容需求?别降级用
FIND_IN_SET,改用应用层拆分后批量执行——数据库不该承担解析责任
最易被忽略的一点:JSON 函数在存储过程中不会自动缓存解析结果。同一个 p_ids 参数,如果在多个 WHERE 子句或多个 JSON_TABLE 中重复使用,务必先 SET @cached = p_ids;,否则每次调用都触发完整 JSON 解析,CPU 开销翻倍。










