首页>内容详情

MySQL JSON复杂查询：从等值判断到深度搜索

大熊•2025-07-09 08:09:26•文章关键词：MySQL,JSON,JSON等值查询

函数分类速览表

MySQL 的 JSON 函数库堪称数据库界的“瑞士军刀”，功能强大但容易让人眼花缭乱。下面用一张表格 + 代码示例 + 灵魂比喻，帮你彻底搞懂这些函数！

一、函数分类速览表

类别	典型函数	比喻	核心能力
查询解析	`JSON_EXTRACT`, `->`, `->>`	数据“显微镜”	精准提取特定字段
修改更新	`JSON_SET`, `JSON_REPLACE`	JSON“装修队”	局部修改不伤整体结构
创建构建	`JSON_OBJECT`, `JSON_ARRAY`	乐高积木组装师	手动构造JSON对象/数组
类型检查	`JSON_VALID`, `JSON_TYPE`	大家来找茬	验证格式合法性，识别数据类型
工具函数	`JSON_LENGTH`, `JSON_KEYS`	数据“体检仪”	测量尺寸、提取键名列表
性能优化	`JSON_CONTAINS`, `JSON_SEARCH`	高速扫描仪	快速判断包含关系，定位路径

二、重点函数详解

1. 查询解析三剑客

-- 原始数据
SET @user = '{
  "name": "李四",
  "age": 28,
  "address": {"city": "北京", "zip": "100000"},
  "hobbies": ["coding", "篮球"]
}';

-- (1) JSON_EXTRACT(官方版)
SELECT JSON_EXTRACT(@user, '$.address.city'); -- "北京"（带引号）

-- (2) -> 操作符（语法糖）
SELECT @user->'$.hobbies[0]'; -- "coding"（带引号）

-- (3) ->> 操作符（去引号版）
SELECT @user->>'$.name' AS name; -- 李四（纯文本）

避坑指南：

路径表达式严格区分大小写！$.Name 和 $.name 是不同字段
数组越界返回 NULL，不会报错（像极了程序员的隐式容忍）

2. 修改更新四天王（MySQL 8.0+）

-- (1) JSON_SET：无中生有（新增字段）
SET @user = JSON_SET(@user, '$.salary', 20000); 
-- 结果：添加 salary 字段

-- (2) JSON_REPLACE：偷梁换柱（修改现有字段）
SET @user = JSON_REPLACE(@user, '$.age', 29); 
-- 结果：age 从28变为29

-- (3) JSON_REMOVE：断舍离（删除字段）
SET @user = JSON_REMOVE(@user, '$.hobbies[1]'); 
-- 结果：hobbies数组只剩 ["coding"]

-- (4) JSON_ARRAY_APPEND：数组扩容
SET @user = JSON_ARRAY_APPEND(@user, '$.hobbies', '摸鱼'); 
-- 结果：hobbies变成 ["coding", "摸鱼"]

性能警告：

修改函数会生成新JSON对象，频繁操作可能触发内存告警（类似Java的String拼接）
修改后的JSON大小不能超过原值！否则直接报错（防止数据膨胀）

3. 构建函数：手动造JSON

-- (1) 造对象：JSON_OBJECT（键值对自动包装）
SELECT JSON_OBJECT('name', '王五', 'age', 30); 
-- 结果：{"name": "王五", "age": 30}

-- (2) 造数组：JSON_ARRAY（元素自动转JSON类型）
SELECT JSON_ARRAY(1, 'text', NOW(), JSON_OBJECT('a', 10)); 
-- 结果：[1, "text", "2024-02-20 12:00:00", {"a": 10}]

-- (3) 合并对象：JSON_MERGE_PATCH（8.0+覆盖式合并）
SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":2, "b":3}'); 
-- 结果：{"a": 2, "b": 3}

冷知识：
JSON_OBJECT 的键名如果是数字，会被强制转字符串（如 JSON_OBJECT(1, 'test') 生成 {"1": "test"})

4. 类型检查与工具函数

-- (1) 合法性验证：JSON_VALID
SELECT JSON_VALID('{"name": "张三"}'); -- 1（合法）
SELECT JSON_VALID('{name: "张三"}');   -- 0（键没引号，非法）

-- (2) 类型检测：JSON_TYPE
SELECT JSON_TYPE(@user->'$.age');    -- INTEGER
SELECT JSON_TYPE(@user->'$.name');  -- STRING
SELECT JSON_TYPE(@user->'$.hobbies'); -- ARRAY

