基础操作与路径表达式
在MySQL 5.7+版本中,JSON字段的查询主要依赖->
和->>
运算符,配合JSON路径表达式实现精准定位。路径表达式遵循RFC 7159标准,其语法结构如下:
| |
| SELECT |
| data->'$.user.name' AS username, |
| data->>'$.metadata.create_time' AS create_time |
| FROM user_profiles |
| WHERE data->'$.settings.notify' = 'true' |
路径表达式深度解析
- 通配符匹配:
| |
| SELECT data->'$.orders[*].amount' |
| FROM ecommerce_orders |
| |
| |
| SELECT data->'$**.product_id' |
| FROM nested_catalogs |
- 条件过滤:
| |
| SELECT JSON_EXTRACT(data, '$.tags[?(@.priority > 3)]') |
| FROM document_store |
- 动态路径构建:
| SET @path = '$.contact.phones[0]'; |
| SELECT data->@path FROM customer_data; |
核心JSON函数详解
1. JSON_CONTAINS
| |
| SELECT * FROM product_list |
| WHERE JSON_CONTAINS(specs->'$.colors', '"red"') |
| |
| |
| SELECT * FROM api_logs |
| WHERE JSON_CONTAINS(payload, '{"status":404}', '$.response') |
2. JSON_SEARCH
| |
| SELECT JSON_SEARCH(data, 'all', '%apple%') |
| FROM device_inventories |
| WHERE device_type = 'laptop' |
| |
| |
| SELECT JSON_UNQUOTE(JSON_SEARCH(data, 'one', '192.168.1.1')) |
| FROM network_configs |
3. JSON_KEYS与JSON_LENGTH
| |
| SELECT |
| JSON_KEYS(data->'$.sensors') AS sensor_types, |
| JSON_LENGTH(data->'$.readings') AS reading_count |
| FROM iot_devices |
高级查询模式
1. JSON数组展开
| |
| SELECT |
| order_id, |
| jt.item_id, |
| jt.quantity |
| FROM orders, |
| JSON_TABLE( |
| items, |
| '$[*]' COLUMNS ( |
| item_id INT PATH '$.id', |
| quantity INT PATH '$.qty' |
| ) |
| ) AS jt |
2. 多条件复合查询
| SELECT * |
| FROM user_activities |
| WHERE |
| JSON_EXTRACT(log_data, '$.duration') > 300 |
| AND JSON_CONTAINS(log_data->'$.tags', '"vip"') |
| AND JSON_TYPE(log_data->'$.device') = 'OBJECT' |
3. JSON数据修改
| |
| UPDATE app_config |
| SET settings = JSON_SET( |
| settings, |
| '$.theme.color', '#2c3e50', |
| '$.notifications.email', 'false' |
| ) |
| WHERE user_id = 1001 |
| |
| |
| UPDATE product_reviews |
| SET comments = JSON_ARRAY_APPEND(comments, '$', '{"text":"Good quality"}') |
性能优化实践
1. 虚拟列索引
| ALTER TABLE customer_profiles |
| ADD COLUMN zip_code VARCHAR(10) |
| GENERATED ALWAYS AS (data->>'$.address.zip'), |
| ADD INDEX (zip_code) |
2. 函数索引(MySQL 8.0+)
| CREATE INDEX idx_price ON products ( |
| (CAST(JSON_EXTRACT(specs, '$.price') AS DECIMAL(10,2))) |
| ) |
3. 查询效率对比测试
| |
| EXPLAIN ANALYZE |
| SELECT * FROM log_data |
| WHERE JSON_EXTRACT(content, '$.user_id') = 12345; |
| |
| |
| EXPLAIN ANALYZE |
| SELECT * FROM log_data |
| WHERE user_id = 12345; |
实战案例:电商数据分析
数据结构示例
| { |
| "order_id": "20230715-001", |
| "customer": { |
| "id": 11547, |
| "vip_level": 3 |
| }, |
| "items": [ |
| {"sku": "A100", "qty": 2, "price": 49.99}, |
| {"sku": "B205", "qty": 1, "price": 129.99} |
| ], |
| "payment": { |
| "method": "credit_card", |
| "amount": 229.97 |
| } |
| } |
复杂查询示例
| |
| SELECT |
| order_id, |
| JSON_EXTRACT(data, '$.payment.amount') AS total, |
| JSON_EXTRACT(data, '$.customer.vip_level') AS vip |
| FROM orders |
| WHERE |
| JSON_EXTRACT(data, '$.customer.vip_level') >= 3 |
| AND JSON_EXTRACT(data, '$.payment.amount') > 200 |
| |
| |
| SELECT |
| item->>'$.sku' AS sku, |
| SUM(item->>'$.qty') AS total_qty, |
| SUM(item->>'$.price' * item->>'$.qty') AS total_sales |
| FROM orders, |
| JSON_TABLE( |
| data->'$.items', |
| '$[*]' COLUMNS ( |
| item JSON PATH '$' |
| ) |
| ) AS items |
| GROUP BY sku |
异常处理与验证
1. 类型验证
| |
| SELECT |
| CASE WHEN JSON_TYPE(data->'$.value') = 'INTEGER' |
| THEN CAST(data->>'$.value' AS UNSIGNED) |
| ELSE 0 |
| END AS safe_value |
| FROM sensor_readings |
2. 存在性检查
| SELECT |
| JSON_EXISTS(data, '$.backup_contacts[0]') AS has_backup, |
| JSON_CONTAINS_PATH(data, 'one', '$.emergency_phone') AS has_emergency |
| FROM user_profiles |
版本兼容性策略
- MySQL 5.7到8.0迁移示例:
| |
| SELECT JSON_EXTRACT(data, '$.contact.phone') |
| FROM customers |
| |
| |
| SELECT data->'$.contact.phone' |
| FROM customers |
- 新特性检测:
| |
| SELECT @@version LIKE '%8.0%' AS has_json_schema_support |
可视化工具辅助
推荐使用MySQL Workbench的JSON可视化分析器,执行:
| SELECT |
| JSON_PRETTY(data), |
| JSON_STORAGE_SIZE(data) AS storage_bytes |
| FROM api_responses |
| WHERE id = 3001 |
最佳实践总结
- 数据建模时避免超过3层嵌套
- 对频繁查询的字段建立生成列索引
- 使用JSON_VALID约束保证数据完整性
- 定期使用JSON_STORAGE_FREE释放空间
- 复杂查询拆分为多个CTE表达式
微信扫一扫:分享
微信里点“发现”,扫一下
二维码便可将本文分享至朋友圈。