如何将MySQL查询到的JSON数据插入新表_使用JSON_EXTRACT解析后插入-SQL-PHP中文网

MySQL中JSON_EXTRACT返回JSON类型值，直接INSERT会存入带引号字符串或报错；必须用JSON_UNQUOTE去引号，或CAST/JSON_VALUE转标量类型，且需处理NULL和路径越界。

如何将mysql查询到的json数据插入新表_使用json_extract解析后插入

MySQL里用JSON_EXTRACT取值后直接INSERT失败？先确认字段类型和NULL处理

直接用 JSON_EXTRACT() 的结果 INSERT 到普通列（比如 VARCHAR、INT）会报错或存成 "\"value\"" 这种带双引号的字符串——因为 JSON_EXTRACT() 返回的是 JSON 类型，不是标量。必须用 JSON_UNQUOTE() 去掉外层引号，或改用 JSON_VALUE()（MySQL 8.0.21+）自动解包。

JSON_EXTRACT(json_col, '$.name') → 返回 "\"Alice\""（JSON 字符串，含转义）
JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.name')) → 返回 Alice（纯文本）
JSON_VALUE(json_col, '$.age') → 直接返回 30（自动转为 INT，支持类型推导）
如果路径不存在，JSON_EXTRACT 返回 NULL，但 JSON_UNQUOTE(NULL) 仍是 NULL，可安全插入允许 NULL 的列

INSERT ... SELECT 中嵌套 JSON 解析的写法要点

常见错误是把整个 JSON 字段当字符串拼进 VALUES，或者在 SELECT 子句里漏掉别名导致目标表列数不匹配。正确做法是在 SELECT 部分逐字段解析，并显式指定类型转换。

目标表字段顺序和 SELECT 列顺序必须严格一致
对数字字段，建议加 + 0 或 CAST(... AS SIGNED) 强制转整型，避免存成字符串后排序/计算异常
嵌套路径如 '$.items[0].price' 要注意数组索引越界：不存在时返回 NULL，不会报错

示例：

INSERT INTO users (name, age, city) 
SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')),
  CAST(JSON_EXTRACT(data, '$.age') AS SIGNED),
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.address.city'))
FROM raw_json_table;

批量插入时性能差？避免在 WHERE 或 JOIN 条件里用 JSON 函数

JSON_EXTRACT() 无法使用索引，如果在大表上对 JSON 字段反复解析再过滤（比如 WHERE JSON_EXTRACT(data, '$.status') = '"active"'），查询会全表扫描。插入前若需条件筛选，优先用生成列（generated column）+ 索引预计算。

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

建表时添加虚拟生成列：

ALTER TABLE raw_json_table 
ADD COLUMN status VARCHAR(20) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.status'))) STORED;

再给该列加索引：CREATE INDEX idx_status ON raw_json_table(status);
后续 INSERT ... SELECT 就能用 WHERE status = 'active'，走索引加速
注意：STORED 列会占用磁盘空间；VIRTUAL 列不存数据但无法建索引（MySQL 5.7+ 支持 VIRTUAL 列索引，8.0+ 才真正可用）

JSON 字段含多层嵌套或数组，怎么展开成多行插入？

单条 JSON 记录里如果有数组（如 {"orders": [{"id":1}, {"id":2}]}），想把每个订单拆成一行插入，不能靠 JSON_EXTRACT 一次取完——得用 JSON_TABLE()（MySQL 8.0.4+）。这是唯一能将 JSON 数组“横向展开”的标准方式。

JSON_TABLE() 返回一个虚拟表，可在 FROM 子句中直接 JOIN

示例：从 data 字段的 $.orders 数组中提取每项的 id 和 amount

INSERT INTO orders (user_id, order_id, amount)
SELECT u.id, j.order_id, j.amount
FROM users u,
JSON_TABLE(u.data, '$.orders[*]' COLUMNS (
  order_id INT PATH '$.id',
  amount DECIMAL(10,2) PATH '$.amount'
)) AS j;

旧版本 MySQL（JSON_LENGTH() + JSON_EXTRACT(..., '$[n]') 模拟展开

实际操作中最容易卡住的是类型隐式转换和 NULL 边界——比如 JSON_EXTRACT 取不到值时返回 NULL，但目标列设了 NOT NULL DEFAULT ''，就会因类型不匹配报错；又或者把 JSON_EXTRACT(..., '$.count') 当整数用，结果存进 VARCHAR 列变成 "123"，后续 ORDER BY count DESC 就按字符串排了。这些细节不盯住，数据看起来“插进去了”，查出来全是错的。