-- (3) 键名提取：JSON_KEYS
SELECT JSON_KEYS('{"a":1, "b":2}'); -- ["a", "b"]

-- (4) 长度测量：JSON_LENGTH
SELECT JSON_LENGTH('["a", "b", "c"]'); -- 3（数组长度）
SELECT JSON_LENGTH('{"a":1, "b":2}');  -- 2（对象键数量）

5. 高级搜索函数

-- (1) JSON_CONTAINS：是否包含某值
SELECT JSON_CONTAINS('[1, 2, 3]', '2', '$'); -- 1（存在）

-- (2) JSON_SEARCH：模糊查找路径
SELECT JSON_SEARCH('{"user": {"name": "李四"}}', 'one', '李四');
-- 结果："$.user.name"（类似文件搜索的定位功能）

性能优化技巧：

对 JSON_CONTAINS 查询建立虚拟列索引（否则全表扫描警告！）
JSON_SEARCH 的 one/all 参数控制返回第一个还是所有匹配路径

三、函数使用灵魂法则

路径表达式要写准：

键名带特殊符号用双引号：$."user-name"
数组索引从0开始：$.hobbies[0]

类型转换要显式：

-- 错误：字符串和数字比较
SELECT * FROM products WHERE attributes->'$.price' = '100'; 

-- 正确：统一类型
SELECT * FROM products 
WHERE CAST(attributes->'$.price' AS UNSIGNED) = 100;

索引要针对性：

-- 创建虚拟列 + 索引
ALTER TABLE products 
ADD COLUMN color VARCHAR(20) 
  GENERATED ALWAYS AS (attributes->>'$.color'),
ADD INDEX idx_color (color);

四、一道面试题实战

题目：如何将JSON数组 ["a","b","c"] 转换成关系型数据表？

-- 使用 JSON_TABLE 函数（MySQL 8.0+）
SELECT * FROM JSON_TABLE(
  '["a", "b", "c"]',
  '$[*]' COLUMNS (
    id FOR ORDINALITY,  -- 自动生成行号
    value VARCHAR(10) PATH '$'
  )
) AS t;

输出：

id	value
1	a
2	b
3	c

终极总结
函数选择口诀： “查用->>，改用SET，建用OBJECT，验用VALID”
性能口诀： “路径要短，索引要建，类型对齐，少用通配”

五、JSON对象全等判断：当强迫症遇到数据结构

5.1 精确匹配（键顺序敏感）

-- 案例：查找配置完全相同的设备（键顺序必须一致）
SELECT * FROM device_configs 
WHERE config_json = '{"resolution": "1080p", "brightness": 80}';

-- 陷阱警告：以下两个JSON会被认为不同（键顺序不同）
'{"a":1, "b":2}' vs '{"b":2, "a":1}'

5.2 松散全等判断（键顺序无关）

-- 方法：使用JSON_CONTAINS双向包含 + 长度相同
SELECT * FROM device_configs 
WHERE 
  JSON_CONTAINS(config_json, '{"brightness": 80, "resolution": "1080p"}') 
  AND 
  JSON_CONTAINS('{"brightness": 80, "resolution": "1080p"}', config_json)
  AND
  JSON_LENGTH(config_json) = 2; -- 确保没有多余字段

六、数组的“灵魂拷问”式查询

6.1 数组完全相等（顺序敏感）

-- 查找tags数组严格等于["VIP","北京"]的用户（顺序、数量、元素完全一致）
SELECT * FROM users 
WHERE tags_json = CAST('["VIP","北京"]' AS JSON);

6.2 数组包含所有元素（顺序无关）

-- 查找tags包含"VIP"和"北京"的用户（类似AND条件）
SELECT * FROM users 
WHERE 
  JSON_CONTAINS(tags_json, '"VIP"') 
  AND 
  JSON_CONTAINS(tags_json, '"北京"');

6.3 数组包含任意元素（类似OR条件）

-- 查找tags包含"VIP"或"北京"的用户
SELECT * FROM users 
WHERE 
  JSON_CONTAINS(tags_json, '["VIP"]') 
  OR 
  JSON_CONTAINS(tags_json, '["北京"]');

七、嵌套结构的“掘地三尺”查询

7.1 多层级路径查询

-- 查找住在"北京朝阳区"的用户（嵌套对象查询）
SELECT * FROM users 
WHERE address_json->>'$.city' = '北京' 
  AND address_json->>'$.district' = '朝阳区';

7.2 通配符搜索所有层级

