MySQL中高效查询JSON字符串字段的方法详解

1. 创建索引（关键优化）

-- 对JSON字段创建虚拟列并建立索引
ALTER TABLE products ADD COLUMN category_name VARCHAR(50) 
    AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.category')));

CREATE INDEX idx_category ON products(category_name);

-- MySQL 8.0+ 支持函数索引
CREATE INDEX idx_price ON products((CAST(metadata->>'$.price' AS DECIMAL(10,2))));

2. 基础查询方法

使用 -> 和 ->>

-- -> 返回JSON（保持引号）
SELECT metadata->'$.category' FROM products;

-- ->> 返回文本（去除引号）
SELECT metadata->>'$.category' FROM products;

-- 条件查询
SELECT * FROM products 
WHERE metadata->>'$.category' = 'electronics';

SELECT * FROM orders 
WHERE JSON_EXTRACT(metadata, '$.status') = 'shipped';

3. 路径查询操作符

-- 访问嵌套对象
SELECT metadata->'$.details.specifications.weight'
FROM products;

-- 数组访问
SELECT metadata->'$.tags[0]'
FROM products;

-- 通配符查询（数组所有元素）
SELECT metadata->'$.tags[*]'
FROM products;

4. JSON函数优化查询

-- JSON_CONTAINS 检查是否存在
SELECT * FROM products 
WHERE JSON_CONTAINS(metadata, '"electronics"', '$.category');

-- JSON_SEARCH 搜索文本
SELECT * FROM products 
WHERE JSON_SEARCH(metadata, 'one', 'laptop') IS NOT NULL;

-- JSON_EXTRACT 多路径查询
SELECT JSON_EXTRACT(metadata, '$.price', '$.category')
FROM products;

-- JSON_KEYS 获取所有键
SELECT JSON_KEYS(metadata) FROM products;

5. 数组操作优化

-- 检查数组包含
SELECT * FROM products 
WHERE JSON_CONTAINS(metadata->'$.tags', '["sale", "new"]');

-- 数组长度
SELECT * FROM products 
WHERE JSON_LENGTH(metadata->'$.tags') > 3;

-- 展开JSON数组为多行（MySQL 8.0+）
SELECT p.id, jt.tag
FROM products p
JOIN JSON_TABLE(
    p.metadata->'$.tags',
    '$[*]' COLUMNS(tag VARCHAR(50) PATH '$')
) AS jt;

6. 性能优化建议

使用虚拟列+索引

-- 对频繁查询的字段创建虚拟列
ALTER TABLE products
ADD COLUMN price DECIMAL(10,2) 
    AS (CAST(metadata->>'$.price' AS DECIMAL(10,2))) STORED,
ADD INDEX idx_price (price);

-- 查询时使用虚拟列
SELECT * FROM products WHERE price > 1000;

避免全表扫描

-- 差的写法：函数操作在左侧
SELECT * FROM products 
WHERE JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.category')) = 'electronics';

-- 好的写法：如果创建了虚拟列索引
SELECT * FROM products WHERE category_name = 'electronics';

使用覆盖索引

-- 创建包含JSON字段和常用列的复合索引
CREATE INDEX idx_metadata_filter ON products(category_name, metadata);

7. 最佳实践示例

-- 1. 复杂条件查询
SELECT 
    id,
    metadata->>'$.name' as product_name,
    CAST(metadata->>'$.price' AS DECIMAL(10,2)) as price,
    JSON_LENGTH(metadata->'$.tags') as tag_count
FROM products
WHERE metadata->>'$.category' = 'electronics'
    AND CAST(metadata->>'$.price' AS DECIMAL(10,2)) > 500
    AND JSON_CONTAINS(metadata->'$.tags', '"wireless"')
ORDER BY CAST(metadata->>'$.price' AS DECIMAL(10,2)) DESC;

-- 2. 聚合查询
SELECT 
    metadata->>'$.category' as category,
    COUNT(*) as count,
    AVG(CAST(metadata->>'$.price' AS DECIMAL(10,2))) as avg_price
FROM products
GROUP BY metadata->>'$.category'
HAVING avg_price > 100;

-- 3. 更新JSON字段（保持其他部分不变）
UPDATE products
SET metadata = JSON_SET(metadata, '$.stock', 50)
WHERE id = 1;

8. 监控和调试

-- 查看JSON查询执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM products 
WHERE metadata->>'$.category' = 'electronics';

-- 使用性能模式监控
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%JSON%';

注意事项

选择合适的方法取决于具体场景：简单查询用->>，复杂查询用虚拟列+索引，数组操作用JSON函数。