如何将逗号分隔字符串插入带自增ID的SQL临时表

嘿，这个需求我经常碰到，给你整理了几种主流数据库下的实现方案，你可以根据自己用的数据库来选：

首先我们需要把逗号分隔的字符串拆分成单独的行数据，然后插入到带有自增ID的临时表中——自增ID字段一般数据库会自动生成，插入时不需要手动指定它的值。

1. SQL Server 实现 #

先创建临时表 #

CREATE TABLE #TempTable (
    ID INT IDENTITY(1,1) PRIMARY KEY, -- 自增ID，从1开始每次加1
    Value INT -- 这里根据你的实际数据类型调整，比如VARCHAR(50)如果是字符串数据
);

方法一：用STRING_SPLIT（SQL Server 2016及以上版本） #

这是最简单的方式，直接用内置函数拆分字符串：

DECLARE @InputString NVARCHAR(MAX) = '12,34,46,767';

INSERT INTO #TempTable (Value)
SELECT TRIM(value) FROM STRING_SPLIT(@InputString, ',');

TRIM函数用来处理字符串中可能存在的空格，比如如果输入是'12, 34, 46'这种带空格的情况

方法二：兼容旧版SQL Server（2016之前） #

如果你的SQL Server版本较低，没有STRING_SPLIT，可以用XML来拆分：

DECLARE @InputString NVARCHAR(MAX) = '12,34,46,767';
-- 把字符串转成XML格式
DECLARE @XmlData XML = '<root><item>' + REPLACE(@InputString, ',', '</item><item>') + '</item></root>';

INSERT INTO #TempTable (Value)
SELECT 
    TRIM(T.c.value('.', 'INT')) AS Value
FROM @XmlData.nodes('/root/item') T(c);

2. MySQL 实现 #

先创建临时表 #

CREATE TEMPORARY TABLE TempTable (
    ID INT AUTO_INCREMENT PRIMARY KEY, -- 自增ID
    Value INT -- 按需调整数据类型
);

方法一：用STRING_SPLIT（MySQL 8.0及以上版本） #

SET @InputString = '12,34,46,767';

INSERT INTO TempTable (Value)
SELECT TRIM(value) FROM STRING_SPLIT(@InputString, ',');

方法二：兼容旧版MySQL（8.0之前） #

用递归CTE来拆分字符串：

SET @InputString = '12,34,46,767';

WITH RECURSIVE SplitCTE AS (
    -- 初始化：取第一个分隔后的值
    SELECT 
        1 AS Pos,
        SUBSTRING_INDEX(@InputString, ',', 1) AS Value,
        SUBSTRING(@InputString, LENGTH(SUBSTRING_INDEX(@InputString, ',', 1)) + 2) AS Remaining
    UNION ALL
    -- 递归处理剩余部分
    SELECT 
        Pos + 1,
        SUBSTRING_INDEX(Remaining, ',', 1),
        SUBSTRING(Remaining, LENGTH(SUBSTRING_INDEX(Remaining, ',', 1)) + 2)
    FROM SplitCTE
    WHERE Remaining != ''
)
INSERT INTO TempTable (Value)
SELECT TRIM(Value) FROM SplitCTE;

3. PostgreSQL 实现 #

先创建临时表 #

CREATE TEMPORARY TABLE TempTable (
    ID SERIAL PRIMARY KEY, -- 自增ID
    Value INT -- 按需调整数据类型
);

拆分插入的方式 #

用string_to_array把字符串转成数组，再用unnest展开成行：

-- 方式一：直接插入
INSERT INTO TempTable (Value)
SELECT TRIM(unnest(string_to_array('12,34,46,767', ',')))::INT;

-- 方式二：用变量存储输入字符串
DO $$
DECLARE
    InputString TEXT := '12,34,46,767';
BEGIN
    INSERT INTO TempTable (Value)
    SELECT TRIM(unnest(string_to_array(InputString, ',')))::INT;
END $$;

几个重要提示 #

• 数据类型适配：上面示例用的是INT类型，如果你的输入字符串是文本、日期等其他类型，记得把Value列的类型和转换逻辑调整（比如去掉::INT，改成VARCHAR(50)）。
• 空值处理：如果输入字符串有连续逗号（比如'12,,34'），拆分后会产生空值，如果你不想插入空值，可以在SELECT语句后加WHERE TRIM(value) != ''。
• 自增ID的自动生成：所有示例中的自增字段（IDENTITY、AUTO_INCREMENT、SERIAL）都会在插入数据时自动生成ID值，所以插入时只需要指定Value列即可。

内容的提问来源于stack exchange，提问作者prasanna