JSON 转 SQL：INSERT、建表与类型映射的坑

结论：先按「一行代表什么业务实体」决定单表还是主表加明细表，再显式列出列名写多行 VALUES 并放进同一事务提交；字段命名与类型映射在导入前定好，别留到 SQL 里补救。

JSON 只描述形状，SQL 还要描述约束。把前者搬进后者时，所有没被写下来的约束，最后都会以脏数据的形式回来找你。

一、先判断：一行 JSON 对应一张表还是一行记录

拿到数据先看最外层，再决定表结构。这个判断做错，后面所有 INSERT 都要重写。

判断标准只有一句：一行代表什么业务实体。如果一行是订单，订单明细就必须单独成表；如果一行是用户，标签适合放明细表或 JSON 列，不适合拼成逗号字符串。

二、数组转 INSERT：最小可运行模板

最稳妥的写法始终是显式列出列名，不要依赖列顺序：

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

三个细节：

• 列名写全，表结构后续加列时不会因为位置错位而写错字段；
• 多行 VALUES 在 SQLite、PostgreSQL、MySQL 都支持，比循环单条插入少很多往返；
• NULL 是关键字，不要加引号。写成 'NULL' 会存进一个四个字符的字符串。

如果 JSON 里的键名和列名不一致（userId 对 user_id），先做一层映射，别在 SQL 里用 COALESCE 猜。字段命名问题在数据层解决，比在 SQL 里补救便宜。

三、嵌套对象拆表：主表 + 明细表

订单是最典型的一对多：

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

对应 JSON 里的 {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}。

要点：

• items 数组长度决定明细条数，主表只写一行；
• 金额用整数分存储（total_cents），别用 FLOAT；
• 明细表加外键 order_id，并在事务里一起提交，避免主表有、明细缺失；
• 如果业务只读展示、不按明细查询，可以保留 JSON 列（PostgreSQL jsonb、MySQL JSON、SQLite TEXT + json1 函数），但要接受"查不了、索引难"的代价。

嵌套对象本身（如 user.address）优先展开成扁平列（address_city、address_zip），只有层级会继续增长时才拆表。固定层级拆表只会增加 JOIN。

四、NULL 与缺字段：SQL 里没有"字段不存在"

JSON 允许某个键完全不出现，SQL 的列却是固定的。导入之后，两种情况都表现为 NULL，业务含义却可能完全不同：

两个常见错误：

1. 用空字符串代替 NULL。之后 WHERE email IS NULL 查不到这些行，统计"未填写邮箱"会漏人。
2. 用 col = NULL 判断。SQL 的三值逻辑里，NULL = NULL 结果是 NULL 而不是 TRUE，必须写 IS NULL：

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

需要保留"字段是否出现过"时，加一列布尔标记，而不是靠 NULL 推断。这一步在 API 契约里也应该写清楚，参见 JSON API 设计与字段命名。

五、字符串引号转义：别拼 SQL 字符串

JSON 字符串里可能出现单引号、双引号、反斜杠、换行，甚至 \u0000。手工拼接时，任何一处漏转义都会让语句错位，严重时构成 SQL 注入。

SQL 标准里单引号转义成两个单引号：

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

但生产代码应该用参数化绑定，让驱动处理转义与类型：

复制// PostgreSQL 用 $1/$2，MySQL 用 ?；这里以 node-postgres 为例
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

只有必须导出 .sql 文件时才手工转义，并且至少做到：

• 把 ' 替换成 ''；
• 保留换行时确认目标数据库接受多行字符串字面量；
• 去掉 \u0000，多数数据库不接受字符串里的 NUL；
• MySQL 默认把反斜杠当转义符，\ 要写成 \\，或者改用标准模式。

另外，JSON 里的双引号在 SQL 单引号字符串里不需要转义，不要多做一层导致内容里冒出反斜杠。

六、类型映射：JSON 的六种值落到 SQL 的哪些类型

JSON 只有 string、number、boolean、null、object、array 六种值，SQL 的类型却细得多。映射错的地方通常不在语法，而在精度和语义：

布尔值的坑最隐蔽：JSON false 导入 MySQL TINYINT(1) 是 0，看起来正常；导出时又被读成 false，来回一趟没问题。但一旦中间有人用 if (value) 判断字符串 "false"，就会得到真值。转换脚本里要显式做 Boolean(value)。

PostgreSQL 的 JSONB 会解析并去重键、保留最后出现的值，适合查询；JSON 保留原始文本与键顺序。SQLite 的 JSON 只是 TEXT 加一组函数，没有真正的类型约束。选之前先问：这张表需不需要按 JSON 内部字段过滤。

七、批量插入与事务：分批、显式提交、可重跑

数据量上来后，三件事决定导入是否可控：

1. 分批。单条 INSERT 循环 N 次会产生 N 次网络往返；把 500 到 1000 行合成一条多行 VALUES，或使用数据库自己的批量导入（PostgreSQL COPY、MySQL LOAD DATA、SQLite 事务内多行插入）。
2. 事务。要么全成，要么全败，别让半张表的数据留在库里：

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. 幂等。导入脚本重跑是常态，用 upsert 替代"先删后插"：

ON CONFLICT 需要唯一约束或主键，否则不会触发。没有自然主键时，用业务唯一键（例如 email）建唯一索引；两者都没有时，先在 JSON 侧补一个稳定 id，别用自增 id 做幂等判断——重跑会插入重复行。

八、方言差异速查

同一条 SQL 想在三家通用，至少要避开 ON CONFLICT、反引号和 SERIAL。跨库场景下把建表与 upsert 分别生成，不要指望一份脚本通吃。

九、导入后的三处抽检

转完先别删原始文件，做三次核对：

1. 行数：SELECT COUNT(*) FROM users; 应等于 JSON 数组长度，少一条通常是末尾记录被截断；
2. NULL 率最高的列：说明键名不一致或嵌套没展开，回去看原始数据是不是同时存在 userId 与 user_id；
3. 金额与长编号：挑最大值与最小值各一条，确认没有被浮点或整型截断。

需要先看清表格形态时，可以先用 JSON 与 CSV、表格数据的互转 的思路扁平化；如果只是给业务同事看，JSON 转 Excel 的四种办法 比直接建库更省事。

为什么 JSON 里的数字导入 SQL 后精度变了？

超过 2^53 的整数在 JavaScript 的 JSON.parse 阶段就已经丢精度，后面写进 BIGINT 也无法恢复。这类 id 在 JSON 里就应该是字符串，导入时再转成 BIGINT 或 TEXT。

JSON 里没有 id，主键怎么填？

让数据库自增，或在导入前生成稳定 UUID。不要用数组下标做 id，重跑一次顺序变了就会错位。用业务唯一键（邮箱、订单号）建唯一索引，才能配合 upsert。

批量 INSERT 报唯一键冲突怎么办？

把普通 INSERT 换成对应方言的 upsert，并确认冲突列上有唯一约束。另一个办法是先 DELETE 再插入，但必须放在同一事务里，且要接受短暂的空窗。

嵌套数组一定要建子表吗？

不一定。只读展示、不按子项查询时，存成 JSON 列更省事；一旦要按标签、SKU 过滤或统计，子表加索引才是可持续的做法。判断标准是查询方式，不是数据形状。