SQL中按分隔符拆分字符串并动态赋值给变量的实现方法

这个问题我碰到过好多次了——当拆分后的元素数量不确定时，硬编码@day1、@day2这种固定变量肯定行不通，毕竟你没法提前知道要定义多少个。下面给你两种实用的解决思路，适配不同的SQL场景：

这是最灵活的方案，不管字符串里有多少天，都能统一存在表结构里，后续操作也方便。不同SQL方言的实现略有不同：

SQL Server 示例 #

DECLARE @days NVARCHAR(100) = 'sunday,monday,thursday';
-- 定义表变量存储拆分后的天数，带序号方便定位
DECLARE @SplitDays TABLE (DayName NVARCHAR(20), DayOrder INT IDENTITY(1,1));

-- 拆分字符串并插入表变量
INSERT INTO @SplitDays (DayName)
SELECT value FROM STRING_SPLIT(@days, ',');

-- 查看结果，序号对应你想要的@day1、@day2的顺序
SELECT * FROM @SplitDays;

你可以通过DayOrder字段来获取第N天的值，比如SELECT DayName FROM @SplitDays WHERE DayOrder = 2就相当于取@day2的值。

MySQL 示例 #

MySQL没有内置的拆分函数，用递归CTE来实现：

SET @days = 'sunday,monday,thursday';

WITH SplitDays AS (
    SELECT 
        SUBSTRING_INDEX(@days, ',', 1) AS DayName,
        TRIM(LEADING ',' FROM SUBSTRING(@days, LOCATE(',', @days) + 1)) AS RemainingDays,
        1 AS DayOrder
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(RemainingDays, ',', 1) AS DayName,
        TRIM(LEADING ',' FROM SUBSTRING(RemainingDays, LOCATE(',', RemainingDays) + 1)) AS RemainingDays,
        DayOrder + 1 AS DayOrder
    FROM SplitDays
    WHERE RemainingDays != ''
)
SELECT DayName, DayOrder FROM SplitDays;

PostgreSQL 示例 #

用string_to_array和unnest函数拆分：

WITH SplitDays AS (
    SELECT 
        unnest(string_to_array('sunday,monday,thursday', ',')) AS DayName,
        generate_series(1, array_length(string_to_array('sunday,monday,thursday', ','), 1)) AS DayOrder
)
SELECT * FROM SplitDays;

如果业务场景真的需要把每个天数赋值给@day1、@day2这种变量，只能用动态SQL来生成对应的赋值语句。这里以SQL Server为例：

DECLARE @days NVARCHAR(100) = 'sunday,monday,thursday';
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @counter INT = 1;

-- 先把拆分结果存到临时表
SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
INTO #TempDays
FROM STRING_SPLIT(@days, ',');

-- 动态构建赋值语句
WHILE EXISTS(SELECT * FROM #TempDays WHERE rn = @counter)
BEGIN
    SET @sql += 'DECLARE @day' + CAST(@counter AS NVARCHAR(5)) + ' NVARCHAR(20); ' +
                'SET @day' + CAST(@counter AS NVARCHAR(5)) + ' = ''' + 
                (SELECT value FROM #TempDays WHERE rn = @counter) + '''; ' +
                'PRINT ''@day' + CAST(@counter AS NVARCHAR(5)) + ' = '' + @day' + CAST(@counter AS NVARCHAR(5)) + ';' + CHAR(10);
    SET @counter += 1;
END

-- 执行动态SQL，会打印每个变量的值
EXEC sp_executesql @sql;

DROP TABLE #TempDays;

⚠️ 注意：动态SQL里定义的变量只在它自己的作用域内有效，外部SQL无法直接访问这些变量。如果要在后续逻辑中使用这些变量，得把逻辑也写到动态SQL里，这也是为什么更推荐思路1的原因。

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