为什么等算子和等算子的等价性比较有区别?

SELECT au_lname, au_fname  
FROM authors
WHERE au_lname = 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------
Green                                    Marjorie

SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------

Could anyone please explain me why is the second query is not returning the expected row?

有人能解释一下为什么第二个查询没有返回预期的行吗?

3 个解决方案

#1

It's a quirk of the standards apparently. Might as well be explicit:

这显然是标准的一个怪癖。不妨明确地说:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

SQL Server遵循ANSI/ISO SQL-92规范(第8.2节，通用规则#3)，说明如何将字符串与空格进行比较。ANSI标准要求对用于比较的字符串进行填充，以便它们的长度在比较之前匹配。填充直接影响WHERE和have子句谓词以及其他Transact-SQL字符串比较的语义。例如，Transact-SQL认为字符串“abc”和“abc”对于大多数比较操作是等价的。

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

这个规则唯一的例外是LIKE谓词。当LIKE谓词表达式的右侧具有一个具有尾随空间的值时，SQL Server在进行比较之前不会将两个值填充到相同的长度。因为类似谓词的目的，根据定义，是为了促进模式搜索，而不是简单的字符串相等测试，所以这并不违反前面提到的ANSI SQL-92规范的一节。

See here, here and here.

看这里，这里和这里。

Bizarre - given the terms "equal" and "like" I would have expected the latter to be the more liberal.

奇怪——考虑到“平等”和“喜欢”这两个词，我本以为后者更*。

#2

your char(8) literal 'Green ' value is automatically converted to the same data type as au_lname column, which is most likely varchar(), so the trailing space is removed in the conversion. This is probabily the optimizer at work trying to make the data types the same, so an index can be used.

您的char(8)字面“绿色”值将自动转换为与au_lname列相同的数据类型，这很可能是varchar()，因此在转换过程中删除了尾随空间。这是在工作中试图使数据类型相同的优化器，因此可以使用索引。

SELECT au_lname, au_fname  
FROM authors
WHERE au_lname = 'Green ' --auto conversion to varchar() is 'Green'

using LIKE, there is no auto conversion from the CHAR(8) 'Green ' value, so no matches are found. Most likely, this is done, so the pattern matching capabilities are preserved. if you want to search for LIKE 'xyz% ' that is quite different than LIKE 'xyz%'

使用LIKE，不会从CHAR(8)自动转换“绿色”值，所以没有找到匹配项。很有可能这样做了，因此模式匹配功能得以保留。如果你想搜索" xyz% "这和" xyz% "很不一样

SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE 'Green ' --no conversion, remains CHAR(8) 'Green '

#3

It depends on the padding stored.

这取决于存储的填充。

LIKE 'Green%' and = 'Green' will both find 'Green' whereas LIKE 'Green ' will not find 'Green' but LIKE 'Green%' will. And '%Green%' will find ' Green ', 'Mr. Green', ' Green', 'Green ', ' Green Ghost' and 'Greentree'

像'Green%'和= 'Green'这样的词都是'Green'，而'Green'则不是'Green'，而是'Green%'这样的词。“%Green%”会出现“Green”、“Mr. Green”、“Green”、“Green”、“Green Ghost”和“Greentree”

NOTE: most sql will actually match the case on - as well as in: 'GreeN', 'green' 'GREEN' all match ='Green'.

注意:大多数sql实际上会与on -以及in: 'GreeN'， 'GreeN'， 'GreeN' all match =' GreeN'匹配。

#1