SQL Server 2016全文检索CONTAINS大小写查询结果不一致问题

这问题我之前帮人排查过类似的，核心原因大概率是你的全文检索目录的排序规则配置，或者SQL Server 2016在处理前缀搜索时的特殊逻辑导致的——毕竟你用大写L和小写l查询，结果差了L-1326这条，明显是大小写匹配出了问题。

一、先确认全文目录的排序规则 #

首先得搞清楚你的全文目录用的是区分大小写还是非区分大小写的排序规则，这是关键。执行下面的SQL就能查到：

SELECT 
    c.name AS CatalogName,
    l.name AS CollationName
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_languages l ON c.language_id = l.lcid
WHERE c.name = '你的全文目录名称'; -- 替换成你实际的目录名

如果返回的排序规则带_CS_（比如SQL_Latin1_General_CP1_CS_AS），那就是区分大小写的，直接会导致大小写查询结果不同；如果是_CI_开头的非区分大小写规则，那就要看前缀搜索的匹配逻辑问题了。

二、为什么小写查询会漏掉L-1326？ #

当你用"l-1326*"做前缀搜索时，全文索引的分词器可能把L-1326拆成了L和-1326两个部分。理论上非区分大小写规则下小写l应该匹配大写L，但SQL Server 2016的全文搜索在处理这种纯字母开头、带特殊字符的前缀时，偶尔会出现匹配遗漏的情况——这算是个小bug，后续版本已经修复了。

三、解决方案（按优先级排序） #

1. 临时方案：查询时统一大小写 #

最简单的办法就是不管输入是大写还是小写，统一转换成和数据一致的格式再查询，比如：

-- 把搜索词转成大写后再查
select * FROM CONTAINSTABLE(OITM, (ItemCode), '"' + UPPER('l-1326') + '*"' )

这样不管你输入的是L还是l，最终都是用大写去匹配，结果就一致了。

2. 彻底解决：重新创建非区分大小写的全文索引 #

如果你的全文目录本来就是区分大小写的，或者非区分大小写但还是有问题，那就重新建索引：

-- 先删掉现有全文索引
DROP FULLTEXT INDEX ON OITM;
-- 删掉旧的全文目录（如果这个目录只给OITM用的话）
DROP FULLTEXT CATALOG 你的全文目录名称;
-- 创建新的非区分大小写、不区分重音的全文目录
CREATE FULLTEXT CATALOG 你的全文目录名称 
WITH ACCENT_SENSITIVITY = OFF,
     DEFAULT_LANGUAGE = 'English'; -- 根据你的数据语言调整，比如中文用'Chinese'
-- 重新给OITM表建全文索引
CREATE FULLTEXT INDEX ON OITM (ItemCode LANGUAGE 1033) -- 1033是English的LCID，中文用2052
KEY INDEX 你的主键索引名称 -- 替换成OITM表的主键索引名，比如PK_OITM
ON 你的全文目录名称;

重建索引后，全文搜索就会严格遵循非区分大小写的规则，大小写查询结果就会一致了。

3. 补充方案：结合LIKE兜底 #

如果暂时没法重建索引，又怕遗漏结果，可以用LIKE和全文搜索结合，确保所有符合条件的条目都被查到：

SELECT * FROM OITM
WHERE ItemCode LIKE 'l-1326%' COLLATE SQL_Latin1_General_CP1_CI_AS
OR EXISTS (
    SELECT 1 FROM CONTAINSTABLE(OITM, (ItemCode), '"l-1326*"' ) ct
    WHERE ct.[KEY] = OITM.你的主键列名 -- 替换成OITM的主键列，比如ItemCode
);

不过要注意，LIKE的性能不如全文搜索，适合数据量不大的场景。

四、验证效果 #

调整完之后，分别执行大写和小写的查询，看看结果是不是一致了：

-- 大写查询
select * FROM CONTAINSTABLE(OITM, (ItemCode), '"L-1326*"' );
-- 小写查询
select * FROM CONTAINSTABLE(OITM, (ItemCode), '"l-1326*"' );

如果两条语句返回的结果完全相同，那问题就解决啦。

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