MySQL进行JSON复杂查询的完全指南！

admin • • SQL技巧

MySQL进行JSON复杂查询的完全指南！

作者：都叫我大帅哥
这篇文章主要为大家详细介绍了MySQL中进行JSON复杂查询的相关知识,包括从等值判断到深度搜索,文中的示例代码讲解详细,感兴趣的喜欢可以了解下。

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

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

1
2
3
4
5
6
-- 案例：查找配置完全相同的设备（键顺序必须一致）
SELECT * FROM device_configs
WHERE config_json = '{"resolution": "1080p", "brightness": 80}';
-- 陷阱警告：以下两个JSON会被认为不同（键顺序不同）
'{"a":1, "b":2}' vs '{"b":2, "a":1}'

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

1
2
3
4
5
6
7
8
-- 方法：使用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; -- 确保没有多余字段

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

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

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

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

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

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

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

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

3.1 多层级路径查询

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

3.2 通配符搜索所有层级

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

3.3 深度过滤数组对象

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

四、混合条件综合查询

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

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

4.2 多JSON字段关联查询

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

4.3 动态条件生成查询

1
2
3
4
5
6
7
8
9
10
11
-- 根据前端传入的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);

五、性能优化黑科技

5.1 虚拟列 + 索引加速

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

5.2 函数索引（MySQL 8.0+）

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

5.3 查询重写优化

1
2
3
4
5
6
7
8
-- 原查询（性能差）
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);

六、经典踩坑案例

6.1 隐式类型转换陷阱

1
2
3
4
5
6
7
-- 错误：字符串与数字比较导致索引失效
SELECT * FROM products
WHERE data_json->>'$.id' = 100; -- $.id值是字符串"100"
-- 正确：显式类型转换
SELECT * FROM products
WHERE CAST(data_json->>'$.id' AS UNSIGNED) = 100;

6.2 通配符滥用灾难

1
2
3
4
5
-- 错误：左模糊查询全表扫描
SELECT * FROM articles
WHERE content_json->>'$.text' LIKE '%重要通知%';
-- 正确：使用全文索引或专用搜索引擎（如Elasticsearch）

6.3 大JSON修改雪崩

1
2
3
4
5
6
-- 错误：频繁更新大JSON字段导致IO飙升
UPDATE user_activities
SET log_json = JSON_ARRAY_APPEND(log_json, '$', '新事件')
WHERE user_id = 1001;
-- 正确：拆分成关系表或分片存储

七、超硬核面试题

题目：如何高效实现JSON数组的交集查询？

示例：查找tags数组同时包含["VIP","北京","90后"]的用户

参考答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 方法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;

到此这篇关于MySQL进行JSON复杂查询的完全指南的文章就介绍到这了。

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>