如何根据逗号分隔字符串匹配更新数据库Users表的CatId字段

问题背景 #

现有Users表结构及数据如下：

UserId  CatId
1       10
2       78
3       99
4       89
5       80

需通过存储过程传入两个逗号分隔的字符串参数：

• P_uid：待更新的UserId列表，示例值为'1,3,4'
• P_new_cat_id：对应UserId要更新的CatId值列表，示例值为'100,12,13'
  要求按两个字符串的顺序一一对应更新Users表的CatId字段，预期更新后的数据如下：

UserId  CatId
1       100
2       78
3       12
4       13
5       80

实现思路 #

普通UPDATE语句无法直接处理这种按顺序一一映射的批量更新，核心思路是：

1. 将两个逗号分隔的字符串拆分成带序号的数据集，确保每个UserId和对应的CatId序号一致
2. 通过序号关联两个拆分后的数据集，再关联原Users表执行更新

具体实现（以SQL Server为例） #

方法1：使用内置STRING_SPLIT函数（SQL Server 2016+） #

STRING_SPLIT默认不返回序号，需要结合ROW_NUMBER()生成序号来对应顺序：

CREATE PROCEDURE UpdateUserCatId
    @P_uid NVARCHAR(MAX),
    @P_new_cat_id NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 拆分UserId列表并生成序号
    WITH SplitUids AS (
        SELECT 
            CAST(value AS INT) AS UserId,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Seq
        FROM STRING_SPLIT(@P_uid, ',')
    ),
    -- 拆分CatId列表并生成序号
    SplitCatIds AS (
        SELECT 
            CAST(value AS INT) AS NewCatId,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Seq
        FROM STRING_SPLIT(@P_new_cat_id, ',')
    )
    -- 关联更新
    UPDATE u
    SET u.CatId = sc.NewCatId
    FROM Users u
    JOIN SplitUids su ON u.UserId = su.UserId
    JOIN SplitCatIds sc ON su.Seq = sc.Seq;
END

方法2：自定义字符串拆分函数（兼容低版本SQL Server） #

如果你的SQL Server版本低于2016，没有STRING_SPLIT，可以先创建一个带序号的拆分函数：

CREATE FUNCTION dbo.SplitStringWithSeq
(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CAST(value AS INT) AS Item,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Seq
    FROM (
        SELECT 
            SUBSTRING(@InputString, number, CHARINDEX(@Delimiter, @InputString + @Delimiter, number) - number) AS value
        FROM master..spt_values
        WHERE type = 'P' AND number <= LEN(@InputString)
            AND SUBSTRING(@Delimiter + @InputString, number, 1) = @Delimiter
    ) AS t
)
GO

然后在存储过程中使用这个函数：

CREATE PROCEDURE UpdateUserCatId
    @P_uid NVARCHAR(MAX),
    @P_new_cat_id NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    WITH SplitUids AS (
        SELECT Item AS UserId, Seq FROM dbo.SplitStringWithSeq(@P_uid, ',')
    ),
    SplitCatIds AS (
        SELECT Item AS NewCatId, Seq FROM dbo.SplitStringWithSeq(@P_new_cat_id, ',')
    )
    UPDATE u
    SET u.CatId = sc.NewCatId
    FROM Users u
    JOIN SplitUids su ON u.UserId = su.UserId
    JOIN SplitCatIds sc ON su.Seq = sc.Seq;
END

注意事项 #

• 确保传入的P_uid和P_new_cat_id的元素数量完全一致，否则会出现部分数据无法匹配的情况
• 如果需要处理空值或无效的数字格式，可以在拆分时增加数据校验逻辑，比如使用TRY_CAST替代CAST避免转换错误

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