MySQL查询JSON数组字段包含特定字符串的方法！
MySQL查询JSON数组字段包含特定字符串的方法！
问题背景
在MySQL数据库中，当某个字段存储的是JSON数组（如["喷绘","2.6m喷绘","M喷绘","直喷","双透","气模"]），需要查询数组中包含特定字符串（如’气模’）的记录时，传统的LIKE语句无法直接使用。本文介绍两种高效的解决方案。
解决方案对比
1. 精确匹配方案（推荐）
当需要完全匹配数组中的元素时（如精确查找"气模"）：
|
1
2
3
4
5
6
|
SELECT *FROM process_unit_pricesWHEREprocess_name = '制图'AND JSON_CONTAINS(craft_name, JSON_QUOTE('气模'))LIMIT 1; |
1.核心函数：
JSON_QUOTE(): 将字符串转为JSON格式（如"气模"）JSON_CONTAINS(): 检查JSON数组中是否包含指定值
2.优点：效率高，精确匹配数组元素
3.注意：必须元素完全相等才会匹配（如"气模车"不会匹配）
2. 模糊匹配方案
当需要部分匹配元素内容时（如查找包含"气模"子串的元素）：
|
1
2
3
4
5
6
|
SELECT *FROM process_unit_pricesWHEREprocess_name = '制图'AND JSON_SEARCH(craft_name, 'one', '%气模%') IS NOT NULLLIMIT 1; |
1.核心函数：
JSON_SEARCH(): 搜索JSON中匹配模式的路径'one': 找到第一个匹配即停止'%气模%': SQL的LIKE通配符模式
2.优点：支持模糊匹配
3.注意：效率低于精确匹配，大数据量时需谨慎
参数化查询示例
在MyBatis等ORM框架中使用（如Spring Boot项目）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<!-- 精确匹配 --><select id="findProcess" resultType="ProcessPrice">SELECT * FROM process_unit_pricesWHERE process_name = #{processType}AND JSON_CONTAINS(craft_name, JSON_QUOTE(#{craft}))LIMIT 1</select><!-- 模糊匹配 --><select id="findProcessFuzzy" resultType="ProcessPrice">SELECT * FROM process_unit_pricesWHERE process_name = #{processType}AND JSON_SEARCH(craft_name, 'one', CONCAT('%', #{craft}, '%')) IS NOT NULLLIMIT 1</select> |
使用场景建议
| 场景 | 推荐方案 | 示例 |
|---|---|---|
| 分类标签匹配 | 精确匹配 | 工艺类型=[“雕刻”,“喷涂”] |
| 关键字搜索 | 模糊匹配 | 描述包含"紧急"的订单 |
| 多值属性过滤 | 精确匹配 | 颜色=[“红”,“蓝”] |
性能优化贴士
索引优化：MySQL 8.0+可为JSON字段创建函数索引
|
1
|
CREATE INDEX idx_craft ON process_unit_prices((CAST(craft_name AS CHAR(255)))); |
数据规范：若常需查询，建议拆分为关系表
避免全模糊：%气模%无法使用索引，尽量用气模%
经验总结：JSON字段查询虽灵活，但需谨慎使用。精确匹配首选JSON_CONTAINS，模糊需求再用JSON_SEARCH，并注意性能影响。在数据设计阶段评估是否真需JSON结构，关系表通常更高效。
知识扩展
下面小编为大家整理了MYSQL 匹配查询JSON字段的相关方法，希望对大家有所帮助
在 MySQL 中，如果你存储的是 JSON 数据，并且需要检查某个 JSON 字段是否包含一个特定的键值对（例如 {user1: 1}），你可以使用 MySQL 提供的 JSON 函数来实现。
1. 使用 JSON_CONTAINS() 函数
JSON_CONTAINS() 函数可以检查一个 JSON 字段是否包含另一个 JSON 值。该函数返回一个布尔值，如果第一个参数（JSON 数据）包含第二个参数（查询条件），则返回 1，否则返回 0。
示例查询：
假设你的表结构如下：
|
1
2
3
4
|
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,user_data JSON); |
你可以通过 JSON_CONTAINS() 函数来检查 JSON 字段是否包含 {user1: 1}。
|
1
2
3
|
SELECT * FROM users WHERE JSON_CONTAINS(user_data, '{"user1": 1}'); |
这条 SQL 语句会返回所有 user_data 字段包含 {user1: 1} 键值对的记录。
2. JSON_CONTAINS() 函数的使用细节
- 第一个参数是你要查询的 JSON 字段。
- 第二个参数是你要查找的 JSON 数据，必须是一个有效的 JSON 格式字符串。
- 该函数会检查 JSON 字段中是否存在第二个参数所表示的键值对。
3. 查询部分匹配
如果你只想检查 JSON 字段中是否存在某个键（例如 user1）并且该键的值是 1，而不关心 JSON 字段中的其他内容，你仍然可以使用 JSON_CONTAINS()，但是需要确保第二个参数符合你想要的结构。
|
1
2
3
|
SELECT * FROM users WHERE JSON_CONTAINS(user_data, '{"user1": 1}'); |
4. 检查 JSON 字段中是否包含某个键
如果你不关心键值对，只想检查 JSON 字段中是否包含某个键（比如 user1），可以使用 JSON_EXTRACT() 或 JSON_UNQUOTE() 与 IS NOT NULL 来实现。
|
1
2
3
|
SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.user1')) = '1'; |
JSON_EXTRACT(user_data, '$.user1')提取user_data字段中的user1键的值。JSON_UNQUOTE()去除引号，使其返回一个普通值。- 比较提取的值是否等于
'1'。
小结
JSON_CONTAINS()：用于检查 JSON 字段是否包含某个指定的键值对。JSON_EXTRACT()+JSON_UNQUOTE()：可以用来提取并比较 JSON 字段中的值。
使用 JSON_CONTAINS() 是检查 JSON 数据是否包含某个完整键值对的简便方法。如果你需要更细粒度的查询（如只关心键是否存在且其值为特定值），可以结合 JSON_EXTRACT() 和 JSON_UNQUOTE() 进行操作。
到此这篇关于MySQL查询JSON数组字段包含特定字符串的方法的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。