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.核心函数：

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

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

2. 模糊匹配方案

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

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

1.核心函数：

2.优点：支持模糊匹配

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

参数化查询示例

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

<!-- 精确匹配 -->
<select id="findProcess" resultType="ProcessPrice">
  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>

使用场景建议

性能优化贴士

索引优化：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}。

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

这条 SQL 语句会返回所有 user_data 字段包含 {user1: 1} 键值对的记录。

2. JSON_CONTAINS() 函数的使用细节

3. 查询部分匹配

如果你只想检查 JSON 字段中是否存在某个键（例如 user1）并且该键的值是 1，而不关心 JSON 字段中的其他内容，你仍然可以使用 JSON_CONTAINS()，但是需要确保第二个参数符合你想要的结构。

SELECT * 
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_data, '$.user1')) = '1';

小结

使用 JSON_CONTAINS() 是检查 JSON 数据是否包含某个完整键值对的简便方法。如果你需要更细粒度的查询（如只关心键是否存在且其值为特定值），可以结合 JSON_EXTRACT() 和 JSON_UNQUOTE() 进行操作。

到此这篇关于MySQL查询JSON数组字段包含特定字符串的方法的文章就介绍到这了,