SQL Server 如何比较字符串和尾随空格

 

1.select * from userinfo where username=‘admin’
2.select * from userinfo where username='admin '
第二条的where语句admin后面有一个空格，但执行两条语句等到的查询结果是一样的。当用户登录时在帐号后面输入空格也能登录成功
————————————————

 

为什么呢？微软官方解释

How SQL Server Compares Strings with Trailing Spaces

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, 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.

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.

 

SQL Server 遵循 ANSI/ISO SQL-92 规范（第8.2、<比较谓语>、常规规则 #3），说明如何比较带有空格的字符串。 ANSI 标准要求在比较中使用的字符字符串填充，以便它们的长度与比较它们的长度相匹配。 填充直接影响 WHERE 和 HAVING 子句谓词以及其他 Transact-sql 字符串比较的语义。 例如，Transact-sql 会将字符串 "abc" 和 "abc " 视为对大多数比较操作等效。此规则的唯一例外是类似谓语。 当 LIKE 谓词表达式的右侧具有尾随空格的值时，SQL Server 不会在比较发生之前将这两个值填充到同一长度。 根据定义，LIKE 谓语的用途是促进模式搜索，而不是简单的字符串相等测试，这不违反前面提到的 ANSI SQL-92 规范的部分。

 

1.select * from userinfo where username like ‘admin%’
2.select * from userinfo where username like 'admin %'

而上面两条结果却是不同的，第二条会匹配admin后面的空格