MySQL JSON_CONTAINS() 用法与实例

在现代 Web 应用开发中，JSON 格式数据的使用越来越普遍。MySQL 从 5.7 版本开始提供了对 JSON 数据类型的原生支持，其中 JSON_CONTAINS() 函数是我们处理 JSON 数据时的一个得力助手。这个函数专门用来检查 JSON 文档中是否包含特定的值或结构，就像是在 JSON 数据海洋中的探测器，帮助我们快速定位需要的信息。

JSON_CONTAINS 函数的基本原理

JSON_CONTAINS() 函数的基本语法如下：

JSON_CONTAINS(target_json, candidate_json[, path])

这个函数有三个参数：

target_json：要搜索的目标 JSON 文档
candidate_json：要查找的候选 JSON 值或结构
path（可选）：指定在目标 JSON 中搜索的路径

函数返回值为 1（true）或 0（false），表示是否找到匹配项。如果任一参数为 NULL 或路径不存在，则返回 NULL。

举个简单的例子：

SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a');

这个查询会返回 1，因为在路径 $.a 处找到了值 1。

检查简单值的包含关系

我们先从最基本的用法开始，检查 JSON 中是否包含某个简单值。

检查数组中是否包含某个数值：

SELECT JSON_CONTAINS('[1, 2, 3]', '2');

返回 1，因为数字 2 存在于数组中

检查对象中是否包含特定键值对：

SELECT JSON_CONTAINS('{"name": "John", "age": 30}', '30', '$.age');

返回 1，因为在 age 路径处找到了值 30

检查字符串是否存在于数组中：

SELECT JSON_CONTAINS('["apple", "banana", "orange"]', '"banana"');

注意字符串值需要用双引号包裹

处理复杂 JSON 结构

JSON_CONTAINS() 的强大之处在于它能处理复杂的 JSON 结构，包括嵌套的对象和数组。

检查嵌套对象中的值：

SELECT JSON_CONTAINS(
    '{"user": {"name": "Alice", "preferences": ["reading", "hiking"]}}',
    '"hiking"',
    '$.user.preferences'
);

返回 1，因为找到了 hiking 这个爱好

检查整个子结构的包含关系：

SELECT JSON_CONTAINS(
    '{"department": "IT", "team": ["John", "Mary", "Bob"]}',
    '["Mary", "Bob"]',
    '$.team'
);

返回 0，因为数组不是完全包含（缺少 John）

使用路径表达式精准定位

路径表达式让我们可以精确指定在 JSON 的哪个部分进行搜索，这是 JSON_CONTAINS() 最灵活的特性之一。

使用通配符检查所有数组元素：

SELECT JSON_CONTAINS(
    '{"grades": [{"math": 90}, {"math": 85}, {"math": 92}]}',
    '90',
    '$.grades[*].math'
);

检查所有成绩中是否有数学得 90 分的

检查特定数组成员的属性：

SELECT JSON_CONTAINS(
    '[{"id": 1, "value": "A"}, {"id": 2, "value": "B"}]',
    '"B"',
    '$[1].value'
);

明确检查第二个元素的 value 属性

实际应用案例

让我们看几个 JSON_CONTAINS() 在实际场景中的应用示例。

用户标签系统查询：

SELECT user_id
FROM user_profiles
WHERE JSON_CONTAINS(tags, '"premium"');

找出所有带有 premium 标签的用户

产品特性筛选：

SELECT product_name
FROM products
WHERE JSON_CONTAINS(specifications, '{"color": "red"}');

找出所有颜色为红色的产品

多条件组合查询：

SELECT *
FROM events
WHERE JSON_CONTAINS(participants, '{"role": "speaker"}')
AND JSON_CONTAINS(metadata, '"conference"', '$.type');

找出会议类型活动中包含演讲者的事件

性能优化与注意事项

虽然 JSON_CONTAINS() 非常方便，但在大数据量使用时需要注意性能问题。

创建函数索引提高查询效率（MySQL 8.0+）：

ALTER TABLE products
ADD INDEX idx_spec_color ((CAST(specifications->'$.color' AS CHAR(20))));

避免在 JSON 文档过大时使用，考虑规范化数据结构：

-- 不推荐：在大型 JSON 上频繁查询
SELECT * FROM logs WHERE JSON_CONTAINS(metadata, '"error"');

-- 更好：将重要字段提取为单独列
ALTER TABLE logs ADD COLUMN has_error BOOLEAN
GENERATED ALWAYS AS (JSON_CONTAINS(metadata, '"error"')) STORED;

注意 NULL 值的处理差异：

-- 这两种情况的结果不同
SELECT JSON_CONTAINS('{"a": null}', 'null', '$.a');  -- 返回 1
SELECT JSON_CONTAINS('{"a": null}', 'NULL', '$.a');  -- 返回 0（字符串 NULL）

总结

JSON_CONTAINS() 是 MySQL 处理 JSON 数据时不可或缺的工具，它提供了灵活而强大的方式来查询 JSON 文档中的内容。无论是简单的值检查，还是复杂的结构匹配，这个函数都能胜任。在实际应用中，合理使用路径表达式可以大幅提高查询的精确度，而在性能敏感的场景中，则需要考虑添加适当的索引或调整数据结构。随着 JSON 在现代应用中的广泛使用，掌握 JSON_CONTAINS() 的各种技巧将成为数据库开发中的一项重要技能。记住，虽然 JSON 提供了灵活性，但过度使用可能会影响性能，在关键查询上还是要权衡是否需要将数据规范化。