-- 查找任意层级包含"error_code":500的日志（递归搜索）
SELECT * FROM service_logs 
WHERE JSON_SEARCH(log_json, 'all', '500', NULL, '$**.error_code') IS NOT NULL;

7.3 深度过滤数组对象

-- 查找订单中有商品ID=100且数量>2的订单（数组对象过滤）
SELECT * FROM orders 
WHERE JSON_EXISTS(
  items_json, 
  '$[*]?(@.product_id == 100 && @.quantity > 2)'
);

八、混合条件综合查询

8.1 JSON字段 + 关系字段联合查询

-- 查找2023年后注册，且扩展信息中device_type="iOS"的用户
SELECT * FROM users 
WHERE 
  register_time > '2023-01-01' 
  AND 
  ext_info->>'$.device_type' = 'iOS';

8.2 多JSON字段关联查询

-- 查找购物车总价>1000且包含"急件"标签的订单
SELECT * FROM orders 
WHERE 
  CAST(cart_info->>'$.total_price' AS DECIMAL) > 1000 
  AND 
  JSON_CONTAINS(tags_json, '"急件"');

8.3 动态条件生成查询

-- 根据前端传入的JSON过滤条件动态查询（PHP示例）
$filters = '{"status":"pending","price":{"$gt":100}}';
$where = [];
foreach(json_decode($filters, true) as $key => $value){
  if(is_array($value)){
    $where[] = "data_json->>'$.$key' > ".$value['$gt'];
  }else{
    $where[] = "data_json->>'$.$key' = '$value'";
  }
}
$sql = "SELECT * FROM products WHERE ".implode(' AND ', $where);

九、性能优化黑科技

9.1 虚拟列 + 索引加速

-- 为常用查询条件创建虚拟列索引
ALTER TABLE users 
ADD COLUMN city VARCHAR(20) 
  GENERATED ALWAYS AS (address_json->>'$.city'),
ADD INDEX idx_city (city);

9.2 函数索引（MySQL 8.0+）

-- 直接为JSON路径表达式创建索引
CREATE INDEX idx_price ON products ((CAST(data_json->>'$.price' AS DECIMAL)));

9.3 查询重写优化

-- 原查询（性能差）
SELECT * FROM logs 
WHERE JSON_EXTRACT(log_data, '$.request.time') > '2023-01-01';

-- 优化后（提取时间到独立字段 + 索引）
ALTER TABLE logs ADD COLUMN request_time DATETIME 
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.request.time')));
CREATE INDEX idx_request_time ON logs(request_time);

十、经典踩坑案例

10.1 隐式类型转换陷阱

-- 错误：字符串与数字比较导致索引失效
SELECT * FROM products 
WHERE data_json->>'$.id' = 100; -- $.id值是字符串"100"

-- 正确：显式类型转换
SELECT * FROM products 
WHERE CAST(data_json->>'$.id' AS UNSIGNED) = 100;

10.2 通配符滥用灾难

-- 错误：左模糊查询全表扫描
SELECT * FROM articles 
WHERE content_json->>'$.text' LIKE '%重要通知%';

-- 正确：使用全文索引或专用搜索引擎（如Elasticsearch）

10.3 大JSON修改雪崩

-- 错误：频繁更新大JSON字段导致IO飙升
UPDATE user_activities 
SET log_json = JSON_ARRAY_APPEND(log_json, '$', '新事件') 
WHERE user_id = 1001;

-- 正确：拆分成关系表或分片存储

十一、超硬核面试题

题目：如何高效实现JSON数组的交集查询？
示例：查找tags数组同时包含["VIP","北京","90后"]的用户
参考答案：

-- 方法1：JSON_CONTAINS链式调用
SELECT * FROM users 
WHERE 
  JSON_CONTAINS(tags_json, '"VIP"') 
  AND JSON_CONTAINS(tags_json, '"北京"')
  AND JSON_CONTAINS(tags_json, '"90后"');

-- 方法2：利用JSON_TABLE展开后统计（MySQL 8.0+）
SELECT user_id 
FROM users, JSON_TABLE(
  tags_json,
  '$[*]' COLUMNS(tag VARCHAR(10) PATH '$')
) AS tags 
WHERE tag IN ('VIP', '北京', '90后')
GROUP BY user_id
HAVING COUNT(DISTINCT tag) = 3;

上一篇：Redis持久化全解析：从健忘症患者到记忆大师的逆袭

下一篇：MySQL JSON数组严格相等判断的正确姿势