MySQL中查询JSON数组字段包含特定字符串的方法

问题背景

在MySQL数据库中，当某个字段存储的是JSON数组（如["喷绘","2.6m喷绘","M喷绘","直喷","双透","气模"]），需要查询数组中包含特定字符串（如’气模’）的记录时，传统的LIKE语句无法直接使用。本文介绍两种高效的解决方案。

---

解决方案对比

1. 精确匹配方案（推荐）

当需要完全匹配数组中的元素时（如精确查找"气模"）：

SELECT *
FROM process_unit_prices
WHERE process_name = '制图' AND JSON_CONTAINS(craft_name, JSON_QUOTE('气模'))
LIMIT 1;

• 核心函数：
  • JSON_QUOTE(): 将字符串转为JSON格式（如"气模"）
  • JSON_CONTAINS(): 检查JSON数组中是否包含指定值
• 优点：效率高，精确匹配数组元素
• 注意：必须元素完全相等才会匹配（如"气模车"不会匹配）

2. 模糊匹配方案

当需要部分匹配元素内容时（如查找包含"气模"子串的元素）：

SELECT *
FROM process_unit_prices
WHERE process_name = '制图' AND JSON_SEARCH(craft_name, 'one', '%气模%') IS NOT NULL
LIMIT 1;

• 核心函数：
  • JSON_SEARCH(): 搜索JSON中匹配模式的路径
  • 'one': 找到第一个匹配即停止
  • '%气模%': SQL的LIKE通配符模式
• 优点：支持模糊匹配
• 注意：效率低于精确匹配，大数据量时需谨慎

---

参数化查询示例

在MyBatis等ORM框架中使用（如Spring Boot项目）：

<!-- 精确匹配 -->
<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>

---

使用场景建议

场景	推荐方案	示例
分类标签匹配	精确匹配	工艺类型=[“雕刻”,“喷涂”]
关键字搜索	模糊匹配	描述包含"紧急"的订单
多值属性过滤	精确匹配	颜色=[“红”,“蓝”]

---

性能优化贴士

1. 索引优化：MySQL 8.0+可为JSON字段创建函数索引

   CREATE INDEX idx_craft ON process_unit_prices((CAST(craft_name AS CHAR(255))));
   

2. 数据规范：若常需查询，建议拆分为关系表
3. 避免全模糊：%气模%无法使用索引，尽量用气模%

经验总结：JSON字段查询虽灵活，但需谨慎使用。精确匹配首选JSON_CONTAINS，模糊需求再用JSON_SEARCH，并注意性能影响。在数据设计阶段评估是否真需JSON结构，关系表通常更高效。