MySQL JSON_CONTAINS 详解：告别 LIKE 模糊匹配，高效查询 JSON 数组

我在做数据清洗的时候遇到过不少类似场景一张订单表里某个字段存的是 JSON 数组里面塞着用户选的 SKU、标签、渠道列表业务上需要查“包含某个值的所有记录”。很多人的第一反应是 LIKE 一把梭写个%xxx%去匹配结果边界条件踩了一堆坑。后来换了 MySQL 原生的 JSON_CONTAINS才算把这类查询从“能跑”变成了“靠谱”。这篇就专门聊聊这个函数从语法、底层逻辑到实际场景、性能优化一次讲透。1. JSON_CONTAINS 到底帮你解决了什么问题1.1 为什么 LIKE 和 FIND_IN_SET 都不够用先说一个非常典型的业务需求商品表里有个tag_list字段存储结构是 JSON 数组比如[爆款, 新品, 限时]现在要找出所有包含“新品”标签的商品。用 LIKE 的话是这样写的SELECT * FROM product WHERE tag_list LIKE %新品%;这写法看着没毛病但实际隐患很多。假设某个商品有个 tag 叫“新品尝鲜”那么LIKE %新品%也会把它捞出来这就是典型的模糊匹配误伤。而且如果 JSON 数组里的字符串包含特殊字符比如引号、反斜杠LIKE 的转义逻辑又是一堆细节。还有人会用 FIND_IN_SET但是它专为逗号分隔的字符串设计和 JSON 数组的结构并不完全兼容。JSON 数组里元素可以包含逗号比如[a,b, c]FIND_IN_SET 一遇到这种就废了。更重要的是这两个方案都忽略了一个问题JSON 是带类型的数字1和字符串1在语义上完全不同文本匹配根本无法区分这两个。JSON_CONTAINS 的价值就在于它按 JSON 文档的结构去判断包含关系而不是按“字符串子串”去匹配。它知道数组里有哪些元素知道你给的目标值是字符串还是数字也就从根上避免了误匹配。1.2 这个函数的应用边界JSON_CONTAINS 的适用场景集中在三块JSON 数组的条件过滤判断数组是否包含一个或多个元素JSON 对象的键值匹配判断一个嵌套对象里是否存在指定键值对和虚拟列、函数索引配合实现对 JSON 字段的高效查询它解决的核心痛点是在 MySQL 5.7 之后引入了 JSON 类型但很多开发者还是习惯用老办法去处理 JSON 数据结果既损失了 JSON 语义又让查询性能变得难以预判。JSON_CONTAINS 补上的正是这个缺口。适合使用 JSON_CONTAINS 的读者主要是那些在业务里已经用 JSON 字段存储半结构化数据的人比如标签系统、配置项、埋点数据、用户画像、商品属性集。如果你还在用 LIKE 搞这类查询这篇文章值得仔细看。2. 语法细节与参数解读2.1 参数结构和返回规则JSON_CONTAINS 的完整语法如下JSON_CONTAINS(target, candidate[, path])三个参数的含义target被搜索的 JSON 文档可以是 JSON 类型的列也可以是表达式candidate要查找的目标 JSON 文档可以是标量、对象或数组path可选的 JSON 路径表达式比如$.tags用于限定搜索范围返回值很简单命中返回 1未命中返回 0。但如果传入的 target 或 candidate 是非法 JSON或者 path 写错会直接报错。举个例子SELECT JSON_CONTAINS([a, b, c], b); -- 返回 1注意 candidate 的写法字符串b在 JSON 里是以b形式存储的所以传参的时候必须写成带双引号的字符串字面量。很多初学者在这一步翻车写的是JSON_CONTAINS([a, b], b)结果返回 0还以为是函数的问题。candidate 是数组也可以SELECT JSON_CONTAINS([a, b, c], [a, b]); -- 返回 1这个很好理解语义是“target 里是否存在 candidate 数组的全部元素”相当于数组子集的判断。但是如果 candidate 里有一个元素不存在整体就是 0SELECT JSON_CONTAINS([a, b, c], [a, d]); -- 返回 02.2 path 参数的用法和坑实际开发中JSON 不是总是顶层数组更多时候是嵌套对象。比如下面这个存储用户信息的 JSON{ name: 张三, tags: [老客户, 高净值], address: { city: 北京, district: 朝阳区 } }如果想判断 tags 里是否包含“高净值”就要用到 pathSELECT JSON_CONTAINS( {name: 张三, tags: [老客户, 高净值]}, 高净值, $.tags );path 的写法是标准的 JSON 路径$表示文档根$.tags表示取根对象里的 tags 字段。如果路径指向一个对象里的某个键SELECT JSON_CONTAINS( {address: {city: 北京}}, 北京, $.address.city );需要注意两点。第一path 指到的是一个对象时candidate 也要传对象path 指到数组时candidate 传数组里的元素。第二如果 path 写成了不存在的位置返回结果不是 0而是直接报错SELECT JSON_CONTAINS( {name: 张三}, 李四, $.address.city ); -- ERROR 3143: Invalid JSON path expression实际开发中我最常用的方式是先用 JSON_EXTRACT 或 JSON_KEYS 看看目标路径的结构确认无误后再写 JSON_CONTAINS 的 path。2.3 对象作为 candidate 时的行为JSON_CONTAINS 对对象的匹配规则是要求 target 中对应位置包含 candidate 对象里的所有键值对但允许 target 有额外的键。SELECT JSON_CONTAINS({name: 张三, age: 30}, {name: 张三}); -- 返回 1 SELECT JSON_CONTAINS({name: 张三, age: 30}, {name: 李四}); -- 返回 0 SELECT JSON_CONTAINS({name: 张三, age: 30}, {name: 张三, age: 30}); -- 返回 1也就是说candidate 对象是 target 对象的“子集”时结果为 1。这个规则和数组的逻辑是呼应的candidate 必须被完全包含。这里容易忽略的是嵌套场景。比如 target 是{info: {name: 张三}}如果用不带 path 的方式去查SELECT JSON_CONTAINS( {info: {name: 张三}}, {name: 张三} ); -- 返回 0原因是 target 的根节点是info对象本身candidate 的{name: 张三}和根节点对不上。这种场景必须用 path 指到$.info或者直接把 candidate 写成完整结构SELECT JSON_CONTAINS( {info: {name: 张三}}, {info: {name: 张三}} );这两个写法结果都是 1但语义完全不同。第一个是判断 info 内部结构第二个是判断整个文档完全包含。写代码前先想清楚你要查的是哪个层级。3. 实战场景标签过滤、动态属性和多条件匹配3.1 数组标签的精确匹配回到开头说的商品标签场景。建一张简化表CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), tag_list JSON ); INSERT INTO product (name, tag_list) VALUES (商品A, [爆款, 新品]), (商品B, [新品尝鲜, 限时]), (商品C, [限时, 清仓]);查询包含“新品”这个独立标签的商品SELECT id, name FROM product WHERE JSON_CONTAINS(tag_list, 新品);这条 SQL 的结果只会返回商品A不会误伤商品B因为 JSON_CONTAINS 对比的是数组里的完整元素而不是做子串匹配。这就是它和 LIKE 的本质差异。再复杂一点筛选同时包含“爆款”和“新品”两个标签的商品SELECT id, name FROM product WHERE JSON_CONTAINS(tag_list, [爆款, 新品]);这里 candidate 写成数组语义是“必须同时包含这两个元素”。如果业务需要“包含其中一个就行”那就要用 ORSELECT id, name FROM product WHERE JSON_CONTAINS(tag_list, 爆款) OR JSON_CONTAINS(tag_list, 新品);实测小数据量时这两条 SQL 性能差距不大但到几十万行的表OR 写法会让优化器更难走索引。后面性能部分再展开聊。3.2 嵌套对象的关键值查询再看一个用户画像场景每个用户有一条 profile 的 JSON包含多个维度的标签和属性{ user_id: 1001, level: VIP, preferences: { category: [数码, 家电], brands: [某品牌A, 某品牌B] } }查询偏好品牌里包含“某品牌A”的 VIP 用户SELECT user_id FROM user_profile WHERE JSON_CONTAINS(profile, {level: VIP}) AND JSON_CONTAINS(profile, 某品牌A, $.preferences.brands);注意这里我同时用了两个条件第一个验证 level第二个深入嵌套路径。这个写法很直观但要注意 JSON_CONTAINS 对同一 JSON 文档做两次扫描如果 profile 特别大、数据量特别多性能会成为瓶颈。另一种做法是直接把整个 profile 都匹配一次SELECT user_id FROM user_profile WHERE JSON_CONTAINS(profile, { level: VIP, preferences: { brands: [某品牌A] } });这个写法看起来一步到位实际上有一个大坑JSON 对象的包含判断要求 candidate 里的数组必须完整匹配 target 数组内容。也就是说如果某用户 brands 字段是[某品牌A, 某品牌B]candidate 写成[某品牌A]是不行的因为数组不是对象不存在“子集”的宽松匹配规则。除非业务能保证 brands 只有一个元素否则这个写法会漏数据。所以实际线上我推荐用多条件 AND 方案虽然多扫一次但语义清晰也不会因为数组长度变化而漏查。3.3 和 JSON_EXTRACT、JSON_TYPE 的联合作战JSON_CONTAINS 有时候不是单独用的而是和其他 JSON 函数配合先做结构判断再做值提取。比如判断一个 JSON 字段里某个数组非空且包含特定值时SELECT user_id FROM user_profile WHERE JSON_CONTAINS(profile, 某品牌A, $.preferences.brands) AND JSON_EXTRACT(profile, $.preferences.brands) IS NOT NULL;第二个条件其实是防御性的防止preferences或brands键不存在导致 JSON_CONTAINS 报错。一般来说如果 JSON 结构是程序写入且固定的可以不写这个防御条件但如果是历史数据或者第三方导入的数据我建议加上线上排查问题会省很多事。还可以用 JSON_TYPE 判断字段类型因为一个字段虽然名为 brands但历史数据里可能存的是字符串而不是数组SELECT user_id FROM user_profile WHERE JSON_TYPE(JSON_EXTRACT(profile, $.preferences.brands)) ARRAY AND JSON_CONTAINS(profile, 某品牌A, $.preferences.brands);这类写法适合做数据质量检查时用不太适合直接放到高频查询路径里原因还是性能。JSON_TYPE 是全文档解析开销不小。4. 性能分析从全表扫描到函数索引4.1 为什么 JSON_CONTAINS 默认不走索引MySQL 5.7 引入 JSON 类型后一个常见误解是JSON 字段也可以建索引。其实 MySQL 的普通索引无法直接建立在 JSON 列上原因是 JSON 是文档型结构内部数据经过了二进制格式化和压缩不像 B 树的 key 那样可以按规则排序和比较。所以一条带有 JSON_CONTAINS 的 WHERE 条件默认情况下的执行计划就是全表扫描对每一行做 JSON 解析和匹配。数据量超过十万行时这个开销非常可观。我之前在一个模拟项目X里面做过压测一张 20 万行的表JSON 字段平均大小约 1KB一条 JSON_CONTAINS 查询的响应时间在 200ms 到 500ms 之间浮动而相同条件的普通 B 树索引查询只需要几毫秒。差距就是这么明显。4.2 虚拟列 函数索引的正确打法既然 JSON 列本身不能直接建索引业界通用做法是使用生成列Generated Column配合函数索引来加速。思路是这样的从 JSON 字段里把你要查的值提取成一个虚拟列然后在虚拟列上建普通索引。MySQL 对表达式的值是有缓存逻辑的插入和更新时自动计算之后查询就能走索引。建表语句如下CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), tag_list JSON, first_tag VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(tag_list, $[0]))) STORED, INDEX idx_first_tag (first_tag) );这里把tag_list数组的第一个元素提取到first_tag列并加上 STORED 关键字让虚拟列持久化存储。然后查询SELECT id, name FROM product WHERE first_tag 新品;这是对“提取固定位置元素”场景的优化。但更常见的 JSON_CONTAINS 判断是“数组里是否存在某个元素”这个用虚拟列建索引的思路要变一下。比如你要经常查 tag_list 是否包含“爆款”可以单独建一个生成列ALTER TABLE product ADD COLUMN has_burst VARCHAR(20) AS (CASE WHEN JSON_CONTAINS(tag_list, 爆款) THEN yes ELSE no END) STORED, ADD INDEX idx_has_burst (has_burst);然后查询就退化成等值比较SELECT id, name FROM product WHERE has_burst yes;这个方法非常有效但代价是业务需求一旦变化比如要查“新品”就得再加一个生成列。所以它适合标签集合相对固定、查询模式比较明确的场景不适合那种随意筛选标签的 BI 报表环境。4.3 大数据量下的替代方案当数据量上升到百万级甚至更高JSON_CONTAINS 搭配虚拟列仍然有限制因为每个标签都要建一列根本不可维护。这时候我会考虑更务实的替代方案。一是把高频查询的标签拆出来单独建关联表。比如 product_tag 表每行是 product_id 和 tag 两个字段tag 列建普通索引查询变成 JOIN 或 IN 子查询性能完全是常规索引能力范围。二是使用全文索引。如果 JSON 里的标签是短文本而且允许一定的模糊匹配损耗可以建全文索引用 MATCH AGAINST 查询性能比 JSON_CONTAINS 强很多。三是在应用层做缓存。对于标签筛选这类查询条件相对固定、结果集更新不频繁的场景可以用缓存维护筛选结果集合业务侧直接读缓存数据库只承担写入和兜底。这三个方案的取舍没有标准答案核心看你的查询频率、数据一致性要求、以及团队维护成本。在我的实践里查询频率高的核心链路建议走关联表分析类的临时场景保留 JSON_CONTAINS 完全没问题。5. 常见问题与排查技巧5.1 数字类型匹配总是返回 0这是一个我见过的最高频问题。JSON 里存的数字是1查询时写SELECT JSON_CONTAINS([1, 2, 3], 1); -- 返回 0看上去没毛病实际返回 0。原因是 JSON_CONTAINS 的 candidate 参数需要符合 JSON 文档规范裸数字1不是合法的 JSON 文档它实际上被当作字符串处理了。正确的写法是SELECT JSON_CONTAINS([1, 2, 3], 1); -- 错误 SELECT JSON_CONTAINS([1, 2, 3], 1); -- 正确第二行的1用双引号包起来是一个 JSON 字符串字面量。如果 target 中的元素是 JSON 数字那么 candidate 要写成SELECT JSON_CONTAINS([1, 2, 3], 1);JSON 数字可以用1或者1.0表示。这里有个细节JSON_CONTAINS 在比较时会区分整数和浮点数吗实测下来MySQL 对1和1.0的判断是相等的。不过保险起见建议在代码层统一数字格式不要让它依赖 MySQL 的隐式处理。5.2 NULL 和非法 JSON 引发的连锁问题如果 target 列本身是 NULLJSON_CONTAINS 的返回值不是 0而是 NULL。这在 WHERE 条件里不会报错但配合 NOT 使用时会出问题。比如你要查“不包含某标签”的商品SELECT id, name FROM product WHERE NOT JSON_CONTAINS(tag_list, 新品);如果有一行 tag_list 是 NULL那么 JSON_CONTAINS 返回 NULLNOT NULL 仍是 NULLWHERE 条件不成立这行数据就被过滤掉了。从业务语义上看“没有标签的商品的应该算不包含新品”但现在它没有被返回。解决办法是显式处理 NULLSELECT id, name FROM product WHERE tag_list IS NULL OR NOT JSON_CONTAINS(tag_list, 新品);另一个问题是非法 JSON。JSON 类型的列本身有合法性校验理论上不会存非法 JSON但如果是从 TEXT 或 VARCHAR 字段强转过来的历史数据就可能存在非 JSON 内容。JSON_CONTAINS 遇到这种数据会直接报错整条 SQL 失败。排查方法很简单先把疑似有问题的行筛出来SELECT id, tag_list FROM product WHERE JSON_VALID(tag_list) 0;5.3 关于路径和转义的几个高频翻车点路径中的键如果含特殊字符比如空格、汉字、引号一般建议用双引号包裹键名SELECT JSON_CONTAINS( {用户 标签: [A, B]}, A, $.用户 标签 );注意$.用户 标签这个写法键名用双引号包裹。不加引号在某些版本下会报路径错误。还有一种情况是 JSON 字符串里本身包含双引号比如标签值是a\b。在 SQL 里写 candidate 时需要用反斜杠转义还要处理 SQL 本身的引号转义规则多层嵌套下来非常容易写错。我的建议是遇到这类场景先单独跑一条 SELECT 把 JSON 内容打印出来确认目标值的原始结构再用 JSON_QUOTE 函数辅助生成合法 JSON 字符串字面量SELECT JSON_CONTAINS( [a\b], JSON_QUOTE(ab) );JSON_QUOTE 能帮你在运行时生成正确的 JSON 字符串格式避免手写转义。6. 其他 JSON 条件函数的替代和对比JSON_CONTAINS 不是唯一的 JSON 条件查询工具实际开发中还要根据场景区分 JSON_CONTAINS、JSON_OVERLAPS 和 JSON_SEARCH 的取舍。MySQL 8.0.17 之后的 JSON_OVERLAPS 是“两个 JSON 文档有交集”就返回 1它和 JSON_CONTAINS 的区别很简单JSON_CONTAINS 要求 candidate 完全被包含JSON_OVERLAPS 只要有一个公共元素就算命中。SELECT JSON_CONTAINS([a, b], [b, c]); -- 返回 0因为 c 不在 target 中 SELECT JSON_OVERLAPS([a, b], [b, c]); -- 返回 1因为 b 是公共元素业务含义就是“同时包含”和“包含任一”的区别。JSON_SEARCH 则是返回匹配字符串在 JSON 文档中的路径它的重点是“找位置”不是“判断是否包含”。如果只是条件过滤JSON_SEARCH 不是最佳选择因为它的语义更接近模糊搜索性能也不占优势。按场景给一个直接的选型建议场景推荐函数数组是否包含某个元素JSON_CONTAINS数组是否包含多个元素JSON_CONTAINS数组是否和目标有交集JSON_OVERLAPS查找包含某值的路径JSON_SEARCH对象中是否存在键值对JSON_CONTAINS提取值后再做数值比较JSON_EXTRACT选函数的核心逻辑是先确认查询的语义到底要“完全包含”还是“存在交集”选错函数会导致数据漏选或多选比性能问题更棘手。7. 除了查询JSON_CONTAINS 还能用在哪些地方JSON_CONTAINS 不只是 WHERE 条件里的工具它还能放在 SELECT 列表、JOIN 条件、CASE WHEN 表达式里用起来比想象中灵活。7.1 用布尔标记实现复杂筛选结果有的业务需要把满足条件的记录打标但不想真正过滤掉。比如统计每个商品是否属于“爆款标签分组”SELECT id, name, CASE WHEN JSON_CONTAINS(tag_list, 爆款) THEN 爆款 WHEN JSON_CONTAINS(tag_list, 新品) THEN 新品 ELSE 普通 END AS tag_group FROM product;这种情况下 JSON_CONTAINS 作为表达式参与计算和普通 WHERE 条件用法不同。如果你要把多个标签匹配结果拼成一组也能在 SELECT 里做布尔运算只是注意 JSON_CONTAINS 返回值不是严格的 0/1而是可能为 NULL遇到 NULL 时 CASE 分支不会命中。7.2 JOIN 条件里的点匹配表 A 的某个字段存的是 JSON 数组表 B 存的是具体值要找出所有关联关系SELECT A.id, B.name FROM table_a A JOIN table_b B ON JSON_CONTAINS(A.tags, JSON_QUOTE(B.tag_name));这个写法把 B 的字段值先转成合法的 JSON 字符串再和 A 的 JSON 数组做匹配。从可用性角度讲它避免了动态拼接 SQL 的麻烦但在大数据量表 JOIN 时性能会比较差因为无法走索引。一般用在关联表数据量小、或一次性数据处理的场景。7.3 需要注意的是它不是判断“键是否存在”的工具如果你想判断 JSON 对象里是否包含某个键JSON_CONTAINS 并不是一个好选择应该用 JSON_KEYS 或 JSON_EXTRACT 结合 IS NOT NULL 判断。比如判断 profile 里是否有 age 字段SELECT JSON_EXTRACT(profile, $.age) IS NOT NULL FROM user_profile;但这里也有坑如果 age 的值本身就是 null那么 JSON_EXTRACT 返回 JSON nullIS NOT NULL 结果会是 false这就有歧义。更准确的写法是配合 JSON_CONTAINS_PATHSELECT JSON_CONTAINS_PATH(profile, one, $.age);JSON_CONTAINS_PATH 专门用来判断路径是否存在语义精确。JSON_CONTAINS 解决的是“值到底有没有”的问题JSON_CONTAINS_PATH 解决的是“键到底有没有”的问题两者分工不同。8. 踩过几次坑之后的操作习惯我用了几年 JSON_CONTAINS踩过的坑不少总结几个已经固化的操作习惯。第一个习惯写任何 JSON 查询前先单独验证 JSON 文档本身的结构和值类型。用一个最小化的 SELECT 确认无误后再放到业务查询里。比如先在控制台跑SELECT JSON_EXTRACT(tag_list, $) FROM product LIMIT 5; SELECT JSON_TYPE(JSON_EXTRACT(tag_list, $[0])) FROM product LIMIT 5;确认数组元素类型是字符串还是数字再写 candidate。这一步能筛掉大多数“明明有值却查不出来”的问题。第二个习惯对历史数据或者外部导入数据永远先做一次 JSON_VALID 检测。老表从 VARCHAR 迁移到 JSON 类型时数据质量往往参差不齐直接上线 JSON_CONTAINS 查询很可能在运行时炸出无效 JSON 的报错。第三个习惯构造复杂 JSON 条件时尽量把 JSON 的字符串字面量交给函数生成比如 JSON_QUOTE 和 JSON_ARRAY而不是手写。手写多层引号转义的 SQL 极易出错尤其在代码里嵌又一层语言变量时双重转义让人崩溃。我的最后一个经验是JSON_CONTAINS 适合灵活性高、数据量中等的场景。如果你的数据量已经大到需要给它做各种索引优化和拆表说明 JSON 不是最适合这种查询的结构早一步拆出来建关系表反而省事。做数据模型选型时不要把 JSON 列当成万能选项它解决的是“结构不固定”的问题代价就是查询和关联能力的削弱。如果你正被 JSON 查询折腾得头疼不妨想一下你的业务到底是查询固定字段多还是结构变化多。前者用关系表更清爽后者用 JSON_CONTAINS 这类函数恰到好处。做对了取舍后面能省下不少维护成本。