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

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

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

-- 方法：使用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; -- 确保没有多余字段

-- 查找tags数组严格等于["vip","北京"]的用户（顺序、数量、元素完全一致）
select * from users 
where tags_json = cast('["vip","北京"]' as json);

-- 查找tags包含"vip"和"北京"的用户（类似and条件）
select * from users 
where 
  json_contains(tags_json, '"vip"') 
  and 
  json_contains(tags_json, '"北京"');

-- 查找tags包含"vip"或"北京"的用户
select * from users 
where 
  json_contains(tags_json, '["vip"]') 
  or 
  json_contains(tags_json, '["北京"]');

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

-- 查找任意层级包含"error_code":500的日志（递归搜索）
select * from service_logs 
where json_search(log_json, 'all', '500', null, '$**.error_code') is not null;

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

-- 查找2023年后注册，且扩展信息中device_type="ios"的用户
select * from users 
where 
  register_time > '2023-01-01' 
  and 
  ext_info->>'$.device_type' = 'ios';

-- 查找购物车总价>1000且包含"急件"标签的订单
select * from orders 
where 
  cast(cart_info->>'$.total_price' as decimal) > 1000 
  and 
  json_contains(tags_json, '"急件"');

-- 根据前端传入的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);

-- 为常用查询条件创建虚拟列索引
alter table users 
add column city varchar(20) 
  generated always as (address_json->>'$.city'),
add index idx_city (city);

-- 直接为json路径表达式创建索引
create index idx_price on products ((cast(data_json->>'$.price' as decimal)));

-- 原查询（性能差）
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);

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

-- 正确：显式类型转换
select * from products 
where cast(data_json->>'$.id' as unsigned) = 100;

-- 错误：左模糊查询全表扫描
select * from articles 
where content_json->>'$.text' like '%重要通知%';

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

-- 错误：频繁更新大json字段导致io飙升
update user_activities 
set log_json = json_array_append(log_json, '$', '新事件') 
where user_id = 1001;

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

-- 方法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;