SQL Server 查询技巧，如何高效处理一个字段中存放多个值的情况

在SQL Server中高效处理字段中存放的多个值，核心在于字符串拆分，常用方法包括利用STRING_SPLIT函数（SQL Server 2016+）快速分割，或使用CROSS APPLY结合XML路径处理旧版本，通过将多值字符串转化为临时行集，可以轻松实现IN查询、多表关联及复杂条件筛选，从而优化查询性能，解决多值字段无法直接比较的难题。

在数据库开发与维护的过程中，我们经常会遇到数据不规范的情况，其中最常见的一种痛点就是：一个字段存放了多个值。

用户表中有一个 Role_IDs 字段，原本应该是一对多关系，但在旧系统中被设计成了字符串，内容为 "101,102,103" 或者 "101;102;103"，当业务需要查询“拥有角色 102 的所有用户”时,传统的单行查询就失效了。

本文将介绍几种在 SQL Server 中查询这种“一个字段存放多个值”场景的常用方法，从简单粗暴到高性能方案,供大家根据实际数据量和版本选择。

逗号分隔的字符串（最常见）

假设表 Users 中有一个字段 Roles，存储格式为 "101,102,103"。

方法 1：使用 CHARINDEX（推荐用于简单匹配）

相比于使用 LIKE '%value%'，使用 CHARINDEX 语法更清晰,且在大多数情况下性能更好。

-- 查询 Roles 字段中包含 '102' 的记录
SELECT *
FROM Users
WHERE CHARINDEX('102', Roles) > 0;

优点：语法简单，不需要使用临时表或函数。 缺点：如果字符串是 "10,102"，使用 CHARINDEX 也能查出来（因为它包含 "10"），这在某些业务逻辑下可能不是期望的结果，如果是这种情况，建议使用方法 2。

方法 2：使用 STRING_SPLIT（SQL Server 2016+ 推荐）

这是 SQL Server 2016 引入的函数，专门用于拆分字符串，它非常强大，可以配合 IN 操作符使用。

-- 查询 Roles 字段中包含 '102' 的记录
SELECT DISTINCT u.*
FROM Users u
INNER JOIN STRING_SPLIT(u.Roles, ',') s
ON s.value = '102';

优点：语义清晰，能精确匹配分隔符之间的值，不会出现 "10" 匹配 "102" 的情况。 缺点：需要 SQL Server 2016 及以上版本。

JSON 格式存放（现代方案）

如果你的数据库版本较新（2016+），且字段存放的是 JSON 格式，'[101, 102, 103]',那么处理起来会更加优雅。

方法 1：使用 OPENJSON

SELECT u.*
FROM Users u
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(u.Roles) WITH (id int '$') 
    WHERE id = 102
);

方法 2：使用 JSON_VALUE（适用于查找特定索引的值）

SELECT u.*
FROM Users u
WHERE JSON_VALUE(u.Roles, '$[1]') = 102; -- 获取数组中索引为1的值

模糊匹配（不推荐但有时必须）

如果字段中存放的是类似 "abc123def" 这样的连续字符，没有分隔符,只能使用模糊查询。

SELECT *
FROM Users
WHERE Roles LIKE '%102%';

警告：这种查询无法使用索引，会导致全表扫描，性能极差，如果数据量大,请务必优化数据结构。

性能与索引优化提示

在处理“一个字段存放多个值”的查询时,有几点需要特别注意：

1. 索引失效：无论是 LIKE '%...%' 还是 CHARINDEX，对于包含多个值的字段，都无法使用普通索引，这意味着随着数据量增加,查询速度会越来越慢。
2. 最佳实践：如果这是一个高频查询字段，强烈建议修改数据库设计，将 Roles 拆分为一张独立的关联表（一张用户对应多行角色记录），或者使用 JSON 类型字段配合生成列（Generated Columns）建立索引。

• 数据量小、版本旧：使用 CHARINDEX。
• 数据量大、版本新：使用 STRING_SPLIT 或 OPENJSON。
• 没有任何分隔符：使用 LIKE（但要做好性能牺牲的准备）。