高级-JSON数据处理

清峰大约 8 分钟

高级-JSON数据处理

业务场景引入

现代应用中JSON数据越来越普遍：

商品属性：不同类别商品有不同的属性字段（颜色、尺寸、规格等）
用户画像：用户的兴趣标签、行为数据等非结构化信息
日志数据：应用日志、埋点数据通常以JSON格式存储
配置信息：系统配置、功能开关等灵活配置数据
API数据交换：微服务间数据传输大量使用JSON格式

MySQL 5.7+引入的原生JSON数据类型为这些场景提供了完美解决方案。

JSON数据类型基础

JSON列定义与操作

-- 创建包含JSON字段的表
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    
    -- JSON字段存储商品属性
    attributes JSON,
    
    -- JSON字段存储库存信息
    inventory JSON,
    
    -- JSON字段存储标签
    tags JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入JSON数据
INSERT INTO products (product_name, category_id, price, attributes, inventory, tags) VALUES
('iPhone 15 Pro', 1, 7999.00, 
 JSON_OBJECT(
     'color', '深空黑色',
     'storage', '256GB',
     'screen_size', '6.1英寸',
     'camera', JSON_OBJECT(
         'main', '48MP',
         'ultra_wide', '12MP',
         'telephoto', '12MP'
     ),
     'features', JSON_ARRAY('Face ID', '无线充电', '防水IP68')
 ),
 JSON_OBJECT(
     'total_stock', 100,
     'available_stock', 85,
     'reserved_stock', 15,
     'warehouse_distribution', JSON_OBJECT(
         'beijing', 30,
         'shanghai', 35,
         'guangzhou', 20
     )
 ),
 JSON_ARRAY('旗舰', '摄影', '商务')
),

('MacBook Pro 14', 2, 14999.00,
 JSON_OBJECT(
     'processor', 'M3 Pro',
     'memory', '18GB',
     'storage', '512GB SSD',
     'display', JSON_OBJECT(
         'size', '14.2英寸',
         'resolution', '3024x1964',
         'brightness', '1000尼特'
     ),
     'ports', JSON_ARRAY('3×雷雷电4', 'HDMI', 'SDXC', 'MagSafe 3')
 ),
 JSON_OBJECT(
     'total_stock', 50,
     'available_stock', 45,
     'reserved_stock', 5
 ),
 JSON_ARRAY('专业', '设计', '编程')
);

JSON路径语法

-- JSON路径表达式语法说明
-- $ : 根对象
-- .key : 对象成员
-- [index] : 数组索引（从0开始）
-- [*] : 数组所有元素
-- .** : 递归搜索

-- 基础查询示例
SELECT 
    product_name,
    -- 提取简单属性
    JSON_EXTRACT(attributes, '$.color') AS color,
    JSON_EXTRACT(attributes, '$.storage') AS storage,
    
    -- 提取嵌套对象
    JSON_EXTRACT(attributes, '$.camera.main') AS main_camera,
    
    -- 提取数组元素
    JSON_EXTRACT(attributes, '$.features[0]') AS first_feature,
    
    -- 使用->操作符（等价于JSON_EXTRACT）
    attributes->'$.color' AS color_operator,
    attributes->>'$.color' AS color_unquoted  -- ->>去除引号
FROM products;

JSON函数详解

创建和修改JSON

-- JSON创建函数
SELECT 
    -- 创建JSON对象
    JSON_OBJECT('name', 'John', 'age', 30, 'city', 'Beijing') AS user_info,
    
    -- 创建JSON数组
    JSON_ARRAY('Apple', 'Banana', 'Orange') AS fruits,
    
    -- 合并JSON对象
    JSON_MERGE_PATCH(
        JSON_OBJECT('name', 'John', 'age', 30),
        JSON_OBJECT('age', 31, 'city', 'Shanghai')
    ) AS merged_user;

