JSON 类型

导学

MySQL 5.7.8 引入了对 JSON 数据类型的原生支持，让你可以直接在数据库中存储和查询 JSON 文档，而不必将 JSON 字符串塞进 TEXT 字段再用程序解析。JSON 类型自动校验格式、优化存储、支持路径查询——是 MySQL 向文档型数据库能力延伸的重要一步。

定义

JSON：用于存储 JSON（JavaScript Object Notation）文档的数据类型。MySQL 5.7.8+ 支持。内部以二进制格式存储（非纯文本），支持自动格式校验和部分索引。

核心语法

CREATE TABLE 表名 (
    列名 JSON [NOT NULL]
);

-- 插入 JSON 值
INSERT INTO 表名 (列名) VALUES ('{"key": "value"}');

-- 查询 JSON 路径
SELECT JSON_EXTRACT(列名, '$.key') FROM 表名;

特性	说明
版本要求	MySQL 5.7.8+
存储格式	内部二进制（非文本），比 TEXT 更高效
格式校验	插入时自动校验 JSON 格式，非法 JSON 报错
最大大小	受 `max_allowed_packet` 限制（默认 4MB）
索引支持	不能直接索引 JSON 列，需通过虚拟列间接索引

演示数据准备

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    profile JSON
);

INSERT INTO employees (emp_name, dept, score, profile) VALUES
('大翔', '技术部', 100, '{"age": 28, "city": "广州", "skills": ["Java", "MySQL"], "remote": true}'),
('白歌', '技术部', NULL, '{"age": 25, "city": "深圳", "skills": ["Python"], "remote": false}');

当前 employees 表中的完整数据如下：

emp_id	emp_name	dept	score	profile
1	大翔	技术部	100	{"age": 28, "city": "广州", "skills": ["Java", "MySQL"], "remote": true}
2	白歌	技术部	NULL	{"age": 25, "city": "深圳", "skills": ["Python"], "remote": false}

SQL 示例

场景一：插入 JSON 值（自动格式校验）

当前数据状态：见上文 employees 表完整数据。

执行语句（合法 JSON）：

INSERT INTO employees (emp_name, dept, score, profile) VALUES
('孔蓝', '产品部', 88, '{"age": 26, "city": "北京", "skills": ["SQL", "Excel"]}');

操作后结果：插入成功。

执行语句（非法 JSON）：

INSERT INTO employees (emp_name, dept, score, profile) VALUES
('赵鸣', '运营部', 76, '{"age": 30, "city": "上海",}');

操作后结果：

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 33

结果解读：JSON 类型在插入时自动校验格式。最后一个键值对后面多了逗号，属于非法 JSON，直接报错。这比把 JSON 存为 TEXT 再靠程序校验更安全。

场景二：提取 JSON 字段值（JSON_EXTRACT）

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT
    emp_name,
    JSON_EXTRACT(profile, '$.age') AS age,
    JSON_EXTRACT(profile, '$.city') AS city,
    JSON_EXTRACT(profile, '$.remote') AS remote
FROM employees;

操作后结果：

emp_name	age	city	remote
大翔	28	"广州"	true
白歌	25	"深圳"	false

结果解读：JSON_EXTRACT(profile, '$.age') 从 profile JSON 文档中提取 age 键的值。路径语法：$ 表示根对象，. 后接键名。注意：提取的字符串值会带双引号（如 "广州"），如需纯字符串，可用 JSON_UNQUOTE() 或 ->> 运算符。

场景三：简化路径查询（-> 和 ->> 运算符）

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- ->  返回 JSON 格式（字符串带引号）
SELECT emp_name, profile->'$.age' AS age_json, profile->'$.city' AS city_json FROM employees;

-- ->> 返回纯文本（去除 JSON 引号）
SELECT emp_name, profile->>'$.age' AS age_text, profile->>'$.city' AS city_text FROM employees;

操作后结果：

-> 运算符：

emp_name	age_json	city_json
大翔	28	"广州"
白歌	25	"深圳"

->> 运算符：

emp_name	age_text	city_text
大翔	28	广州
白歌	25	深圳

结果解读：

-> 是 JSON_EXTRACT 的简写，返回 JSON 格式（数字不变，字符串带双引号）
->> 是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写，返回纯文本（去除 JSON 引号）
查询条件中通常用 ->>，因为不带引号的字符串更适合比较

场景四：查询 JSON 数组元素

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 提取数组第 1 个元素（索引从 0 开始）
SELECT emp_name, profile->>'$.skills[0]' AS first_skill FROM employees;

-- 查询包含 "Java" 技能的人
SELECT emp_name FROM employees WHERE JSON_CONTAINS(profile->>'$.skills', '"Java"');

操作后结果：

第一个技能：

emp_name	first_skill
大翔	Java
白歌	Python

包含 Java 的人：

emp_name
大翔

结果解读：

$.skills[0] 访问 skills 数组的第 1 个元素（索引从 0 开始）
JSON_CONTAINS(数组, '"Java"') 判断数组中是否包含指定值。注意：查询值必须是 JSON 格式字符串，所以 "Java" 要带双引号

场景五：更新 JSON 文档（JSON_SET / JSON_REPLACE / JSON_REMOVE）

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 添加/修改字段（不存在则添加，存在则修改）
UPDATE employees SET profile = JSON_SET(profile, '$.level', '高级') WHERE emp_id = 1;

