MySQL JSON 数据类型：存取、查询与索引速查

摘要

• MySQL 从 5.7.8 起提供原生 JSON 类型：写入时自动校验、内部二进制存储，查询可用路径表达式与一组 JSON_* 函数
• 本文按日常用法整理：建表写入、路径取值（-> / ->>）、增删改键、生成列索引、多值索引，以及常见坑
• 适合日志 / 埋点 / 半结构化扩展字段；强一致业务主表仍建议用普通列
• Shell 侧批量导入见 MySQL Shell（mysqlsh）使用手册：常用功能速查 的 util.importJson；官方文档：The JSON Data Type、JSON Functions

1. 为什么用 JSON 类型

相对把 JSON 塞进 VARCHAR / TEXT：

点	JSON 列	文本列自己存 JSON 字符串
合法性	写入即校验，非法文档直接报错	不校验
存储	归一化后的二进制格式，按路径访问更高效	纯文本，每次解析
函数	完整 JSON_* / -> / ->>	只能当字符串处理
空间	通常更紧凑（键去重、类型编码）	原样存

版本要点：

• 5.7.8：原生 JSON 类型 + 基础函数

• 5.7.9：column->path 简写

• 5.7.13：column->>path（带自动 UNQUOTE）

• 8.0：JSON_TABLE、部分更新优化、多值索引（8.0.17+）等

JSON 不是「万能表结构」。常查、常过滤、要强约束的字段，优先做成普通列；JSON 更适合扩展属性、事件载荷、配置快照。

2. 建表与写入

CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doc JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 字面量（必须是合法 JSON）
INSERT INTO events (doc) VALUES
  ('{"cid": 1001, "cname": "MySQL 基础", "user_id": 20001, "cstatus": "open", "tags": ["mysql", "dba"]}'),
  ('{"cid": 1002, "cname": "MySQL 进阶", "user_id": 20002, "cstatus": "open", "tags": ["mysql"]}');

-- 用函数构造（推荐，少踩引号坑）
INSERT INTO events (doc) VALUES (
  JSON_OBJECT(
    'cid', 1003,
    'cname', 'InnoDB 内核',
    'user_id', 20001,
    'cstatus', 'closed',
    'tags', JSON_ARRAY('innodb', 'kernel')
  )
);

非法示例（会失败）：

INSERT INTO events (doc) VALUES ('{cid: 1}');  -- 键必须双引号等，非合法 JSON

3. 读取：路径表达式

路径以 $ 表示文档根，. 取对象键，[n] 取数组下标。

SELECT
  id,
  doc->'$.cname'              AS cname_quoted,  -- "MySQL 基础"（JSON 字符串，带引号）
  doc->>'$.cname'             AS cname,         -- MySQL 基础（普通字符串）
  doc->>'$.cid'               AS cid,
  CAST(doc->>'$.user_id' AS UNSIGNED) AS user_id,
  doc->'$.tags[0]'            AS first_tag,
  JSON_EXTRACT(doc, '$.tags') AS tags
FROM events;

写法	含义
doc->'$.a' / JSON_EXTRACT(doc, '$.a')	取出仍是 JSON 值
doc->>'$.a' / JSON_UNQUOTE(JSON_EXTRACT(...))	取出并去掉 JSON 引号，得到 SQL 字符串/数字文本
doc->'$.arr[*]'	数组全部元素（部分函数支持通配）

过滤：

SELECT id, doc->>'$.cname'
FROM events
WHERE doc->>'$.cstatus' = 'open'
  AND CAST(doc->>'$.user_id' AS UNSIGNED) = 20001;

对数字做比较时，->> 得到的是字符串形态，常用 CAST(... AS UNSIGNED/DECIMAL)，或直接用 JSON_EXTRACT 与 CAST(... AS JSON) 比较，避免隐式转换踩坑。

4. 修改文档内容

JSON 列整列替换也可以，但日常多用「按路径改」：

-- 设置 / 覆盖（路径不存在则创建）
UPDATE events
SET doc = JSON_SET(doc, '$.cstatus', 'closed', '$.level', 2)
WHERE id = 1;

-- 仅当路径不存在时写入
UPDATE events
SET doc = JSON_INSERT(doc, '$.owner', 'alice')
WHERE id = 1;

-- 仅当路径已存在时替换
UPDATE events
SET doc = JSON_REPLACE(doc, '$.cname', 'MySQL 基础（修订）')
WHERE id = 1;

-- 删除键或数组元素
UPDATE events
SET doc = JSON_REMOVE(doc, '$.level', '$.tags[1]')
WHERE id = 1;

-- 数组追加
UPDATE events
SET doc = JSON_ARRAY_APPEND(doc, '$.tags', 'json')
WHERE id = 1;

合并对象（8.0 常用 JSON_MERGE_PATCH，RFC 7396 语义）：

UPDATE events
SET doc = JSON_MERGE_PATCH(doc, '{"cstatus":"draft","extra":{"src":"app"}}')
WHERE id = 1;