-- JSON修改操作
UPDATE products 
SET attributes = JSON_SET(
    attributes,
    '$.color', '星际蓝',  -- 更新现有属性
    '$.warranty', '1年'   -- 添加新属性
)
WHERE product_id = 1;

-- 复杂的JSON更新
UPDATE products 
SET inventory = JSON_SET(
    inventory,
    '$.available_stock', inventory->'$.available_stock' - 1,
    '$.last_updated', NOW()
)
WHERE product_id = 1;

-- 删除JSON属性
UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.warranty')
WHERE product_id = 1;

-- 数组操作
UPDATE products 
SET attributes = JSON_ARRAY_APPEND(attributes, '$.features', '双卡双待')
WHERE product_id = 1;

JSON查询函数

-- JSON搜索和查询
SELECT 
    product_name,
    attributes,
    
    -- 检查JSON键是否存在
    JSON_CONTAINS_PATH(attributes, 'one', '$.camera') AS has_camera,
    
    -- 检查JSON值是否包含特定内容
    JSON_CONTAINS(attributes, '"Face ID"', '$.features') AS has_face_id,
    
    -- 搜索JSON路径
    JSON_SEARCH(attributes, 'one', 'Face ID') AS face_id_path,
    
    -- 获取JSON对象键名
    JSON_KEYS(attributes) AS attribute_keys,
    
    -- 计算JSON数组长度
    JSON_LENGTH(attributes, '$.features') AS feature_count
    
FROM products;

-- 复杂JSON查询示例
SELECT 
    product_name,
    price,
    JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) AS color,
    JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.storage')) AS storage,
    JSON_EXTRACT(inventory, '$.available_stock') AS stock
FROM products
WHERE JSON_EXTRACT(attributes, '$.storage') = '"256GB"'
  AND JSON_EXTRACT(inventory, '$.available_stock') > 10;

高级JSON应用场景

用户画像数据管理

-- 用户画像表
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    basic_info JSON NOT NULL,
    preferences JSON,
    behavior_data JSON,
    tags JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 为常用查询字段创建虚拟列索引
    age INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.age'))) VIRTUAL,
    city VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.city'))) VIRTUAL,
    
    INDEX idx_age (age),
    INDEX idx_city (city)
) ENGINE=InnoDB;

-- 插入用户画像数据
INSERT INTO user_profiles (user_id, basic_info, preferences, behavior_data, tags) VALUES
(1001, 
 JSON_OBJECT(
     'name', '张三',
     'age', 28,
     'gender', '男',
     'city', '北京',
     'occupation', '软件工程师',
     'income_level', 'middle'
 ),
 JSON_OBJECT(
     'categories', JSON_ARRAY('数码', '图书', '运动'),
     'brands', JSON_ARRAY('Apple', 'Nike', 'Adidas'),
     'price_range', JSON_OBJECT('min', 100, 'max', 5000),
     'shopping_time', JSON_ARRAY('周末', '晚上')
 ),
 JSON_OBJECT(
     'last_30_days', JSON_OBJECT(
         'page_views', 150,
         'orders', 3,
         'total_amount', 2580.50,
         'categories_browsed', JSON_ARRAY('数码', '服装', '图书')
     ),
     'device_info', JSON_OBJECT(
         'primary_device', 'mobile',
         'os', 'iOS',
         'browser', 'Safari'
     )
 ),
 JSON_ARRAY('活跃用户', '高价值', '技术爱好者')
);

-- 用户画像查询示例
-- 1. 查询特定年龄段的用户
SELECT user_id, 
       JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.name')) AS name,
       JSON_EXTRACT(basic_info, '$.age') AS age
FROM user_profiles 
WHERE JSON_EXTRACT(basic_info, '$.age') BETWEEN 25 AND 35;

-- 2. 查询喜欢特定品牌的用户
SELECT user_id,
       JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.name')) AS name,
       JSON_EXTRACT(preferences, '$.brands') AS preferred_brands
FROM user_profiles
WHERE JSON_CONTAINS(JSON_EXTRACT(preferences, '$.brands'), '"Apple"');