-- 只修改已有字段（不存在则不操作）
UPDATE employees SET profile = JSON_REPLACE(profile, '$.age', 29) WHERE emp_id = 1;

-- 删除字段
UPDATE employees SET profile = JSON_REMOVE(profile, '$.remote') WHERE emp_id = 2;

操作后结果：

SELECT emp_name, profile FROM employees;

大翔的 profile：

emp_name	profile
大翔	{"age": 29, "city": "广州", "level": "高级", "remote": true, "skills": ["Java", "MySQL"]}

白歌的 profile：

emp_name	profile
白歌	{"age": 25, "city": "深圳", "skills": ["Python"]}

结果解读：

JSON_SET：添加 level 字段（原不存在），同时 JSON_REPLACE 修改了 age 从 28 到 29
JSON_REMOVE：删除了白歌的 remote 字段
这些函数不会修改 JSON 的其他部分，是局部更新

场景六：通过虚拟列给 JSON 加索引

当前数据状态：创建新表演示。

CREATE TABLE employees_v2 (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    profile JSON,
    -- 虚拟列：从 JSON 中提取 city
    city VARCHAR(20) AS (profile->>'$.city') VIRTUAL,
    -- 给虚拟列加索引
    INDEX idx_city (city)
);

INSERT INTO employees_v2 (emp_name, profile) VALUES
('大翔', '{"age": 28, "city": "广州"}'),
('白歌', '{"age": 25, "city": "深圳"}');

执行语句：

-- 利用虚拟列索引查询
SELECT emp_name, city FROM employees_v2 WHERE city = '广州';

-- 查看执行计划，确认使用了索引
EXPLAIN SELECT * FROM employees_v2 WHERE city = '广州';

操作后结果：

emp_name	city
大翔	广州

结果解读：

JSON 列本身不能直接创建索引
通过 GENERATED VIRTUAL 列从 JSON 中提取值，然后给虚拟列加索引
查询时直接用虚拟列 city，MySQL 会自动使用索引
虚拟列不占用实际存储空间（VIRTUAL），只在查询时计算；也可以用 STORED 持久化存储

常见误区

误区	正解
"`JSON` 类型在 5.7 所有版本都支持"	不是。需要MySQL 5.7.8+。5.7.7 及更早版本不支持。
"`JSON` 存的是纯文本字符串"	不是。内部以二进制格式存储，比 `TEXT` 更紧凑，查询更快。
"可以直接给 `JSON` 列创建索引"	不能直接索引。需要通过虚拟列（`GENERATED COLUMN`）间接索引。
"`JSON_EXTRACT` 返回的都是字符串"	不是。数字返回数字，布尔返回布尔，字符串返回带引号的 JSON 字符串。用 `->>` 或 `JSON_UNQUOTE` 去除引号。
"`JSON` 可以完全替代关系型表设计"	不能。`JSON` 适合存储结构灵活、查询简单的数据；复杂关联查询仍需关系型设计。
"`JSON` 更新会重写整个文档"	`JSON_SET`/`JSON_REPLACE`/`JSON_REMOVE` 是局部更新，但底层实现可能涉及文档重写，性能不如普通列更新。

面试考点

Q：MySQL 5.7 的 JSON 和 TEXT 存 JSON 字符串的区别？

JSON 类型自动校验格式，非法 JSON 插入报错；TEXT 不校验。2. JSON 内部二进制存储，更紧凑；TEXT 纯文本存储。3. JSON 支持路径查询（JSON_EXTRACT、->、->>）；TEXT 需要程序解析。4. JSON 可通过虚拟列间接索引；TEXT 不能直接索引。

Q：如何给 JSON 列加索引？

不能直接加索引。需要创建 GENERATED VIRTUAL 列从 JSON 中提取值，然后给虚拟列加索引。例如：city VARCHAR(20) AS (profile->>'$.city') VIRTUAL, INDEX idx_city (city)。

Q：-> 和 ->> 的区别？

-> 是 JSON_EXTRACT 的简写，返回 JSON 格式（字符串带双引号）；->> 是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写，返回纯文本（去除 JSON 引号）。查询条件中通常用 ->>。

Q：JSON 类型适合什么场景？不适合什么场景？

适合：配置项、用户画像、商品属性等结构灵活、查询简单的半结构化数据。不适合：需要频繁更新单个字段、复杂关联查询、大量聚合统计的场景。这些场景用传统关系型列设计更高效。

Q：MySQL 5.7 的 JSON 和 MongoDB 的区别？

MySQL 5.7 的 JSON 是关系型数据库对文档存储的扩展，支持 SQL 查询和事务，但 JSON 操作能力有限；MongoDB 是原生文档数据库，JSON 操作更丰富（如聚合管道、全文检索），但不支持跨文档事务（5.7 时代）。

小结

JSON 类型是 MySQL 5.7.8+ 引入的，用于存储 JSON 文档，自动校验格式
内部二进制存储，支持路径查询（JSON_EXTRACT、->、->>）
支持局部更新（JSON_SET、JSON_REPLACE、JSON_REMOVE）
不能直接索引，需通过 GENERATED VIRTUAL 虚拟列间接索引
适合半结构化数据（配置、属性、标签），不适合复杂关联和频繁更新的场景

下一章引子：学会了基础数据类型，接下来了解 MySQL 中用于文本搜索的全文检索函数——当 LIKE '%keyword%' 太慢时，全文索引是更好的选择。