5. 构造、校验与探查

SELECT JSON_OBJECT('a', 1, 'b', TRUE, 'c', NULL);
SELECT JSON_ARRAY(1, 'x', JSON_OBJECT('k', 2));
SELECT JSON_VALID('{"a":1}');          -- 1
SELECT JSON_TYPE(doc), JSON_DEPTH(doc), JSON_LENGTH(doc), JSON_KEYS(doc)
FROM events WHERE id = 1;

-- 是否包含某路径 / 某值
SELECT JSON_CONTAINS_PATH(doc, 'one', '$.tags');
SELECT JSON_CONTAINS(doc, '"mysql"', '$.tags');
SELECT JSON_SEARCH(doc, 'one', 'MySQL 基础');

JSON_TABLE（8.0+）把 JSON 展成关系行，方便 JOIN：

SELECT e.id, t.tag
FROM events e,
JSON_TABLE(
  e.doc,
  '$.tags[*]' COLUMNS (tag VARCHAR(32) PATH '$')
) AS t
WHERE e.id = 1;

6. 索引：生成列与多值索引

JSON 列本身不能直接建普通 BTree 索引；把常查路径抽成生成列再索引。

6.1 生成列（最常用）

ALTER TABLE events
  ADD COLUMN cstatus VARCHAR(10)
    GENERATED ALWAYS AS (doc->>'$.cstatus') STORED,
  ADD COLUMN user_id BIGINT UNSIGNED
    GENERATED ALWAYS AS (CAST(doc->>'$.user_id' AS UNSIGNED)) STORED,
  ADD INDEX idx_events_cstatus (cstatus),
  ADD INDEX idx_events_user (user_id);

-- 之后像普通列一样查
SELECT id, doc->>'$.cname'
FROM events
WHERE cstatus = 'open' AND user_id = 20001;

STORED 占空间但读路径省；VIRTUAL（默认）不落盘，适合写多读少、表达式便宜的场景。

6.2 多值索引（8.0.17+，数组元素）

-- 给 tags 数组建多值索引
ALTER TABLE events
  ADD INDEX idx_events_tags ((CAST(doc->'$.tags' AS CHAR(32) ARRAY)));

SELECT id FROM events
WHERE JSON_CONTAINS(doc->'$.tags', '"mysql"');
-- 或
SELECT id FROM events
WHERE 'mysql' MEMBER OF (doc->'$.tags');

7. 与 Document Store / util.importJson

Document Store 的 Collection、以及 util.importJson 导入关系表时，底层往往就是「一张带 doc JSON 列（再加内部键）的表」。

关系表导入要求：

CREATE TABLE events_import (
  id INT NOT NULL AUTO_INCREMENT,
  doc JSON,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

// mysqlsh，必须 X Protocol
util.importJson("/path/events.json", {
  schema: "appdb",
  table: "events_import"   // 默认写入 doc 列
})

源文件常用 NDJSON（一行一个文档）。不会把 cid/cname 自动映射成普通列；要拆列需自行：

INSERT INTO course_1 (cid, cname, user_id, cstatus)
SELECT
  CAST(doc->>'$.cid' AS UNSIGNED),
  doc->>'$.cname',
  CAST(doc->>'$.user_id' AS UNSIGNED),
  doc->>'$.cstatus'
FROM events_import;

更多见 MySQL Shell（mysqlsh）使用手册：常用功能速查。

8. 限制与踩坑

1. 不能当普通索引列：路径查询要生成列或多值索引，否则易全表扫描。

2. -> 与 ->> 别混：比较字符串条件时优先 ->>；需要保留 JSON 类型时用 ->。

3. 键名大小写：JSON 对象键比较区分大小写；cname 与 Cname 是不同键。

4. NULL 语义：SQL NULL 与 JSON null 不同；JSON_EXTRACT 路径不存在时返回 SQL NULL。

5. 整列更新成本：频繁大文档原地改，注意 binlog / undo；能拆热字段就拆。

6. 最大大小：受 max_allowed_packet 等限制；超大文档不适合硬塞一列。

7. 字符集：JSON 列本身不设字符集；字符串值按 utf8mb4 处理相关规则存储/比较。

8. 与业务表混用：同 schema 下普通表名与 Collection 名不能冲突（importJson 时常见报错）。

9. 选型建议

场景	建议
订单金额、用户状态等强约束字段	普通列 + 约束/索引
埋点事件、Webhook 原文、设备上报	JSON 列存整包
主表固定列 + 少量扩展属性	主列 + attrs JSON
大量按数组标签过滤	JSON + 多值索引，或拆关联表
纯文档 CRUD、X DevAPI	Collection / Document Store

参考

• The JSON Data Type

• JSON Functions

• JSON Path Syntax

• Secondary Indexes and Generated Columns

• 相关博文：MySQL Shell（mysqlsh）使用手册：常用功能速查、MySql索引、MySql--导入与导出