-- 3. 查询高价值用户（近30天消费超过2000）
SELECT user_id,
       JSON_UNQUOTE(JSON_EXTRACT(basic_info, '$.name')) AS name,
       JSON_EXTRACT(behavior_data, '$.last_30_days.total_amount') AS total_spending
FROM user_profiles
WHERE JSON_EXTRACT(behavior_data, '$.last_30_days.total_amount') > 2000;

日志数据存储与分析

-- 应用日志表
CREATE TABLE application_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    timestamp TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL'),
    service_name VARCHAR(50),
    log_data JSON,
    
    -- 虚拟列用于常用查询
    user_id BIGINT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.user_id'))) VIRTUAL,
    request_id VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.request_id'))) VIRTUAL,
    
    INDEX idx_timestamp (timestamp),
    INDEX idx_level (level),
    INDEX idx_service (service_name),
    INDEX idx_user_id (user_id),
    INDEX idx_request_id (request_id)
) ENGINE=InnoDB;

-- 插入不同类型的日志
INSERT INTO application_logs (level, service_name, log_data) VALUES
('INFO', 'user-service', JSON_OBJECT(
    'action', 'user_login',
    'user_id', 1001,
    'request_id', 'req_20241201_001',
    'ip_address', '192.168.1.100',
    'user_agent', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)',
    'location', JSON_OBJECT('country', 'CN', 'city', '北京'),
    'duration_ms', 250
)),

('ERROR', 'order-service', JSON_OBJECT(
    'action', 'create_order',
    'user_id', 1002,
    'request_id', 'req_20241201_002',
    'error_code', 'INSUFFICIENT_STOCK',
    'error_message', '商品库存不足',
    'product_id', 2001,
    'requested_quantity', 5,
    'available_quantity', 2,
    'stack_trace', 'InsufficientStockException at OrderService.createOrder()'
)),

('INFO', 'payment-service', JSON_OBJECT(
    'action', 'payment_completed',
    'user_id', 1001,
    'order_id', 'ORD20241201001',
    'payment_method', 'alipay',
    'amount', 299.99,
    'transaction_id', 'txn_20241201_abc123',
    'processing_time_ms', 1200
));

-- 日志分析查询
-- 1. 查询特定用户的操作日志
SELECT timestamp, level, service_name,
       JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.action')) AS action,
       JSON_EXTRACT(log_data, '$.duration_ms') AS duration
FROM application_logs
WHERE user_id = 1001
ORDER BY timestamp DESC;

-- 2. 错误日志统计
SELECT 
    service_name,
    JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.error_code')) AS error_code,
    COUNT(*) AS error_count
FROM application_logs
WHERE level = 'ERROR'
  AND timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY service_name, JSON_EXTRACT(log_data, '$.error_code');

-- 3. 性能分析：慢请求统计
SELECT 
    service_name,
    JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.action')) AS action,
    AVG(JSON_EXTRACT(log_data, '$.duration_ms')) AS avg_duration,
    MAX(JSON_EXTRACT(log_data, '$.duration_ms')) AS max_duration,
    COUNT(*) AS request_count
FROM application_logs
WHERE JSON_EXTRACT(log_data, '$.duration_ms') IS NOT NULL
GROUP BY service_name, JSON_EXTRACT(log_data, '$.action')
HAVING avg_duration > 1000;

动态表单和配置管理

