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;

1.核心函数：

• JSON_QUOTE(): 将字符串转为JSON格式（如"气模"）
• JSON_CONTAINS(): 检查JSON数组中是否包含指定值

2.优点：效率高，精确匹配数组元素

3.注意：必须元素完全相等才会匹配（如"气模车"不会匹配）

2. 模糊匹配方案

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

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

1.核心函数：

• JSON_SEARCH(): 搜索JSON中匹配模式的路径
• 'one': 找到第一个匹配即停止
• '%气模%': SQL的LIKE通配符模式

2.优点：支持模糊匹配

3.注意：效率低于精确匹配，大数据量时需谨慎

参数化查询示例

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

<!-- 精确匹配 -->
<select id="findProcess" resultType="Proceswww.chinasem.cnsPrice">
  SELECT * FROM process_unit_prices
  WHERE process_name = #{processType}
    AND JSON_CONTAINS(craft_name, JSON_QUOTE(#{craft}))
  LIMIT 1
</select>

<!-- 模糊匹配 -->
<select id="findProcessFuzzy" resultType="ProcessPrice">
  SELECT * FROM process_unit_prices
  WHERE process_name = #{processType}
    AND JSON_SEARCH(craft_name, 'one', CONCAT('%', #{craft}, '%')) IS NOT NULL
  LIMIT 1
</select&China编程gt;

使用场景建议

性能优化贴士

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

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。

示例查询：

假设你的表结构如下：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_data JSON
);

你可以通过 JSON_CONTAINS() 函数来检查 JSON 字段是否包含 {user1: 1}。

SEL编程China编程ECT * 
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()，但是需要确保第二个参数符合你想要的结构。

SELECT javascript* 
FROM users 
WHERE JSON_CONTAINS(user_data, '{"user1": 1}');

4. 检查 JSON 字段中是否包含某个键

如果你不关心键值对，只想检查 JSON 字段中是否包含某个键（比如 user1），可以使用 JSON_EXTRACT() 或 JSON_UNQUOTE() 与 IS NOT NULL 来实现。

SELECT * 
FROM users 
WHERE JSON_UNQUOTE(JSON_EXTRACT(user_d编程ata, '$.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数组字段包含特定字符串的方法的文章就介绍到这了,更多相关MySQL查询JSON字段内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！