sql-server - SQL Server 2014 LIKE 4 B 宽字符返回整个表

Question

我很难理解为什么在 4 B 宽表情符号 ( http://emojipedia.org/eyes/ ) 上使用 LIKE 条件的 SELECT 会返回表中的所有行。

-- The DB collation is Finnish_Swedish_CI_AS
CREATE TABLE #Test 
(
     Number int identity,
     Value nvarchar(20) NOT NULL
);
GO

INSERT INTO #Test VALUES ('');
INSERT INTO #Test VALUES ('a');
INSERT INTO #Test VALUES ('b');
INSERT INTO #Test VALUES ('c');
INSERT INTO #Test VALUES ('👀'); -- should be http://emojipedia.org/eyes/
GO

select * from #Test where Value like '%👀%'
select * from #Test where Value like N'%👀%'
GO

DROP TABLE #Test;
GO

第一个选择的结果是单个表情符号行。但是，第二个选择的结果(其中字符串标记为带有 N 前缀的 UNICODE)是所有行。

为什么第二个选择匹配表中的所有行？

实际场景我有一个 Web 应用程序，用户可以在其中搜索目录，如果他们搜索表情符号，则会返回整个表，而不是正确匹配的行。

ASP.NET MVC 5 web application <-> Web Api 2 <-> EF -> SQL Server

更新我的测试SQL不正确，因为@deroby指出INSERT语句应该用N'...'标记正确插入为 Unicode。

解决方案 正如 @deroby 所说，这似乎是一个排序问题，将其更改为 Finnish_Swedish_100_CI_AS 似乎可以使事情按预期进行。

select * from #Test where Value like N'%👀%' COLLATE Finnish_Swedish_100_CI_AS

Answer 1

添加后会变得更有趣

INSERT INTO #Test VALUES ('👂'); -- should be http://emojipedia.org/eyes/

您的第一个SELECT现在将返回两个表情符号记录；即使它们看起来是错误的。其原因是 👂和👀是无效的 varchar() 值。因此，它们会导致一些占位符被放入数据库中(显示为 ?? )。

现在，如果您“正确”地进行插入并将数据作为正确的 Unicode 字符串插入，那么事情就会好得多:

INSERT INTO #Test VALUES (N'');
INSERT INTO #Test VALUES (N'a');
INSERT INTO #Test VALUES (N'b');
INSERT INTO #Test VALUES (N'c');
INSERT INTO #Test VALUES (N'👀'); -- should be http://emojipedia.org/eyes/
INSERT INTO #Test VALUES (N'👂'); -- should be http://emojipedia.org/eyes/

select * from #Test -- returns everything
select * from #Test where Value like '%👀%' -- returns nothing (because it looks for the '??' placeholder
select * from #Test where Value like N'%👀%' -- returns just one line

PS:这就是它在我的 SQL2012 上的工作原理 (Latin1_General_BIN)。

Why does the the second select match all rows in the table?

我也无法解释；对我来说则不然。所以我猜测这是一个排序问题，这些表情符号都被认为是相等的。

事实上，正在做测试

select * from #Test where Value like N'%👀%' COLLATE Finnish_Swedish_CI_AS
select * from #Test where Value like N'%👀%' COLLATE Latin1_General_BIN

说明这里需要寻找原因。至于为什么，我也不知道。