-- 动态表单配置表
CREATE TABLE form_configurations (
    form_id VARCHAR(50) PRIMARY KEY,
    form_name VARCHAR(200) NOT NULL,
    form_schema JSON NOT NULL,
    validation_rules JSON,
    ui_config JSON,
    version INT DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 插入表单配置
INSERT INTO form_configurations (form_id, form_name, form_schema, validation_rules, ui_config) VALUES
('user_registration', '用户注册表单', 
 JSON_OBJECT(
     'fields', JSON_ARRAY(
         JSON_OBJECT(
             'name', 'username',
             'type', 'text',
             'label', '用户名',
             'required', true,
             'placeholder', '请输入用户名'
         ),
         JSON_OBJECT(
             'name', 'email',
             'type', 'email',
             'label', '邮箱',
             'required', true,
             'placeholder', '请输入邮箱地址'
         ),
         JSON_OBJECT(
             'name', 'phone',
             'type', 'tel',
             'label', '手机号',
             'required', true,
             'placeholder', '请输入手机号'
         ),
         JSON_OBJECT(
             'name', 'interests',
             'type', 'checkbox',
             'label', '兴趣爱好',
             'options', JSON_ARRAY('阅读', '运动', '旅游', '音乐', '电影')
         )
     )
 ),
 JSON_OBJECT(
     'username', JSON_OBJECT(
         'min_length', 3,
         'max_length', 20,
         'pattern', '^[a-zA-Z0-9_]+$'
     ),
     'email', JSON_OBJECT(
         'pattern', '^[^@]+@[^@]+\\.[^@]+$'
     ),
     'phone', JSON_OBJECT(
         'pattern', '^1[3-9]\\d{9}$'
     )
 ),
 JSON_OBJECT(
     'layout', 'vertical',
     'theme', 'light',
     'submit_button', JSON_OBJECT(
         'text', '注册',
         'color', 'primary'
     )
 )
);

-- 用户提交的表单数据
CREATE TABLE form_submissions (
    submission_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    form_id VARCHAR(50) NOT NULL,
    user_id BIGINT,
    form_data JSON NOT NULL,
    submission_ip VARCHAR(45),
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_form_id (form_id),
    INDEX idx_user_id (user_id),
    INDEX idx_submitted_at (submitted_at),
    
    FOREIGN KEY (form_id) REFERENCES form_configurations(form_id)
) ENGINE=InnoDB;

-- 查询表单配置和动态生成
SELECT 
    form_name,
    JSON_PRETTY(form_schema) AS schema,
    JSON_EXTRACT(form_schema, '$.fields[*].name') AS field_names
FROM form_configurations 
WHERE form_id = 'user_registration';

JSON索引和性能优化

虚拟列索引

-- 为JSON字段创建虚拟列和索引
ALTER TABLE products 
ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) VIRTUAL,
ADD COLUMN storage VARCHAR(20) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.storage'))) VIRTUAL,
ADD COLUMN available_stock INT GENERATED ALWAYS AS (JSON_EXTRACT(inventory, '$.available_stock')) VIRTUAL;

-- 为虚拟列创建索引
CREATE INDEX idx_color ON products (color);
CREATE INDEX idx_storage ON products (storage);
CREATE INDEX idx_available_stock ON products (available_stock);

-- 使用虚拟列索引的查询
SELECT product_name, price, color, storage 
FROM products 
WHERE color = '深空黑色' 
  AND available_stock > 10;

JSON搜索优化

-- 多值索引（MySQL 8.0.17+）
ALTER TABLE products 
ADD INDEX idx_features ((CAST(attributes->'$.features' AS CHAR(255) ARRAY)));

-- 使用多值索引查询
SELECT product_name, price
FROM products
WHERE JSON_CONTAINS(attributes->'$.features', '"Face ID"');

