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 作为普通文本存储，需通过 JSON 函数解析
函数	完整 JSON_* / -> / ->>	只能当字符串处理
空间	大致与文本相当，另有二进制元数据开销	原始文本存储

版本要点（以实战边界为主）：

功能	版本
原生 JSON 类型 + 基础函数	5.7.8+
column->path	5.7.9+
column->>path	5.7.13+
JSON_TABLE()	8.0.4+
多值索引	8.0.17+
JSON_VALUE()	8.0.21+
Row-based 的 PARTIAL_JSON binlog	8.0+（8.4 仍常用）

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 标量并去掉 JSON 字符串引号；结果通常以字符串形式参与 SQL 表达式（不会自动变成 INT）
doc->'$.arr[*]'	数组全部元素（部分函数支持通配）

过滤：

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

对数字做比较时，->> 得到的是字符串形式，建议按业务类型显式 CAST，例如 CAST(... AS UNSIGNED) 或 CAST(... AS DECIMAL(...))。

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.4+）把 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 列不能像普通标量列那样，直接按整份文档内容建立普通的单值 B-Tree 索引。常查路径可用生成列 / 函数索引；JSON 数组元素可用多值索引（8.0.17+）。

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+，数组元素）

多值索引针对 JSON 数组元素建索引，不是给整个 JSON 文档做通用索引。优化器主要用于：

• 'x' MEMBER OF (doc->'$.tags')

• JSON_CONTAINS(doc->'$.tags', ...)

• JSON_OVERLAPS(doc->'$.tags', ...)

-- 给 tags 数组建多值索引（CAST(... AS ... ARRAY) 为官方要求写法）
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');

建了索引 ≠ 优化器一定选用，应用 EXPLAIN / EXPLAIN ANALYZE 核对：

EXPLAIN
SELECT id FROM events
WHERE 'mysql' MEMBER OF (doc->'$.tags');

7. 与 Document Store / util.importJson

MySQL Document Store 的 Collection 建立在 MySQL 表与 JSON 数据之上；util.importJson() 还可把 JSON 文档导入普通关系表的指定 JSON 列（默认列名 doc）。二者相关，但 API 上要分开写：collection 与 table / tableColumn。

关系表导入要求：

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 列；也可用 tableColumn 指定
})

源文件可以是 NDJSON（一行一个文档），也可以是其他受支持的标准 JSON 文档格式（含 MongoDB Extended JSON 等，视文件与选项而定）。不会把 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 不同。路径不存在时通常返回 SQL NULL；路径存在且值为 JSON null 时，返回的是 JSON null。例如 JSON_EXTRACT('{"a":null}','$.a') → JSON null；JSON_EXTRACT('{"a":1}','$.b') → SQL NULL。

5. 大文档频繁更新：JSON_SET / JSON_REPLACE / JSON_REMOVE 可做 JSON 部分更新，但仍需关注存储、Undo 与 binlog 成本。Row-based 复制下可设 binlog_row_value_options=PARTIAL_JSON，符合条件时只记录变更片段，减小 binlog。热字段能拆普通列就拆。

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

7. 字符集与排序规则：JSON 列本身不指定字符集 / 排序规则。JSON 上下文中的字符串使用 utf8mb4 + utf8mb4_bin，因此 JSON 字符串比较默认区分大小写（如 JSON_ARRAY('x') = JSON_ARRAY('X') 为 0）。

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

• Replication of JSON Documents

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