MySQL JSON字段：JSON_EXTRACT与JSON_CONTAINS

认识MySQL JSON字段与路径表达式

MySQL自5.7版本起原生支持JSON数据类型，其底层采用优化的二进制格式存储，写入时会自动进行严格的JSON语法校验，确保数据合法性。与传统的VARCHAR存储纯文本不同，JSON字段在读取时可直接解析为结构化对象或数组。理解JSON路径表达式是操作该字段的前提。路径以$符号开头，代表整个JSON文档。访问对象属性使用点号，如$.user.name；访问数组元素使用方括号加索引，如$.tags[0]；若需遍历数组所有元素，则使用通配符$[*]或$.items[*].id。路径表达式严格区分大小写，且不支持动态变量拼接。在实际建表时，通常将JSON列定义为NOT NULL并设置默认值'{}'或'[]'，以避免后续查询时出现隐式类型转换问题。掌握路径语法后，后续的数据提取与条件过滤才能精准定位目标节点。

用JSON_EXTRACT读取JSON数据

JSON_EXTRACT(json_doc, path[, path] ...)是MySQL提取JSON节点的核心函数。其基本语法为传入JSON列名与一个或多个路径表达式。例如，执行SELECT JSON_EXTRACT(info, '$.profile.age') FROM users;将返回{"age": 28}。需要注意的是，该函数返回值始终为JSON类型，外层会包裹双引号（字符串）或保留原始结构。若需直接获取纯SQL标量值（如去除引号的字符串或原生数字），应使用->>操作符或配合JSON_UNQUOTE函数，例如SELECT info->>'$.profile.age' FROM users;。对于数组提取，JSON_EXTRACT(data, '$.tags[1]')可精准获取第二个元素。在复杂查询中，可结合JSON_EXTRACT进行多路径提取，如SELECT JSON_EXTRACT(config, '$.theme', '$.version')，返回结果为包含多个键值对的JSON数组。理解返回值类型差异，能有效避免在应用层进行二次解析时出现类型不匹配错误。

用JSON_CONTAINS判断JSON是否包含内容

JSON_CONTAINS(target, candidate[, path])用于判断目标JSON文档是否包含指定的候选值，返回1（包含）或0（不包含）。其核心逻辑是完全匹配而非模糊搜索。对于对象，候选值必须包含目标对象中对应键的完整键值对；对于数组，候选值必须是目标数组的子集。例如，SELECT * FROM orders WHERE JSON_CONTAINS(items, '{"id": 101}');可筛选出包含该商品ID的订单。若需限定在特定路径下匹配，可传入第三个参数，如JSON_CONTAINS(tags, '"urgent"', '$.labels')，仅在labels数组中查找字符串urgent。在WHERE子句中使用时，需注意候选值必须符合JSON格式，字符串需加双引号，数字则无需。该函数不支持通配符，若需部分匹配应改用JSON_SEARCH。结合JSON_CONTAINS可高效实现标签过滤、权限校验等典型业务场景，大幅简化传统多表关联查询的复杂度。

对比、验证与常见避坑

JSON_EXTRACT负责取值，JSON_CONTAINS负责判断，两者职责截然不同，混用极易导致逻辑错误。常见陷阱之一是类型严格匹配：JSON中的数字25与字符串"25"在MySQL中不相等，JSON_CONTAINS会返回0，需确保候选值类型与存储一致。路径错误是另一高频问题，若路径不存在，JSON_EXTRACT返回NULL而非报错，此时若直接用于比较（如WHERE JSON_EXTRACT(...) = 'x'）会导致整行被过滤。验证查询结果时，建议先用SELECT JSON_EXTRACT(...) AS val单独查看提取值，确认结构与类型后再嵌入WHERE条件。对于数组匹配，JSON_CONTAINS要求元素顺序无关但值必须完全一致，若需判断数组是否包含任意元素，应使用JSON_OVERLAPS（MySQL 8.0.17+）。调试时可借助JSON_TYPE()函数检查节点类型，或使用JSON_VALID()验证原始数据合法性，从而快速定位异常根因。

性能与索引实践

频繁对JSON字段进行查询时，全表扫描会导致严重性能瓶颈。MySQL 5.7支持通过生成列（Generated Column）提取JSON节点并为其创建普通B-Tree索引，例如ALTER TABLE t ADD COLUMN user_id INT GENERATED ALWAYS AS (info->>'$.id') STORED;，随后对user_id建索引即可实现高效等值查询。MySQL 8.0.13起进一步支持函数索引，允许直接对JSON_EXTRACT表达式建索引，无需显式创建物理列。但需注意，JSON索引仅支持等值或范围查询，不支持前缀匹配或复杂嵌套过滤。当某JSON路径的查询频率极高且数据量庞大时，强烈建议将其拆分为独立的常规列，利用关系型数据库的成熟优化器。此外，避免在WHERE子句中对JSON列使用函数包装，这会破坏索引下推机制。合理规划存储结构，才能在灵活性与查询性能间取得平衡。