"当产品经理第7次修改需求文档时，我的MySQL表结构终于崩溃了..." 这样的场景你是否似曾相识？在快速迭代的互联网时代，固定表结构常常成为开发者的噩梦。但鲜为人知的是，你手中的MySQL8.0早已暗藏利器——JSON字段类型。今天给大家分享MySQL8.0当中JSON字段类型的用法，在保持关系型数据库严谨性的同时，收获NoSQL般的灵活体验。

一、基础篇：JSON字段操作速成

1.1 创建你的第一个JSON字段

CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    profile JSON NOT NULL COMMENT '用户档案信息',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 数据插入的三种方式

标准插入：

INSERT INTO user_profiles (user_id, profile)
VALUES (1001, '{
    "basic": {
        "name": "张三",
        "gender": "male",
        "age": 28
    },
    "preferences": {
        "theme": "dark",
        "notification": true
    }
}');

动态生成JSON：

INSERT INTO user_profiles (user_id, profile)
VALUES (1002, JSON_OBJECT(
    'basic', JSON_OBJECT(
        'name', '李四',
        'birthday', CURDATE() - INTERVAL 25 YEAR
    ),
    'social', JSON_ARRAY('wechat', 'dingtalk')
));

程序生成示例（Python）：

import json
profile = {
    "experimental_features": {
        "dark_mode": True,
        "beta_test": False
    }
}
cursor.execute("INSERT INTO user_profiles (user_id, profile) VALUES (%s, %s)", 
              (1003, json.dumps(profile)))

二、查询方式

2.1 精确路径查询

-- 查询所有使用深色主题的用户
SELECT user_id, profile->"$.preferences.theme" AS theme
FROM user_profiles
WHERE profile->"$.preferences.theme" = 'dark';

2.2 动态键值查询

-- 查找包含beta_test功能的用户
SELECT user_id, JSON_EXTRACT(profile, '$.experimental_features.beta_test') 
FROM user_profiles
WHERE JSON_CONTAINS_PATH(profile, 'one', '$.experimental_features.beta_test') = 1;

2.3 数组的妙用

-- 查询最近访问过杭州的用户
SELECT user_id, JSON_SEARCH(profile->'$.locations', 'all', '杭州') AS found
FROM user_profiles
WHERE JSON_CONTAINS(profile->'$.locations', '"杭州"');

三、动态Schema设计实战

3.1 动态字段扩展方案

元数据表设计：

CREATE TABLE dynamic_fields (
    field_id INT AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(50) NOT NULL UNIQUE,
    data_type ENUM('string','number','boolean') NOT NULL,
    description TEXT
);

-- 示例字段注册
INSERT INTO dynamic_fields (field_name, data_type) VALUES
('membership_level', 'number'),
('wechat_openid', 'string');

动态校验存储过程：

DELIMITER //
CREATE PROCEDURE safe_json_insert(
    IN uid INT,
    IN jdata JSON
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '22032'
    BEGIN
        SELECT 'Invalid JSON structure' AS error;
    END;
    
    INSERT INTO user_profiles (user_id, profile)
    VALUES (uid, jdata);
END //
DELIMITER ;

四、性能优化三板斧

4.1 索引优化方案

-- 为常用查询字段创建虚拟列+索引
ALTER TABLE user_profiles
ADD COLUMN user_name VARCHAR(50) 
    GENERATED ALWAYS AS (profile->"$.basic.name") VIRTUAL,
ADD INDEX idx_name (user_name);

4.2 查询优化技巧

-- 低效查询 全表扫描
SELECT * FROM user_profiles 
WHERE JSON_EXTRACT(profile, '$.preferences.notification') = true;

-- 优化后查询
SELECT * FROM user_profiles 
WHERE profile->"$.preferences.notification" = CAST(true AS JSON);

执行计划对比：

原始查询：type=ALL, rows=1000
优化后：type=range, rows=50

五、实战案例：电商用户画像系统

场景需求：

• 每个商户可自定义用户属性
• 需要支持多维度组合查询
• 属性变更需记录历史版本

混合存储方案：

CREATE TABLE customer_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    shop_id INT NOT NULL,
    core_data JSON NOT NULL COMMENT '核心固定字段',
    extended_data JSON NOT NULL COMMENT '扩展动态字段',
    version INT DEFAULT 0,
    INDEX idx_shop (shop_id)
);

-- 版本管理触发器
DELIMITER //
CREATE TRIGGER before_profile_update 
BEFORE UPDATE ON customer_profiles
FOR EACH ROW
BEGIN
    INSERT INTO profile_history 
    SELECT NULL, OLD.*;
    SET NEW.version = OLD.version + 1;
END //
DELIMITER ;

总结

通过本文的实战演练，我们看到MySQL的JSON字段在以下场景还是非常实用的：

• 快速迭代期的临时方案
• 稀疏字段的存储优化
• 混合结构数据场景
• 作为分库分表前的缓冲方案

但也要注意其局限性：当JSON文档超过1MB时，性能会显著下降；完全非结构化的数据存储还是应该考虑专门的文档数据库。