JSON 转 SQL:把对象数组变成可执行的 INSERT 语句

后端开发里最常见的一个琐碎活,就是手上有一段 JSON,想让它原样躺进数据库表。接口返回的用户列表、导出的一张表、爬下来的几百条记录,数据本身已经成形,缺的只是一句句 INSERT。手抄太慢,正则替换又容易在引号上栽跟头。这篇把 JSON 转 SQL 的内部逻辑拆开讲:对象的键怎样成为列名,每个值怎样落进 VALUES,以及为什么字符串里的单引号是整件事最容易翻车的地方。

键做列,值做 VALUES

转换的核心规则只有一条:对象数组里每个对象的键,合并成列清单;每个对象的值,按顺序填进 VALUES。

关键在于"合并"。如果某些对象多字段或少字段,不能简单取第一行的键。正确做法是取所有行里键的并集做列清单,某一行没有这个键,就在对应位置补 NULL。这样三十行结构略有出入的数据也能对齐成同一张表,不会因为第五行多了个 phone 字段就整批错位。

值这一侧要分类型处理:数字裸写,布尔按方言写成 TRUE/FALSE 或 1/0,null 写成 NULL,字符串用单引号包起来。嵌套对象和数组没法单独塞进一个关系型列,只能序列化成 JSON 字符串存进一列,需要的话再用 JSON 转 CSV 把它拍平成真正的多列。

单引号翻倍,这是防注入的底线

字符串转义是朴素转换最容易出事的一环。SQL 里字符串用单引号界定,所以值里只要带一个单引号,就可能提前把字符串闭合,后面的内容被当成 SQL 语句执行,这正是注入的入口。

不少人第一反应是用反斜杠 \'。这在某些编程语言里能用,但不是 SQL 标准,严格模式下直接报错。正确写法是把每个内部单引号翻倍成两个 ''。数据库解析时把 '' 当成字符串内部的一个撇号,而不是闭合符号。名字 O'Brien 应当变成字面量 'O''Brien',值 it's done 变成 'it''s done'。只要这一步做对,带撇号的爱尔兰姓氏、英文缩写、中文里夹带的英文引号都能安全落库。

一段真实例子:从 JSON 到 INSERT

假设接口返回这样一段数据:

[
  {"id": 1, "name": "小明", "vip": true, "note": "it's done"},
  {"id": 2, "name": "O'Brien", "vip": false}
]

填上表名 users,选 PostgreSQL 方言,得到的是:

INSERT INTO users (id, name, vip, note) VALUES
  (1, '小明', TRUE, 'it''s done'),
  (2, 'O''Brien', FALSE, NULL);

注意三处细节。第一,第二行没有 note 字段,列清单仍按并集保留 note,缺的那格补 NULL。第二,it's done 和 O'Brien 里的单引号都翻倍成了 '',不会破坏语句。第三,布尔在 PostgreSQL 上写成 TRUE/FALSE,如果切到 MySQL 或 SQLite 会变成 1/0,标识符引用也从双引号换成反引号。把这段 SQL 复制进数据库客户端就能直接跑。

建表语句与方言差异

INSERT 之前通常还要一句 CREATE TABLE。从 JSON 推断列类型有个朴素但够用的办法:看值。整数键往 INTEGER 走,带小数往 NUMERIC,布尔往 BOOLEAN(MySQL 上用 TINYINT(1)),字符串往 VARCHAR 或 TEXT,嵌套结构往 JSON 或 JSONB。

方言差异主要在标识符引用上。MySQL 用反引号包住表名列名,比如 ` order ,而 PostgreSQL 和 SQLite 用双引号 "order"。平时无所谓,但列名撞上保留字(order、user、group`)时,引用符号用错就是语法错误。所以方言要先选对,生成的引号才跟目标引擎对得上。生成的 SQL 嫌挤可以再过一遍 SQL 格式化工具排版对齐。

批量灌数据:每行一条还是合并多行

到底每行一条 INSERT,还是合并成一条多行 VALUES,取决于用途。

写迁移文件或种子文件时,我习惯每行一条。这样 git 差异干净,改一条数据只动一行,review 的人一眼能看出变了什么。而往测试库灌几百行 fixture 时,我会切成合并的多行 INSERT:INSERT INTO t (列) VALUES (...), (...), (...);。一条多行语句比几百条单独语句快很多,数据库只解析和提交一次。我上次给一个回归测试准备六百行数据,合并成一条后整批秒进,而拆成单条要等好几秒,差别很直观。

数据迁移时还有个省事的点:把方言切到目标引擎,一份 MySQL 导出的 JSON 能干净地生成 PostgreSQL 的 INSERT,反引号自动换成双引号,布尔自动换写法,不用你一个个手改。

把这些规则手写一遍很费神,直接用 JSON 转 SQL 工具跑最省事:粘 JSON、填表名、选方言,转义和并集列都替你算好,全程在浏览器本地完成,什么都不上传。

Made by Toolora · Updated 2026-06-13