如何在SQL中将CSV字符串拆分并批量插入到关联表-SQL-PHP中文网

mysql 8.0+ 可用 json_table 拆分 csv：先 replace 转为 json 数组格式，再 concat 构造合法 json 并解析；需过滤 null/空值，注意引号转义与数据清洗。

如何在sql中将csv字符串拆分并批量插入到关联表

MySQL 8.0+ 用 `JSON_TABLE` 拆分 CSV 并 INSERT

MySQL 原生不支持直接拆分字符串，但 8.0+ 可借道 JSON_TABLE —— 把 CSV 转成 JSON 数组再展开。关键在于先用 REPLACE 把逗号换成 JSON 数组格式，再用 JSON_TABLE 解析。

常见错误是忽略引号转义和空值：CSV 中带双引号、逗号或换行时，REPLACE('a,b,c', ',', '","') 会崩；更稳妥的做法是限定场景（如纯数字/英文 ID）或预清洗数据。

假设源表 orders 有字段 product_ids（值为 "1,2,5"），目标关联表为 order_products(order_id, product_id)

SQL 写法：

INSERT INTO order_products (order_id, product_id)
SELECT o.id, jt.pid
FROM orders o
JOIN JSON_TABLE(
  CONCAT('["', REPLACE(o.product_ids, ',', '","'), '"]'),
  '$[*]' COLUMNS (pid INT PATH '$')
) AS jt;

注意 CONCAT 构造的 JSON 必须合法；若 product_ids 为空或 NULL，需加 WHERE o.product_ids IS NOT NULL AND o.product_ids != ''

PostgreSQL 用 `string_to_array` + `unnest` 最稳

PostgreSQL 天然支持数组操作，string_to_array 和 unnest 组合是拆 CSV 的标准解法，兼容性好、语义清晰、性能可控。

容易踩的坑是类型转换失败：CSV 字符串里混了空格（如 "1, 2, 3"），直接 unnest(string_to_array(...))::INT 会报错 invalid input syntax for integer。

正确写法要先 TRIM：

INSERT INTO order_products (order_id, product_id)
SELECT o.id, TRIM(v)::INT
FROM orders o
CROSS JOIN unnest(string_to_array(o.product_ids, ',')) AS v
WHERE o.product_ids IS NOT NULL AND o.product_ids != '';

如果 CSV 含引号（如 "\"1\",\"2\",\"3\""），得先用 REGEXP_REPLACE 去引号：REGEXP_REPLACE(o.product_ids, '"', '', 'g')
批量插入量大时，避免在子查询里反复调用 string_to_array；可提前把结果存到 CTE 中复用

SQL Server 用 `STRING_SPLIT` 注意排序丢失问题

SQL Server 2016+ 的 STRING_SPLIT 函数能拆 CSV，但它返回的结果集**不保证顺序**，这对需要按原位置关联的场景（如带权重的标签顺序）是硬伤。

Miller CSV TSV JSON 数据处理器

Miller (mlr) 是一个命令行工具，用于查询、整形和重新格式化名称索引数据，如 CSV、TSV、JSON 和 JSON Lines。它将 awk、sed、cut、join 和 sort 的功能整合到一个专为结构化数据处理而构建的单一工具中。

下载

另一个坑是 STRING_SPLIT 不处理空字符串：CSV 末尾多一个逗号（"1,2,"）会产生空值行，而 value 列类型是 NVARCHAR(4000)，强制转 INT 会失败。

安全写法要过滤空值并显式转换：

INSERT INTO order_products (order_id, product_id)
SELECT o.id, CAST(ss.value AS INT)
FROM orders o
CROSS APPLY STRING_SPLIT(o.product_ids, ',') ss
WHERE ss.value != '' AND ISNUMERIC(ss.value) = 1;

如需保留原始顺序，必须自己加序号：用 CHARINDEX 配合递归 CTE 或升级到 SQL Server 2022，用带 ordinal 参数的 STRING_SPLIT（STRING_SPLIT(o.product_ids, ',', 1)）

通用避坑：别在应用层拼 SQL 插入 CSV

有人习惯在代码里把 CSV 拆成数组，再循环拼 INSERT ... VALUES (x),(y),(z) —— 这在小数据量下可行，但一旦 CSV 超过几百项，SQL 长度可能突破 MySQL 的 max_allowed_packet 或触发 SQL 注入风险（尤其 CSV 来自用户输入）。

真正该做的，是让数据库干它该干的事：拆分逻辑下推到 SQL 层，只传原始 CSV 字符串；应用层专注事务控制和错误捕获。

如果数据库版本太老（比如 MySQL 5.7），又不能改结构，那就老老实实用存储过程或临时表中转——别硬扛。