1. 从“存”到“查”JSON数组在MySQL中的价值与挑战几年前当我们的业务系统需要为一个用户打上“技术宅”、“电影爱好者”、“咖啡控”等多个标签时常见的做法是设计一张用户标签关联表或者干脆用逗号分隔的字符串存到一个字段里。前者关联查询复杂后者则完全丧失了数据的结构化查询能力。直到MySQL 5.7版本引入了对JSON数据类型的原生支持这个问题才有了一个优雅的解决方案我们可以直接把[技术宅, 电影爱好者, 咖啡控]这样一个标签数组以JSON格式存进一个字段里。这不仅仅是存储形式的改变它意味着我们可以在数据库层面直接对这个数组进行查询和操作比如高效地找出所有带有“咖啡控”标签的用户。然而便利的背后是新的挑战。当你面对一个存储了成千上万条JSON数据的表需要快速判断某条记录的JSON数组是否包含特定元素时传统的LIKE ‘%咖啡控%’模糊匹配不仅效率低下而且极易出错比如误匹配“手工咖啡控”。这正是JSON_CONTAINS()函数大显身手的地方。它专为这种场景设计能精准、高效地完成数组包含性判断。但用好它远不止于知道函数名那么简单。从理解JSON数据的存储细节到函数在不同场景下的调用方式再到如何与GORM这样的ORM框架结合每一步都有门道。这篇文章我就结合自己多次在项目中处理MySQL JSON数据的实战经验来彻底讲清楚“判断JSON数组是否包含某元素”这件事。2. 基石深入理解MySQL中的JSON数据类型在深入函数使用之前我们必须先打好地基理解MySQL是如何对待JSON数据的。这绝非简单的字符串存储理解这一点能帮你避开很多坑。2.1 JSON类型的本质与存储优化当你将一个字段定义为JSON类型时MySQL并不会把它当作普通的TEXT或VARCHAR来对待。服务器会对插入的JSON字符串进行严格的有效性校验无效的JSON文档会被拒绝。校验通过后MySQL会以一种内部优化的二进制格式JSON类型来存储这份数据这种格式允许快速读取文档元素。当你查询时服务器无需再从头解析文本而是可以直接从二进制结构中获取值这比操作TEXT类型的JSON字符串要快得多。一个关键特性是在存储时JSON文档中的键会被排序重复的键会被去重只保留最后一个键值对。这意味着{a: 1, b: 2, a: 3}存入后再取出会变成{a: 3, b: 2}。对于数组元素的顺序是会被保留的。2.2 定义与基础操作创建一个包含JSON字段的表很简单CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), -- 定义一个名为 tags 的JSON类型字段用于存储标签数组 tags JSON, attributes JSON COMMENT ‘存储用户其他属性如偏好设置’ );插入数据时必须确保是有效的JSON格式-- 正确插入一个JSON数组 INSERT INTO users (username, tags) VALUES (‘张三’, ‘[“技术”, “阅读”, “音乐”]‘); -- 正确插入一个JSON对象 INSERT INTO users (username, attributes) VALUES (‘李四’, ‘{“theme”: “dark”, “notifications”: true}’); -- 错误无效的JSON格式会报错 INSERT INTO users (username, tags) VALUES (‘王五’, ‘[技术, 阅读]’); -- 缺少双引号查询时可以使用-和-操作符来提取JSON中的部分内容。-返回的是JSON类型而-返回的是UTF8字符串类型这在后续比较中至关重要。-- 提取tags字段整个JSON值 SELECT tags FROM users; -- 使用 - 提取返回JSON类型例如 ‘“技术”‘ SELECT tags-’$[0]‘ FROM users; -- 提取数组第一个元素 -- 使用 - 提取返回字符串类型例如 ‘技术’ SELECT tags-’$[0]‘ FROM users;这里出现的‘$[0]’是JSON路径表达式$代表文档本身[0]表示数组的第一个索引从0开始。3. 核心利器JSON_CONTAINS函数全解JSON_CONTAINS(target, candidate[, path])是解决我们核心问题的函数。它的作用是判断一个targetJSON文档中是否包含指定的candidateJSON值。如果指定了path则判断在target文档的该路径下是否包含candidate。3.1 函数语法与返回值target 被搜索的JSON文档列或JSON值。candidate 要寻找的JSON值。path可选 在target中搜索的路径。返回值 返回1true表示包含0false表示不包含。如果任何参数为NULL或target/path不是有效的JSON文档/路径则返回NULL。3.2 基础用法判断数组是否包含某个值这是最直接的场景。假设我们要找出所有喜欢“音乐”的用户。SELECT * FROM users WHERE JSON_CONTAINS(tags, ‘“音乐”’);这里有一个极其重要的细节第二个参数‘“音乐”’是一个JSON字符串值带有双引号而不是普通的SQL字符串‘音乐’。因为函数判断的是JSON值的包含关系所以candidate也必须是一个有效的JSON值。数字、布尔值、null同理-- 查找标签包含数字 10 的用户 (假设tags是 [1, 10, 20]) WHERE JSON_CONTAINS(tags, ’10’); -- 查找属性中包含 true 的用户 (假设attributes是 {“active”: true}) WHERE JSON_CONTAINS(attributes, ‘true’, ‘$.active’);3.3 进阶用法使用路径(path)进行精准搜索path参数赋予了函数更强的灵活性。例如我们的attributes字段存储了一个复杂对象{ “preferences”: { “hobbies”: [“骑行”, “摄影”], “level”: “advanced” } }如果我们想查找hobbies数组里包含“摄影”的用户就需要指定路径SELECT * FROM users WHERE JSON_CONTAINS(attributes, ‘“摄影”’, ‘$.preferences.hobbies’);路径‘$.preferences.hobbies’指向了attributes对象内部的hobbies数组。3.4 判断数组是否包含另一个数组子集关系JSON_CONTAINS还可以判断一个数组是否包含另一个数组的所有元素即是否为超集。例如查找标签同时包含“技术”和“阅读”的用户SELECT * FROM users WHERE JSON_CONTAINS(tags, ‘[“技术”, “阅读”]’);这条查询会返回tags字段为[“技术”, “阅读”, “音乐”]或[“阅读”, “技术”]的记录但不会返回只有[“技术”]或[“阅读”]的记录。这里顺序不重要它检查的是元素集合的包含关系。3.5 性能考量与索引使用在JSON字段上使用JSON_CONTAINS进行查询其性能取决于数据量和查询模式。全表扫描是常见的执行方式因为函数计算需要解析每一行的JSON值。为了提升查询性能MySQL支持在JSON列上创建函数索引也称为生成列索引。思路是将一个JSON路径下的值提取出来存储在一个生成的列上然后为该列创建索引。例如如果我们频繁需要根据attributes-’$.preferences.level’进行查询或包含性判断可以这样做-- 1. 添加一个存储级别的生成列 ALTER TABLE users ADD COLUMN preference_level VARCHAR(20) GENERATED ALWAYS AS (attributes-’$.preferences.level’) STORED; -- 2. 为该生成列创建索引 CREATE INDEX idx_level ON users(preference_level); -- 3. 查询时直接使用生成列速度极快 SELECT * FROM users WHERE preference_level ‘advanced’;对于数组包含查询虽然不能直接为整个数组条件创建完美索引但如果你经常查找包含某个特定、固定值的记录也可以为该值的提取结果创建索引尽管这需要更多存储空间。更常见的优化是重新审视数据模型如果某些JSON键值对查询极其频繁或许它们应该被设计成单独的标量列。4. 避坑指南JSON_CONTAINS实战中的常见问题在实际开发中直接使用JSON_CONTAINS可能会遇到一些意想不到的问题我在这里把几个典型的坑列出来。4.1 类型匹配陷阱字符串与数字JSON是类型敏感的。数字10和字符串“10”在JSON中是两种完全不同的值。-- 假设 tags 字段存储的是 [5, 10, 15] SELECT JSON_CONTAINS(‘[5, 10, 15]’, ‘“10”’); -- 返回 0 (false) SELECT JSON_CONTAINS(‘[5, 10, 15]’, ’10’); -- 返回 1 (true) -- 假设 tags 字段存储的是 [“5”, “10”, “15”] SELECT JSON_CONTAINS(‘[“5”, “10”, “15”]’, ’10’); -- 返回 0 (false) SELECT JSON_CONTAINS(‘[“5”, “10”, “15”]’, ‘“10”’); -- 返回 1 (true)踩坑点从应用层如Go、PHP传递数据时很容易忽略类型的序列化。确保你传递的candidate值的类型与JSON数组中存储的类型完全一致。4.2 路径表达式错误与NULL处理如果指定的path在targetJSON文档中不存在JSON_CONTAINS会返回NULL而不是0。这在布尔逻辑中可能导致非预期的结果。-- 假设 attributes 没有 ‘preferences.hobbies’ 路径 SELECT JSON_CONTAINS(attributes, ‘“摄影”’, ‘$.preferences.hobbies’) FROM users; -- 可能返回 NULL在WHERE子句中NULL会被视为FALSE但如果你在SELECT列表中使用并与其它值运算就需要用IFNULL或COALESCE函数处理SELECT IFNULL(JSON_CONTAINS(attributes, ‘“摄影”’, ‘$.preferences.hobbies’), 0) AS has_hobby FROM users;4.3 大小写敏感性与排序规则字符串的比较是否区分大小写取决于MySQL的排序规则Collation。对于JSON中的字符串比较通常遵循utf8mb4_bin排序规则这意味着是区分大小写的。SELECT JSON_CONTAINS(‘[“Apple”, “Banana”]’, ‘“apple”’); -- 返回 0 (false)如果你的业务需要不区分大小写有几种处理方式应用层统一在存入和查询前统一转为小写或大写。使用JSON_SEARCH函数JSON_SEARCH可以指定‘i’参数进行不区分大小写的搜索但它的用法和返回结果与JSON_CONTAINS不同。生成列索引创建一个存储小写版本的生成列并建立索引查询时也用小写值。5. 在GORM中优雅地使用JSON_CONTAINS在Go语言中GORM是广泛使用的ORM框架。从GORM v1.20.0开始通过gorm.io/datatypes包提供了对JSON类型的原生支持这让操作变得非常方便。5.1 定义模型与JSON字段首先你需要引入datatypes包并在模型结构体中使用datatypes.JSON类型。import ( “gorm.io/gorm” “gorm.io/datatypes” ) type User struct { gorm.Model Username string Tags datatypes.JSON // 对应MySQL的JSON类型 Attributes datatypes.JSON }GORM会自动在迁移时将datatypes.JSON字段映射为数据库中的JSON类型。5.2 使用GORM进行JSON_CONTAINS查询GORM的Where方法允许你直接写入原生SQL片段。这是执行JSON_CONTAINS查询最直接的方式。// 查找tags包含“音乐”的用户 var users []User searchTag : “音乐” // 注意必须是JSON字符串格式 db.Where(“JSON_CONTAINS(tags, ?)“, searchTag).Find(users) // 查找attributes中preferences.hobbies包含“摄影”的用户 searchHobby : “摄影” path : “$.preferences.hobbies” db.Where(“JSON_CONTAINS(attributes, ?, ?)“, searchHobby, path).Find(users)关键点传递给JSON_CONTAINS的查询值如searchTag必须是有效的JSON字符串表示。这意味着字符串值需要自带双引号。一个常见的做法是使用Go的json.Marshalimport “encoding/json” tag : “音乐” jsonTag, _ : json.Marshal(tag) // jsonTag 是 []byte(“音乐”) db.Where(“JSON_CONTAINS(tags, ?)“, string(jsonTag)).Find(users)5.3 封装与最佳实践为了避免每次查询都手动处理JSON序列化可以封装一个辅助函数。func JSONContainsQuery(column string, value interface{}) string { // 根据value类型决定是否添加路径参数占位符 // 这里简化处理假设value是基础类型将其序列化为JSON字符串 // 实际应用中可能需要更复杂的逻辑来处理路径和数组 b, _ : json.Marshal(value) return fmt.Sprintf(“JSON_CONTAINS(%s, ‘%s’)“, column, string(b)) } // 注意上述简单实现有SQL注入风险仅作思路演示。应使用参数化查询。更安全的做法是结合GORM的Clauseimport “gorm.io/gorm/clause” func (u *User) FindByTag(db *gorm.DB, tag string) ([]User, error) { var users []User jsonTag, _ : json.Marshal(tag) err : db.Where( clause.Expr{SQL: “JSON_CONTAINS(tags, ?)“, Vars: []interface{}{string(jsonTag)}}, ).Find(users).Error return users, err }5.4 处理复杂JSON结构查询当查询条件基于复杂的JSON对象时构建查询条件需要格外小心。例如查询attributes中preferences.level为“advanced”且preferences.hobbies包含“摄影”的用户。level : “advanced” hobby : “摄影” jsonLevel, _ : json.Marshal(level) jsonHobby, _ : json.Marshal(hobby) db.Where(“JSON_CONTAINS(attributes, ?, ‘$.preferences.level’) AND JSON_CONTAINS(attributes, ?, ‘$.preferences.hobbies’)“, string(jsonLevel), string(jsonHobby)).Find(users)对于这种复杂查询务必先通过EXPLAIN分析执行计划评估性能。如果这是高频查询强烈建议使用前面提到的生成列索引进行优化。6. 替代方案与场景化选择JSON_CONTAINS并非唯一选择在某些场景下其他函数或设计可能更合适。6.1 JSON_OVERLAPS判断是否有交集MySQL 8.0.17引入了JSON_OVERLAPS()函数它用于判断两个JSON文档是否有交集对于数组即是否有共同元素。它比JSON_CONTAINS更宽松。-- 查找标签与 [“技术”, “运动”] 有交集的用户 -- 即用户标签中至少包含“技术”或“运动”中的一个 SELECT * FROM users WHERE JSON_OVERLAPS(tags, ‘[“技术”, “运动”]’);这对于“或”逻辑的标签筛选非常有用。6.2 JSON_SEARCH查找值路径JSON_SEARCH(json_doc, ‘one|all’, search_str[, escape_char[, path] …])用于在JSON文档中查找指定字符串的路径。它支持通配符%和_并且可以通过‘i’参数进行不区分大小写搜索。-- 查找tags中任意位置包含“技”字的用户模糊匹配 SELECT * FROM users WHERE JSON_SEARCH(tags, ‘one’, ‘%技%’) IS NOT NULL;JSON_SEARCH返回的是路径字符串如‘$[0]’而JSON_CONTAINS返回的是布尔值。前者用于“找到在哪”后者用于“判断是否存在”。6.3 关系型设计 vs. JSON设计这是最根本的抉择。使用JSON字段的便利性是以牺牲部分关系型数据库的特性为代价的例如查询性能对JSON内部字段的查询通常不如对独立索引列的查询快。数据约束很难在JSON字段内的某个键上定义外键约束或NOT NULL约束。模式演进虽然灵活但缺乏明确的模式定义可能导致数据不一致。何时使用JSON字段是合理的半结构化或可变属性例如用户的个性化设置、产品的附加属性这些字段在不同行之间差异很大且不参与核心关系关联和复杂过滤。日志或审计数据存储原始的事件详情或变更快照。临时或低频查询数据数据主要用于存储和按主键或简单条件读取内部结构的复杂查询很少。何时应该坚持传统关系设计需要高频、高效过滤、排序或聚合的字段。需要强制数据完整性和外键关联的字段。该字段是业务实体的核心属性。在我的经验里一个实用的折中方案是将高度结构化、查询频繁的核心属性作为标量列将可变的、查询模式灵活的扩展属性放入一个JSON字段。例如用户表的email、name作为标量列而preferences主题、时区、通知开关作为JSON字段。7. 性能优化实战从慢查询到毫秒响应我曾经维护过一个用户画像系统其中用户兴趣标签就存储在JSON数组中。最初针对特定标签的查询响应时间在数据量达到百万级后慢至数秒。通过一系列优化最终将查询稳定在毫秒级。以下是核心的优化步骤。7.1 第一步诊断与EXPLAIN分析首先对慢查询使用EXPLAIN或EXPLAIN ANALYZEMySQL 8.0.18进行分析。EXPLAIN FORMATJSON SELECT * FROM users WHERE JSON_CONTAINS(tags, ‘“音乐”’);查看输出重点关注type 大概率是ALL表示全表扫描。key 为NULL表示没有使用索引。rows 预估扫描行数如果接近表总行数证实了全表扫描。7.2 第二步引入生成列与索引我们的标签查询模式相对固定主要是查询是否包含几个热门标签。我们为最常查询的5个标签如“音乐”、“阅读”、“技术”、“运动”、“美食”分别创建了虚拟生成列和索引。-- 为“音乐”标签创建虚拟列和索引 ALTER TABLE users ADD COLUMN tag_music BOOLEAN GENERATED ALWAYS AS (JSON_CONTAINS(tags, ‘“音乐”’)) VIRTUAL; CREATE INDEX idx_tag_music ON users(tag_music); -- 查询时直接使用生成列 SELECT * FROM users WHERE tag_music TRUE;为什么用VIRTUAL而不是STORED虚拟列不占用存储空间其值在读取时计算。对于JSON_CONTAINS这种确定性函数创建虚拟列索引是允许且高效的。这比STORED列节省了大量磁盘空间尤其当这类列很多时。7.3 第三步查询重写与应用层缓存将SQL查询从函数调用改为对索引列的等值匹配是性能提升的关键。同时在应用层如Redis对热门标签的查询结果进行短时间缓存例如30秒对于读多写少的场景效果显著。7.4 第四步数据归档与分区对于历史数据我们实施了归档策略。将超过一年未活跃且标签不再变化的用户数据迁移到归档表主表只保留活跃数据。这直接减少了核心表的数据量让索引更高效。对于按时间范围查询的场景也可以考虑使用MySQL的分区表功能。7.5 效果对比优化前后同一个查询的响应时间从~2.5s下降到了~15ms。这个案例告诉我们虽然JSON提供了灵活性但在生产环境中必须通过合理的索引策略将其“驯服”才能兼顾灵活与性能。8. 总结与个人心得回过头看“判断JSON数组是否包含某元素”这个看似简单的需求背后牵扯到MySQL JSON类型的存储机制、JSON_CONTAINS函数的精确用法、类型的严格匹配、路径表达式的书写以及最重要的——在生产环境中的性能考量。我个人的体会是JSON字段是一把双刃剑。它极大地提升了开发初期应对需求变化的敏捷性尤其适合存储那些“不知道未来还会加什么”的扩展字段。但是一旦某个JSON内部的属性变成了高频过滤条件就必须严肃对待其性能问题。生成列索引是一个强大的补救工具但它本质上是在用空间和维护复杂度来换取查询性能。所以我的建议是在数据库设计时要有前瞻性。如果某个字段在未来极有可能被用于WHERE、ORDER BY或GROUP BY子句那么即使当前需求不明确也应该慎重考虑将其设计为独立的标量列。JSON字段更适合存储那些真正“辅助性”、“描述性”且查询模式多变的数据。最后无论使用何种技术理解底层原理都是写出高效、稳健代码的前提。希望这篇结合了原理、实战和避坑经验的分享能帮助你在下次处理MySQL JSON数据时更加得心应手。
![[光学原理与应用-521]：对光的错误理解与纠偏](http://pic.xiahunao.cn/yaotu/[光学原理与应用-521]：对光的错误理解与纠偏)
