sql - 为什么 SQL Server 在处理半表情符号时表现不一致？

Question

当 SQL Server 数据库中的 NVARCHAR 属性包含表情符号时，字符串函数和运算符的行为方式不同(默认数据库排序规则为 SQL_Latin1_General_CP1_CI_AS)。

字符串函数的行为

LEFT() 和 LEN() 等函数将表情符号视为多个单独的字符(LEFT 会将表情符号切成两半并返回部分值)。

DECLARE @YourString NVARCHAR(12) = N'Thank you 😃'
SELECT @YourString, LEFT(@YourString, 11), LEN(@YourString)

返回值为“谢谢😃”、“谢谢�”和12。

运营商的行为

UNION、INTERSECT 和 EXISTS 等运算符将表情符号和半表情符号视为一个相同的值，尽管它们根据值到达的顺序返回不同的结果。

UNION 运算符的行为

UNION 将这些视为相同的记录(因为它只返回一个值)，但仍会根据顺序产生不同的结果，返回底部记录 .

SELECT @YourString UNION SELECT LEFT(@YourString, 11) 返回“Thank you �”(下半部分的值)

SELECT LEFT(@YourString, 11) UNION SELECT @YourString 返回“Thank you 😃”(上半部分的值)

INTERSECT 和 EXISTS 运算符的行为

INTERSECT 和 EXISTS 也将它们视为相同的值，但会从 top record 返回值(考虑到目的，这是有意义的这些运算符，但在看到 UNION 做相反的事情后仍然感觉很奇怪)。

SELECT @YourString INTERSECT SELECT LEFT(@YourString, 11) 返回“谢谢 😃”。

SELECT LEFT(@YourString, 11) INTERSECT SELECT @YourString 返回“Thank you �”。

SELECT @YourString EXCEPT SELECT LEFT(@YourString, 11) 不返回任何结果。

总结

• LEFT() 和 LEN() 等字符串函数将这些字符视为单独的值。
• UNION 运算符将它们视为相同的值，但仍会返回不同的结果(优先考虑运算符下半部分的值)
• EXISTS 和INTERSECT 运算符将它们视为相同的值，但会返回上半部分的值

问题

为什么两个不同的值被某些运算符(UNION、INTERSECT、EXCEPT)视为一个相同的字符，但被字符串函数(LEFT、LEN)解释为两个不同的值？

奖励问题

为什么 UNION 运算符会返回它看到的第二个值？

Answer 1

恐怕我在这里没有看到任何不一致之处:使用 SQL_Latin1_General_CP1_CI_AS，所有设置和比较操作都将两个值视为相等 (fiddle):

CREATE TABLE t1 (s1 NVARCHAR(12) COLLATE SQL_Latin1_General_CP1_CI_AS);
CREATE TABLE t2 (s2 NVARCHAR(12) COLLATE SQL_Latin1_General_CP1_CI_AS);
INSERT INTO t1 VALUES (N'Thank you 😃');
INSERT INTO t2 SELECT LEFT(s1, 11) FROM t1;

SELECT * FROM t1;
SELECT * FROM t2;

SELECT s1 FROM t1 UNION SELECT s2 FROM t2;     -- only s1
SELECT s1 FROM t1 EXCEPT SELECT s2 FROM t2;    -- none
SELECT s2 FROM t2 EXCEPT SELECT s1 FROM t1;    -- none
SELECT s1 FROM t1 INTERSECT SELECT s2 FROM t2; -- only s1

SELECT * FROM t1 INNER JOIN t2 ON s1 = s2;     -- one row with s1 and s2

---

一旦我们将排序规则更改为 Latin1_General_100_CI_AS，所有操作都会将这两个值视为不相等 (fiddle):

CREATE TABLE t1 (s1 NVARCHAR(12) COLLATE Latin1_General_100_CI_AS);
CREATE TABLE t2 (s2 NVARCHAR(12) COLLATE Latin1_General_100_CI_AS);
INSERT INTO t1 VALUES (N'Thank you 😃');
INSERT INTO t2 SELECT LEFT(s1, 11) FROM t1;

SELECT * FROM t1;
SELECT * FROM t2;

SELECT s1 FROM t1 UNION SELECT s2 FROM t2;     -- both
SELECT s1 FROM t1 EXCEPT SELECT s2 FROM t2;    -- only s1
SELECT s2 FROM t2 EXCEPT SELECT s1 FROM t1;    -- only s2
SELECT s1 FROM t1 INTERSECT SELECT s2 FROM t2; -- none

SELECT * FROM t1 INNER JOIN t2 ON s1 = s2;     -- no result

---

遗留编码认为这两个值相等的原因可以在这个问题中找到:

• SQL Query Where Column = '' returning Emoji characters 🎃 and 🍰

---

您可能已经知道这一点，但我想明确指出，对于具有不同字节内容和不同长度的两个值，a = b 的计算结果为真本身并不异常(exception):所有不区分大小写的排序规则都将 'A' 和 'a' 视为相等，甚至区分大小写的排序规则也将 'A' 视为>(长度 1)和 'A '(长度 2)相等，因为 SQL Server 会忽略尾随空格。