讲师中心 微信公众号

工具推荐

视频效率加速
updream

一键生成高质量推广成片

AniShort
AniShort

AI短剧协同制作平台

SoundView
SoundView

字幕擦除翻译、配音口型同步

Seko
Seko

AI短剧一键生成成片

如何在MySQL 8.0存储过程中解析并处理JSON数组

星芳姑娘_6318

星芳姑娘_6318

发布时间:2026-11-19 09:24:26

|

158人浏览过

|

来源于php中文网

原创

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

如何在mysql 8.0存储过程中解析并处理json数组

存储过程里怎么传入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 更轻量、更快。

Json To Jianying Description
Json To Jianying Description

将包含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 开销翻倍。

热门AI工具

更多
WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

创客贴AI

创客贴AI是一款AI图像与设计工具,AI辅助的智能在线设计工具。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

花生AI
花生AI Hot

一款AI视频创作工具,主要用于B站推出的AI视频创作工具,适合需要提升相关任务效率的用户。

立刻MV
立刻MV Hot

立刻MV是一款AI文本写作工具,AI 音乐视频(MV)创作工具。

UP简历
UP简历 Hot

一款AI办公效率工具,主要用于基于AI技术的免费在线简历制作工具,适合需要提升相关任务效率的用户。

讯飞智作

讯飞智作是一款AI视频创作工具,AI文本配音工具,数字人课程、营销视频制作。

Flowith
Flowith Hot

Flowith是一款基于画布和智能体工作流的 AI 协作工作空间。

相关专题

更多
mysql修改数据表名
mysql修改数据表名

MySQL修改数据表:1、首先查看数据库中所有的表,代码为:‘SHOW TABLES;’;2、修改表名,代码为:‘ALTER TABLE 旧表名 RENAME [TO] 新表名;’。php中文网还提供MySQL的相关下载、相关课程等内容,供大家免费下载使用。

2673

2023.06.20

MySQL创建存储过程
MySQL创建存储过程

存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句调用存储过程智能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。php中文网还提供MySQL创建存储过程的相关下载、相关课程等内容,供大家免费下载使用。

1599

2023.06.21

mongodb和mysql的区别
mongodb和mysql的区别

mongodb和mysql的区别:1、数据模型;2、查询语言;3、扩展性和性能;4、可靠性。本专题为大家提供mongodb和mysql的区别的相关的文章、下载、课程内容,供大家免费下载体验。

935

2023.07.18

mysql密码忘了怎么查看
mysql密码忘了怎么查看

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql密码忘了怎么办呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

3852

2023.07.19

mysql创建数据库
mysql创建数据库

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql怎么创建数据库呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

6728

2023.07.25

mysql默认事务隔离级别
mysql默认事务隔离级别

MySQL是一种广泛使用的关系型数据库管理系统,它支持事务处理。事务是一组数据库操作,它们作为一个逻辑单元被一起执行。为了保证事务的一致性和隔离性,MySQL提供了不同的事务隔离级别。php中文网给大家带来了相关的教程以及文章欢迎大家前来学习阅读。

1399

2023.08.08

sqlserver和mysql区别
sqlserver和mysql区别

SQL Server和MySQL是两种广泛使用的关系型数据库管理系统。它们具有相似的功能和用途,但在某些方面存在一些显著的区别。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

7011

2023.08.11

mysql忘记密码
mysql忘记密码

MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。那么忘记mysql密码我们该怎么解决呢?php中文网给大家带来了相关的教程以及其他关于mysql的文章,欢迎大家前来学习阅读。

5962

2023.08.14

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

0

2026.11.17

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn