10. JSON、枚举与二进制类型

前面几节讲的都是”传统”类型。这一节讲几个进阶类型：JSON 存半结构化数据、ENUM 存固定选项、BLOB 存二进制大对象。它们各有专门场景，用对了能让设计更优雅。

10.1 JSON 类型：存半结构化数据

JSON（JavaScript Object Notation） 是一种轻量的数据交换格式，到处都在用。从 MySQL 5.7.8 起，MySQL 原生支持 JSON 类型，8.0 之后功能更完整。到 26.7，JSON 已经是处理半结构化数据的主力。

为什么用 JSON 类型

以前存 JSON 都是塞进 TEXT 列，当作字符串存。这样有两个问题：

1. 不校验格式：存进去的可能不是合法 JSON，取出来解析就崩；
2. 查询困难：要从 JSON 里取某个字段，得把整个字符串读出来再解析。

JSON 类型解决了这两点：

• 自动校验：插入时 MySQL 检查是不是合法 JSON，不合法直接报错；
• 高效查询：能用路径表达式直接取出 JSON 内部的字段，不用读全部。

建表和插入

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    profile JSON
);

INSERT INTO users(username, profile) VALUES
('zhangsan', '{"age": 25, "city": "北京", "tags": ["vip", "active"]}'),
('lisi', '{"age": 30, "city": "上海", "tags": ["new"]}');

也能用函数构造 JSON，效果一样：

INSERT INTO users(username, profile) VALUES
('wangwu', JSON_OBJECT('age', 28, 'city', '广州', 'tags', JSON_ARRAY('vip')));

JSON_OBJECT() 构造对象，JSON_ARRAY() 构造数组。

10.2 查询 JSON：路径表达式

查 JSON 内部的值，用 JSON_EXTRACT() 函数或更简洁的 -> 操作符。

-- 取 profile 里的 age 字段
SELECT username, JSON_EXTRACT(profile, '$.age') FROM users;

-- 等价的简写：-> 操作符
SELECT username, profile->'$.age' FROM users;

-- 取数组里的元素（tags 第一个）
SELECT username, profile->'$.tags[0]' FROM users;

$.age 这种就是路径表达式：

• $ 表示 JSON 文档根；
• $.age 表示根对象的 age 字段；
• $.tags[0] 表示 tags 数组的第一个元素；
• $.tags[*] 表示 tags 数组所有元素。

-> 和 ->> 的区别

• ->：取出的值带引号，如 "25"；
• ->>：取出的值去掉引号，如 25（更干净，推荐用于显示）。

mysql> SELECT profile->'$.city', profile->>'$.city' FROM users WHERE id=1;
+------------------+-------------------+
| profile->'$.city' | profile->>'$.city' |
+------------------+-------------------+
| "北京"           | 北京              |
+------------------+-------------------+

10.3 修改 JSON

JSON 列不能像普通字段那样 SET age = 26，要用专门的函数修改：

-- 修改某个字段（不存在会新增）
UPDATE users SET profile = JSON_SET(profile, '$.age', 26) WHERE id = 1;

-- 插入新字段（已存在则报错）
UPDATE users SET profile = JSON_INSERT(profile, '$.vip_level', 3) WHERE id = 1;

-- 替换字段（不存在则不操作）
UPDATE users SET profile = JSON_REPLACE(profile, '$.city', '深圳') WHERE id = 1;

-- 删除字段
UPDATE users SET profile = JSON_REMOVE(profile, '$.tags[0]') WHERE id = 1;

四个函数各有用途：

函数	作用
JSON_SET	有则改、无则加（最常用）
JSON_INSERT	只加不改
JSON_REPLACE	只改不加
JSON_REMOVE	删除指定路径

10.4 JSON 查询条件

在 WHERE 里用 JSON 字段做条件：

-- 找 age 大于 28 的用户
SELECT username FROM users WHERE profile->'$.age' > 28;

-- 找 city 是北京的用户
SELECT username FROM users WHERE profile->>'$.city' = '北京';

-- 找 tags 里包含 'vip' 的用户
SELECT username FROM users
WHERE JSON_CONTAINS(profile->'$.tags', '"vip"');

JSON_CONTAINS() 判断 JSON 是否包含某个值，注意被包含的值也要是合法 JSON（字符串要带引号）。

10.5 JSON 索引优化

JSON 列本身不能直接建索引。要加速 JSON 查询，有两个办法：

方法一：生成列 + 索引

把常用 JSON 字段提取成生成列，再给它建索引：

ALTER TABLE users
ADD COLUMN city VARCHAR(50)
GENERATED ALWAYS AS (profile->>'$.city') STORED,
ADD INDEX idx_city (city);

之后查 WHERE city = '北京' 就能走索引了。生成列后面有专门一节讲。

方法二：多值索引（8.0.17+）

对 JSON 数组建多值索引：

ALTER TABLE users
ADD INDEX idx_tags ((CAST(profile->'$.tags' AS CHAR(20) ARRAY)));

这样查 JSON_CONTAINS 或 MEMBER OF 能走索引。

10.6 ENUM：枚举类型

ENUM（枚举） 用来存”只能从固定几个值里选一个”的字段。定义时列出所有可选值，存的时候只能存这些值之一。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2),
    status ENUM('pending','paid','shipped','done','cancelled') NOT NULL DEFAULT 'pending'
);

INSERT INTO orders(amount, status) VALUES(99.50, 'paid');

ENUM 的底层

ENUM 表面是字符串，底层存的是整数索引。列表里的值按定义顺序映射成 1、2、3…：

-- 'pending'=1, 'paid'=2, 'shipped'=3, 'done'=4, 'cancelled'=5
INSERT INTO orders(amount, status) VALUES(50, 2);  -- 等价于 'paid'

所以 ENUM 比存字符串省空间（一个枚举值只占 1~2 字节）。

ENUM 的排序

ENUM 排序按内部索引，不按字母：

SELECT status FROM orders ORDER BY status;
-- 顺序是 pending, paid, shipped, done, cancelled（定义顺序）

ENUM 的注意事项

• 加新值要改表结构：ALTER TABLE ... MODIFY COLUMN ... ENUM(...)，大表上代价高；
• 跨数据库不通用：ENUM 是 MySQL 特性，迁库可能麻烦；
• 非法值：严格模式下插不存在的值会报错，非严格模式存成空字符串 ''（索引 0）。

10.7 SET：集合类型

SET 和 ENUM 类似，但能选多个值。适合”一个字段可以同时属于多个分类”的场景。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    tags SET('tech','life','travel','food','music')
);

INSERT INTO articles(title, tags) VALUES('游记', 'travel,food');
INSERT INTO articles(title, tags) VALUES('技术文章', 'tech');

tags 可以同时是 travel 和 food。

底层 SET 用位图存：每个选项占一位，最多 64 个成员。查询用 FIND_IN_SET：

SELECT title FROM articles WHERE FIND_IN_SET('travel', tags);

10.8 BINARY 和 VARBINARY：二进制字符串

BINARY(M) 和 VARBINARY(M) 是 CHAR 和 VARCHAR 的二进制版本：

• 存的是字节串，不是字符；
• 没有字符集，不涉及时排序规则；
• 比较时按字节值大小比较，区分大小写。

CREATE TABLE tokens (
    id INT PRIMARY KEY,
    token BINARY(32)  -- 存 32 字节的哈希值
);

适用场景：存哈希值、加密后的密文、UUID 的二进制形式这类”纯字节”数据。

10.9 BLOB：二进制大对象

BLOB（Binary Large Object） 用来存大的二进制数据，比如图片、音频、视频、文件。有四个档位：

类型	最大长度	用途
TINYBLOB	255 字节	极小二进制
BLOB	65,535 字节	普通二进制，约 64 KB
MEDIUMBLOB	16 MB	中等二进制
LONGBLOB	4 GB	大二进制

CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    image_data LONGBLOB
);

BLOB 的存储注意

• BLOB 内容存在行外，行内只存指针；
• 插入二进制数据要用程序（Python/Java/PHP）读取文件后传入，命令行里直接写不方便；
• LOAD_FILE() 函数能读文件，但受 secure_file_priv 变量限制路径：

-- 查看允许读文件的目录
SELECT @@secure_file_priv;

-- 从允许的目录读图片存进去
INSERT INTO images(title, image_data)
VALUES('logo', LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/logo.png'));

10.10 BLOB 和 TEXT 的关系

BLOB 和 TEXT 是”兄弟”：存储结构一样（都存行外），区别在：

对比项	BLOB	TEXT
内容	二进制字节	字符
字符集	无	有
排序	按字节	按字符集排序规则
大小比较	区分大小写	取决于排序规则

简单记：文本用 TEXT，字节用 BLOB。

10.11 各类型选型建议

这一节涉及类型多，归纳一下：

1. 半结构化、字段会变：用 JSON，配合生成列建索引；
2. 选项固定且少（状态、性别）：用 ENUM，省空间、有约束；
3. 多标签、固定少：用 SET（但更推荐关联表）；
4. 哈希、密钥、UUID 字节：用 BINARY/VARBINARY；
5. 小图片、证书、缩略图：用 BLOB 系列，大文件别入库；
6. JSON 别滥用：能普通列就普通列，JSON 是补充不是替代。

10.12 小结

这一节讲完了进阶数据类型：

• JSON 类型自动校验格式，用 -> / ->> 或 JSON_EXTRACT 查内部字段；
• 修改 JSON 用 JSON_SET/JSON_INSERT/JSON_REPLACE/JSON_REMOVE；
• JSON 不能直接建索引，靠生成列或多值索引加速；
• ENUM 存固定选项，底层是整数，按定义顺序排序；
• SET 存多个选项，位图存储；
• BINARY/VARBINARY 存字节串，无字符集；
• BLOB 四档存二进制大对象，大文件别入库。

下一节讲字符集与排序规则，理解 utf8mb4 背后的门道。