-- 函数索引（MySQL 8.0.13+）
CREATE INDEX idx_camera_main ON products ((JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.camera.main'))));

-- 查询性能对比
EXPLAIN SELECT * FROM products WHERE JSON_EXTRACT(attributes, '$.camera.main') = '48MP';

JSON查询性能优化

-- 避免在大结果集上使用JSON函数
-- ❌ 性能较差
SELECT product_name, JSON_EXTRACT(attributes, '$.color') 
FROM products 
WHERE category_id = 1;

-- ✅ 使用虚拟列
SELECT product_name, color 
FROM products 
WHERE category_id = 1;

-- 批量JSON更新优化
-- ❌ 逐行更新
UPDATE products 
SET attributes = JSON_SET(attributes, '$.updated_at', NOW())
WHERE category_id = 1;

-- ✅ 使用CASE语句批量更新
UPDATE products 
SET attributes = CASE 
    WHEN category_id = 1 THEN JSON_SET(attributes, '$.promotion', 'electronics_sale')
    WHEN category_id = 2 THEN JSON_SET(attributes, '$.promotion', 'computer_discount')
    ELSE attributes
END
WHERE category_id IN (1, 2);

JSON数据迁移和兼容性

从关系型到JSON的迁移

-- 原始关系型表结构
CREATE TABLE product_attributes_old (
    product_id BIGINT,
    attribute_name VARCHAR(50),
    attribute_value VARCHAR(500),
    PRIMARY KEY (product_id, attribute_name)
);

-- 迁移到JSON结构
INSERT INTO products (product_id, product_name, attributes)
SELECT 
    p.product_id,
    p.product_name,
    JSON_OBJECTAGG(pa.attribute_name, pa.attribute_value) AS attributes
FROM product_master p
LEFT JOIN product_attributes_old pa ON p.product_id = pa.product_id
GROUP BY p.product_id, p.product_name;

-- 验证迁移结果
SELECT 
    product_id,
    JSON_KEYS(attributes) AS attribute_keys,
    JSON_LENGTH(attributes) AS attribute_count
FROM products 
WHERE attributes IS NOT NULL;

JSON数据验证

-- 创建JSON模式验证函数
DELIMITER $$
CREATE FUNCTION validate_product_attributes(attr JSON) 
RETURNS BOOLEAN
READS SQL DATA
DETERMINISTIC
BEGIN
    -- 检查必需字段
    IF NOT JSON_CONTAINS_PATH(attr, 'one', '$.color') THEN
        RETURN FALSE;
    END IF;
    
    -- 检查数据类型
    IF JSON_TYPE(JSON_EXTRACT(attr, '$.features')) != 'ARRAY' THEN
        RETURN FALSE;
    END IF;
    
    -- 检查值的有效性
    IF JSON_EXTRACT(attr, '$.storage') NOT IN ('"64GB"', '"128GB"', '"256GB"', '"512GB"', '"1TB"') THEN
        RETURN FALSE;
    END IF;
    
    RETURN TRUE;
END$$
DELIMITER ;

-- 使用验证函数
SELECT product_id, product_name,
       validate_product_attributes(attributes) AS is_valid
FROM products 
WHERE NOT validate_product_attributes(attributes);

总结与最佳实践

JSON使用场景选择

适合使用JSON的场景
- 属性不固定的数据（商品属性、用户画像）
- 日志和监控数据
- 配置信息和元数据
- API数据交换格式
不适合使用JSON的场景
- 需要复杂关联查询的数据
- 结构固定且查询频繁的数据
- 需要严格数据类型约束的场景

性能优化建议

-- 1. 合理使用虚拟列和索引
-- 为常用查询字段创建虚拟列索引

-- 2. 避免复杂的JSON路径表达式
-- ❌ 复杂路径
SELECT JSON_EXTRACT(data, '$**.user.profile.settings.theme') FROM logs;

-- ✅ 简化结构
SELECT JSON_EXTRACT(data, '$.user_theme') FROM logs;

-- 3. 批量操作优化
-- 使用JSON_MERGE_PATCH进行批量更新

-- 4. 数据大小控制
-- 避免在JSON字段中存储大量数据，考虑分离存储

开发规范建议

JSON结构设计
- 保持JSON结构相对稳定
- 使用有意义的键名
- 避免过深的嵌套层级
查询优化
- 为常用查询路径创建虚拟列
- 使用适当的索引策略
- 避免全表扫描JSON字段
数据验证
- 在应用层进行JSON格式验证
- 使用约束或触发器保证数据质量
- 定期检查JSON数据完整性

MySQL的JSON功能为现代应用提供了灵活的数据存储方案，合理使用可以大大简化开发复杂度，提高系统的灵活性和可扩展性。