MySQL实战：Json字段类型详解

原创

小明互联网技术分享社区

发布于 2025-07-15 08:57:39

1.8K0

文章被收录于专栏：MYSQLMYSQL

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

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

MySQL5.7.8 版本之后，MySQL数据表新增了一个原生的 JSON 数据类型：JSON格式字符串将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式；

在 JSON 列插入或者更新的时候将会自动验校验是否符合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 精确路径查询

可以根据Json键值进行查询

-- 查询所有使用深色主题的用户
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 ;

总结

以上是关于Json字段类型的介绍，总结MySQL的JSON字段在以下场景还是非常实用的：

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

但也要注意其局限性：当JSON文档超过1MB时，性能会显著下降；完全非结构化的数据存储还是建议考虑专门的文档数据库比如MongoDB才是最佳的方案之一。大家如果使用过程中有问题的话，欢迎评论区聊一聊！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

腾讯技术创作特训营S14#新手村

腾讯技术创作特训营S14#补给站

#mysql实战