很多业务系统在设计单据表时，把明细行整体存进一个 JSON 列（比如 order_header.detail_json），主表只留单号、客户、状态等字段。这样做写入简单、结构灵活，但一到对账、报表、按商品汇总金额的环节就卡住：MySQL 不能直接对 JSON 数组里的每个元素做聚合。开发常见的土办法是把数据全捞到应用层用 Java/Python 循环展开，再拼 SQL，数据量大时内存和网络都扛不住。
MySQL 8.0.4 起提供的内置表函数 JSON_TABLE，可以在 SQL 层把 JSON 数组「横向展开」成多行，随后像普通表一样 JOIN、WHERE、GROUP BY。本文用一个真实的采购单据场景，从建表、造数据开始，一步步拆行、关联主表、按商品和客户统计金额，并给出性能陷阱与常见报错的处理方式。
一、场景与表结构设计
假设有两张表：主表 order_header 存单据头，明细表并不存在，明细以 JSON 数组形式放在主表的 detail_json 列中。每条明细包含商品编码、名称、单价、数量、折扣。
CREATE TABLE order_header (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
customer_id INT NOT NULL,
status TINYINT NOT NULL DEFAULT 1 COMMENT '1待审核 2已审核 3已作废',
detail_json JSON NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE customer (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;明细 JSON 的典型形态如下，注意 qty 是整数、price 是两位小数、discount 是 0~1 的折扣率：
[
{"sku":"A1001","name":"螺丝 M6","price":0.50,"qty":200,"discount":0.95},
{"sku":"A1002","name":"垫片 M6","price":0.10,"qty":500,"discount":1.00},
{"sku":"B2001","name":"扳手 8寸","price":25.00,"qty":10,"discount":0.90}
]造一点测试数据，方便后面对比结果：
INSERT INTO customer (id, name) VALUES
(1, '华东五金'), (2, '西南机电'), (3, '北方工具');
INSERT INTO order_header (order_no, customer_id, status, detail_json) VALUES
('PO20240501001', 1, 2, '[
{"sku":"A1001","name":"螺丝 M6","price":0.50,"qty":200,"discount":0.95},
{"sku":"A1002","name":"垫片 M6","price":0.10,"qty":500,"discount":1.00},
{"sku":"B2001","name":"扳手 8寸","price":25.00,"qty":10,"discount":0.90}]'),
('PO20240501002', 2, 2, '[
{"sku":"A1001","name":"螺丝 M6","price":0.50,"qty":1000,"discount":0.92},
{"sku":"C3001","name":"卷尺 5m","price":12.00,"qty":30,"discount":1.00}]'),
('PO20240501003', 3, 1, '[
{"sku":"B2001","name":"扳手 8寸","price":25.00,"qty":4,"discount":1.00}]'),
('PO20240501004', 1, 3, '[
{"sku":"A1002","name":"垫片 M6","price":0.10,"qty":2000,"discount":0.98}]');目标有三个：把每张单据的明细拆成多行；关联 customer 拿到客户名；按商品维度、客户维度分别统计实付金额（price * qty * discount）。
二、JSON_TABLE 的基本语法与列定义
语法骨架是 JSON_TABLE(json_doc, path COLUMNS(...)) [AS] alias。第一个参数是 JSON 值，第二个是行路径（必须返回 JSON 数组），COLUMNS 子句里逐列声明「列名 类型 PATH ‘$.字段’」。
SELECT h.order_no, d.sku, d.name, d.price, d.qty, d.discount
FROM order_header h,
JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
name VARCHAR(64) PATH '$.name',
price DECIMAL(10,2) PATH '$.price',
qty INT PATH '$.qty',
discount DECIMAL(4,2) PATH '$.discount'
)
) AS d
WHERE h.status = 2;几个必须记住的细节：
- 行路径必须用
$[*]（或$[0]这类单元素路径）。如果写成$，得到的是整个数组作为一行，PATH '$.sku'全部为 NULL。 - 列必须显式声明类型。JSON 里的
0.50如果不声明为 DECIMAL，用VARCHAR接会得到字符串'0.5'（末尾零被丢弃），后续乘法虽然会隐式转换，但精度和索引都用不上。金额列一律DECIMAL(p,2)。 - 隐式连接要写
FROM h, JSON_TABLE(h.detail_json, ...) d。JSON_TABLE 是「表函数」，不是子查询，它可以直接引用左侧表的列，等价于 LATERAL 语义。 - 别名不可省。列引用时写成
d.sku，若别名写成AS jt就统一用jt.sku。
执行上面的语句，PO20240501001 会展开成 3 行，PO20240501002 展开成 2 行，以此类推，共 8 行明细。
2.1 嵌套数组的处理
若明细里还有子数组（比如每个商品带多个批号或库位），用 NESTED PATH 继续展开，嵌套层会与上一层做笛卡尔展开：
SELECT h.order_no, d.sku, b.batch_no, b.loc
FROM order_header h,
JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
qty INT PATH '$.qty',
NESTED PATH '$.batches[*]' COLUMNS (
batch_no VARCHAR(32) PATH '$.batch_no',
loc VARCHAR(32) PATH '$.loc'
)
)
) AS d
JOIN order_header h2 ON h2.id = h.id
WHERE h.status = 2;这条语句在上面的数据上会返回 0 行，因为示例数据里没有 batches 字段；一旦某条明细包含 batches 数组，该明细会按批次数量裂成多行，而 sku、qty 会随之复制。做金额统计时，如果批号不影响金额，千万别用 NESTED PATH 去 JOIN，否则金额被重复累加（这是最常见的对账差错来源之一）。
三、关联主表和客户表做金额统计
把上面拆行的结果当成一张临时明细表，JOIN order_header（获取单据头字段）和 customer（获取客户名）即可。注意 order_header h 本身已经在 FROM 里，只要补一个 customer 的连接：
SELECT
h.order_no,
c.name AS customer_name,
d.sku,
d.name AS item_name,
d.price,
d.qty,
d.discount,
ROUND(d.price * d.qty * d.discount, 2) AS line_amount
FROM customer c
JOIN order_header h ON h.customer_id = c.id
JOIN JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
name VARCHAR(64) PATH '$.name',
price DECIMAL(10,2) PATH '$.price',
qty INT PATH '$.qty',
discount DECIMAL(4,2) PATH '$.discount'
)
) AS d ON TRUE
WHERE h.status = 2
ORDER BY h.order_no, d.sku;JOIN ... ON TRUE 是 JSON_TABLE 与前置表连接的惯用写法（等价于逗号连接，但可读性更好，也方便把连接条件写在同一处）。执行结果为：
| order_no | customer_name | sku | line_amount |
|---|---|---|---|
| PO20240501001 | 华东五金 | A1001 | 95.00 |
| PO20240501001 | 华东五金 | A1002 | 50.00 |
| PO20240501001 | 华东五金 | B2001 | 225.00 |
| PO20240501002 | 西南机电 | A1001 | 460.00 |
| PO20240501002 | 西南机电 | C3001 | 360.00 |
PO20240501003 是待审核（status=1）、PO20240501004 已作废（status=3），被 WHERE 过滤掉，符合业务口径。
3.1 按商品维度汇总
SELECT
d.sku,
MAX(d.name) AS item_name,
SUM(d.qty) AS total_qty,
SUM(ROUND(d.price * d.qty * d.discount, 2)) AS total_amount,
COUNT(DISTINCT h.id) AS order_cnt
FROM order_header h
JOIN JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
name VARCHAR(64) PATH '$.name',
price DECIMAL(10,2) PATH '$.price',
qty INT PATH '$.qty',
discount DECIMAL(4,2) PATH '$.discount'
)
) AS d ON TRUE
WHERE h.status = 2
GROUP BY d.sku
ORDER BY total_amount DESC;结果中 A1001 的 total_qty 为 1200、total_amount 为 555.00，来自两张单据的合并；order_cnt 用 COUNT(DISTINCT h.id) 统计出现次数，避免同一单据多次计入。
这里 MAX(d.name) 是一个小技巧：同一 SKU 名称理论上一致，用聚合函数包一层即可满足 ONLY_FULL_GROUP_BY 严格的 SQL 模式，不必把它写进 GROUP BY。
3.2 按客户维度汇总并做占比
WITH line AS (
SELECT
h.customer_id,
h.id AS order_id,
ROUND(d.price * d.qty * d.discount, 2) AS line_amount
FROM order_header h
JOIN JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
price DECIMAL(10,2) PATH '$.price',
qty INT PATH '$.qty',
discount DECIMAL(4,2) PATH '$.discount'
)
) AS d ON TRUE
WHERE h.status = 2
)
SELECT
c.name,
SUM(l.line_amount) AS amount,
ROUND(SUM(l.line_amount) * 100 / SUM(SUM(l.line_amount)) OVER (), 2) AS pct
FROM line l
JOIN customer c ON c.id = l.customer_id
GROUP BY c.id, c.name
ORDER BY amount DESC;把拆行逻辑抽进 CTE（MySQL 8.0 支持 WITH）可以避免在多个统计口径里重复写一长串 COLUMNS 定义。窗口函数 SUM(...) OVER () 与聚合函数嵌套时，外层 SUM 是分组求和，内层 OVER () 是全量求和，这是标准写法，MySQL 8.0.22 及以上版本均可正常执行。
四、把 JSON_TABLE 封装成视图
若团队里多个报表都要用明细，建议建视图固化拆行逻辑，业务 SQL 只需 SELECT * FROM v_order_item。但 MySQL 视图有个硬限制：视图定义里不能引用会话变量或某些表函数形式，直接写 JSON_TABLE 在部分小版本上会报 ERROR 1351 (HY000): View's SELECT contains a variable or parameter。稳妥做法是：先用 CTE 包一层再建视图，或在应用层用 CREATE TABLE ... AS SELECT 落地。
CREATE OR REPLACE VIEW v_order_item AS
SELECT
h.id AS order_id,
h.order_no,
h.customer_id,
h.status,
h.created_at,
d.sku,
d.name AS item_name,
d.price,
d.qty,
d.discount,
ROUND(d.price * d.qty * d.discount, 2) AS line_amount
FROM order_header h
JOIN JSON_TABLE(
h.detail_json,
'$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku',
name VARCHAR(64) PATH '$.name',
price DECIMAL(10,2) PATH '$.price',
qty INT PATH '$.qty',
discount DECIMAL(4,2) PATH '$.discount'
)
) AS d ON TRUE;在 MySQL 8.0.21 上执行这条语句会失败，报错信息形如：
ERROR 1351 (HY000): View's SELECT contains a variable or parameter解决办法有两条：升级到 8.0.30 及以上（该版本对视图中的 JSON_TABLE 支持更完善），或者退而求其次，用 CREATE TABLE v_order_item AS SELECT ... 建物化表，再配一个定时任务（事件或外部调度）增量刷新。生产上做报表底座，物化表反而更可控，因为可以在 sku、customer_id 上建索引，而视图每次查询都要全量展开 JSON。
五、性能与正确性的实战陷阱
5.1 无法走 JSON 内部字段的索引
JSON_TABLE 的拆行发生在执行阶段，WHERE d.sku = 'A1001' 这种条件下推到 JSON 内部是做不到的，MySQL 会把 status 过滤后的每一行 JSON 都解析一遍。表里 10 万行单据、每行 5 条明细，就是 50 万次 JSON 解析。可用的优化手段：
- 为高频过滤字段建生成列 + 索引。例如
ALTER TABLE order_header ADD COLUMN sku_list VARCHAR(512) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(detail_json,'$[*].sku'))) VIRTUAL, ADD INDEX idx_sku_list (sku_list(64));。注意生成列上是 JSON 数组文本，只能做前缀匹配，等值查询仍不理想。 - 更好的方式是写一张真正的明细表，在应用层双写；JSON 列只作为快照保留。JSON_TABLE 适合「事后分析」和「轻量查询」，不适合高频 OLTP 明细检索。
- 用
EXPLAIN ANALYZE（8.0.18+）确认JSON_TABLE那一步的rows估算是否离谱。若估算偏差大，可加/*+ SET_VAR(optimizer_switch='...') */之外的提示有限，通常只能从过滤前置入手。
5.2 NULL 与类型不匹配
当某条明细缺少字段，列值为 NULL，参与 price * qty 运算后整行为 NULL，SUM 会忽略它——金额悄悄变少。用 COALESCE 兜底：
SELECT
h.order_no,
d.sku,
ROUND(COALESCE(d.price,0) * COALESCE(d.qty,0) * COALESCE(d.discount,1), 2) AS line_amount
FROM order_header h
JOIN JSON_TABLE(
h.detail_json, '$[*]' COLUMNS (
sku VARCHAR(32) PATH '$.sku' DEFAULT '0' ON EMPTY,
price DECIMAL(10,2) PATH '$.price' DEFAULT '0' ON EMPTY,
qty INT PATH '$.qty' DEFAULT '0' ON EMPTY,
discount DECIMAL(4,2) PATH '$.discount' DEFAULT '1' ON EMPTY
)
) AS d ON TRUE;DEFAULT ... ON EMPTY 处理「路径存在但值为 JSON null/空」的情况；DEFAULT ... ON ERROR 处理类型转换失败的情况（比如 price 里存了 "abc"）。不加 ON ERROR 时，转换失败会直接抛错：
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.而类型不匹配（如字符串转 DECIMAL 失败）常见的是 ERROR 3140 (22032): Invalid JSON text in argument 1 to function cast_as_json 或列定义阶段的 ERROR 1366 (HY000): Incorrect decimal value。生产环境强烈建议给每个金额、数量列都写 DEFAULT ... ON ERROR，否则一条脏数据会让整个报表任务失败。
5.3 精度陷阱：先乘后舍入
price*qty*discount 里若 discount 声明为 DECIMAL(4,2)，0.95 精确；但若有人图省事声明成 DOUBLE，会出现 0.5*200*0.95 = 94.99999999999999 这类结果，SUM 后与财务系统差几分钱。金额列一律 DECIMAL，且行级先 ROUND 再 SUM，与「先 SUM 再 ROUND」的结果可能不同，必须和业务口径保持一致。对账场景推荐在 CTE 里先把 line_amount 舍入，再在外层 SUM。
5.4 空数组与非法 JSON
如果 detail_json 存的是 []，JSON_TABLE 返回 0 行，内连接时该单据会整体消失。要保留这类单据（比如展示「空单据」列表），把连接改成 LEFT JOIN JSON_TABLE(...) d ON TRUE，此时商品列为 NULL，统计时用 COUNT(d.sku) 区分是否有明细。另外，detail_json 列类型是 JSON 时数据库保证格式合法；若历史遗留是 TEXT，插入前必须 JSON_VALID() 校验，否则 JSON_TABLE 会直接报 ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary' 之类的问题。
六、常见问题 FAQ
Q1：报错 ERROR 1064，提示 JSON_TABLE 附近语法错误？
先查版本：SELECT VERSION();。JSON_TABLE 需要 8.0.4 及以上，5.7 完全不支持。5.7 环境只能用 JSON_LENGTH + 数字辅助表 JSON_EXTRACT(detail_json, CONCAT('$[', n.i, ']')) 手工展开，写法繁琐且无法用 COLUMNS 定义类型。
Q2：为什么 COUNT(*) 出来的行数比实际明细多？
检查是否用了 NESTED PATH 展开子数组，或者 JSON_TABLE 之外又 JOIN 了明细可能一对多的表（如物流轨迹、审批记录），导致行放大。统计金额时务必确认 JOIN 的表与明细是一对一关系，或者先用子查询聚合再 JOIN。
Q3：拆行后 SUM(amount) 和单据头存的 total_amount 对不上？
按顺序排查：① 是否漏了 COALESCE，部分明细为 NULL 被 SUM 忽略；② 是否先舍入再汇总的口径不一致；③ discount 是 0.95 还是 95（百分数），列定义宽度 DECIMAL(4,2) 存不下 95.00，会截断成 95.00 之外的值或报错；④ 是否有重复行被 JOIN 放大。建议把明细行单独 SELECT 出来和单据头金额逐单核对。
Q4：能不能在 JSON_TABLE 的 COLUMNS 里做计算，比如直接算金额？
COLUMNS 里只支持 PATH 取值，不支持表达式列。金额计算放在外层 SELECT 里做，或者把它做成 CTE/视图字段（见第三节）。8.0 的 COLUMNS 支持 EXISTS PATH 返回布尔列，用它可以标记某条明细是否带备注、是否有附件这类存在性判断。
Q5：数据量大时查询很慢，有没有替代方案？
JSON_TABLE 适合几千到几十万行的分析场景。到了百万行级别，建议：明细单独建表，应用层在写单据时同步落库；JSON 列只保留快照用于回溯。若短期不能改表结构，可以把拆行结果每日物化到 order_item_daily 表，在 sku、customer_id、order_date 上建联合索引，报表查物化表，响应从秒级降到毫秒级。
Q6：JSON 里的数字在拆行后变成科学计数法或丢精度怎么办？
COLUMNS 的类型声明决定了解析方式。声明 DECIMAL(18,4) 时 MySQL 按定点数解析，不会丢精度；声明 VARCHAR 则按 JSON 文本原样输出。注意大整数（超过 2^53）在 JSON 里本身就可能失真，单据金额超过千万级别时推荐存字符串再转 DECIMAL。
小结：JSON_TABLE 把「JSON 数组拆行」这件事从应用层搬回了 SQL 层，配合 CTE 和窗口函数，几十行 SQL 就能完成单据明细的展开、关联与多维统计。关键点只有四个：行路径写 $[*]、金额列用 DECIMAL、每个列配 DEFAULT ... ON EMPTY/ON ERROR 兜底、行级先舍入再汇总。把这四点做对，JSON 存储的灵活性和关系型统计的严谨性就能同时拿到。