SQL Server多字段多值查询优化方案咨询

针对你提到的Person表多字段多值查询场景，当前使用的((@City IS NULL) OR (';' + @City+ ';' like '%;' + City+ ';%'))方案存在性能瓶颈（无法利用字段索引，触发全表扫描）、易出错（特殊字符匹配异常）等问题，以下是更优的实现方案：

1. 表值参数（TVP，推荐） #

适用于SQL Server 2008及以上版本，是最规范高效的多值参数处理方式：

实现步骤： #

• 先创建用户定义的表类型，用于接收多值列表：

CREATE TYPE dbo.StringList AS TABLE (Value NVARCHAR(100));
GO

• 编写存储过程，通过表值参数接收各字段的多值条件，利用IN或JOIN关联查询：

CREATE PROCEDURE dbo.QueryPersons
    @Names dbo.StringList READONLY,
    @Contacts dbo.StringList READONLY,
    @Cities dbo.StringList READONLY
    -- 其余54个字段的表值参数依次定义
AS
BEGIN
    SELECT p.*
    FROM Person p
    WHERE 
        -- 若参数为空则跳过该条件
        (NOT EXISTS(SELECT 1 FROM @Names) OR p.Name IN (SELECT Value FROM @Names))
        AND (NOT EXISTS(SELECT 1 FROM @Contacts) OR p.Contact IN (SELECT Value FROM @Contacts))
        AND (NOT EXISTS(SELECT 1 FROM @Cities) OR p.City IN (SELECT Value FROM @Cities))
        -- 其余字段的条件按上述格式添加
END
GO

核心优势： #

• 完全利用字段上的索引，查询性能大幅提升
• 参数传递规范，避免字符串拼接的安全和格式问题
• 逻辑清晰，便于维护大量参数

2. 字符串拆分函数+IN #

若无法使用表值参数（如旧版数据库），可利用数据库内置的字符串拆分函数（如SQL Server 2016+的STRING_SPLIT）将多值字符串拆分为临时数据集，再用IN查询：

SELECT p.*
FROM Person p
WHERE
    (@City IS NULL OR p.City IN (SELECT value FROM STRING_SPLIT(@City, ';') WHERE value <> ''))
    -- 其余字段按此格式添加，注意过滤拆分后的空值

核心优势： #

• 相比LIKE拼接，能利用字段索引，性能显著提升
• 实现简单，无需额外创建表类型

3. 动态SQL拼接 #

当参数数量过多（57个），静态SQL编写繁琐时，可采用动态SQL拼接仅包含非空参数的查询条件，必须使用参数化避免SQL注入：

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Person WHERE 1=1'
DECLARE @Params NVARCHAR(MAX) = N'@City NVARCHAR(MAX), @Name NVARCHAR(MAX)' -- 所有参数定义

-- 拼接City条件
IF @City IS NOT NULL AND @City <> ''
BEGIN
    SET @SQL += ' AND City IN (SELECT value FROM STRING_SPLIT(@City, '';'') WHERE value <> '''')'
END

-- 拼接Name条件
IF @Name IS NOT NULL AND @Name <> ''
BEGIN
    SET @SQL += ' AND Name IN (SELECT value FROM STRING_SPLIT(@Name, '';'') WHERE value <> '''')'
END

-- 其余字段条件依次拼接

EXEC sp_executesql @SQL, @Params, @City = @City, @Name = @Name -- 传入所有参数

核心优势： #

• 仅生成必要的查询条件，SQL语句更简洁
• 灵活适配大量参数场景，减少冗余条件

原方案的问题说明 #

你当前使用的LIKE拼接方式存在以下缺陷：

• ';' + City + ';'会导致字段无法使用索引，触发全表扫描，数据量大时性能极差
• 若字段值包含%、_等LIKE通配符，会出现错误匹配
• 字符串拼接逻辑易出错，维护成本高

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