SQL 字符串比较，大于和小于运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26080187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL string comparison, greater than and less than operators
提问by user3575921
I'm a newbie in studying SQL.
我是学习 SQL 的新手。
I'm in doubt about a thing.
我对一件事有疑问。
Today I viewed some query examples, and I found some string comparisons in the WHERE condition.
今天查看了一些查询示例，在WHERE条件中发现了一些字符串比较。
The comparison was made using the greater than (>) and less than (<) symbols, is this a possible way to compare strings in SQL? and how does it act? a string less than another one comes before in dictionary order? For example, ball is less than water? and this comparison is case sensitive? for example BALL < water, the upcase character does affect these comparison?
比较是使用大于 (>) 和小于 (<) 符号进行的，这是在 SQL 中比较字符串的可能方法吗？它是如何运作的？一个小于另一个的字符串按字典顺序排在前面？例如，球小于水？这个比较区分大小写？例如 BALL < water，大写字符会影响这些比较吗？
I've googled for hours but I was not able to find nothing that can drive me out these doubt.
我已经用谷歌搜索了几个小时，但我找不到任何可以让我摆脱这些疑问的东西。
回答by spencer7593
The comparison operators (including <and >) "work" with string values as well as numbers.
比较运算符（包括<和>）“工作”于字符串值以及数字。
For MySQL
对于 MySQL
By default, string comparisons are not case sensitive and use the current character set. The default is
latin1(cp1252 West European), which also works well for English.
默认情况下，字符串比较不区分大小写并使用当前字符集。默认值为
latin1(cp1252 West European)，它也适用于英语。
String comparisons will be case sensitive when the characterset collation of the strings being compared is case sensitive, i.e. the name of the character set ends in _csrather than _ci. There's really no point in repeating all of the information that's available in MySQL Reference Manual here.
当被比较的字符串的字符集排序规则区分大小写时，字符串比较将区分大小写，即字符集的名称以 结尾_cs而不是_ci。在这里重复 MySQL 参考手册中提供的所有信息真的没有意义。
MySQL Comparison Operators Reference:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
MySQL 比较运算符参考：http:
//dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html
有关 MySQL 字符集/排序规则的更多信息：http:
//dev.mysql.com/doc/refman/5.5/en/charset.html
To answer the specific questions you asked:
要回答您提出的具体问题：
Q:is this a possible way to compare strings in SQL?
问：这是一种在 SQL 中比较字符串的可能方法吗？
A:Yes, in both MySQL and SQL Server
A：是的，在 MySQL 和 SQL Server 中都可以
Q:and how does it act?
问：它是如何运作的？
A:A comparison operator returns a boolean, either TRUE, FALSE or NULL.
答：比较运算符返回一个布尔值，TRUE、FALSE 或 NULL。
Q:a string less than another one comes before in dictionary order? For example, ball is less than water?
问：一个小于另一个的字符串按字典顺序排在前面？例如，球小于水？
A:Yes, because 'b' comes before 'w' in the characteset collation, the expression
A:是的，因为在 characteset 排序规则中 'b' 出现在 'w' 之前，表达式
'ball' < 'water'
will return TRUE. (This depends on the charactersetand on the collation.
将返回 TRUE。（这取决于字符集和排序规则。
Q:and this comparison is case sensitive?
问：而且这个比较区分大小写？
A:Whether a particular comparison is case sensitive or not depends on the database server; by default, both SQL Server and MySQL are case insensitive.
A：特定的比较是否区分大小写取决于数据库服务器；默认情况下，SQL Server 和 MySQL 都不区分大小写。
In MySQL it is possible to make string comparisons by specifying a characterset collation that is case sensitive (the characterset name will end in _cs rather than _ci)
在 MySQL 中，可以通过指定区分大小写的字符集排序规则来进行字符串比较（字符集名称将以 _cs 而不是 _ci 结尾）
Q:For example BALL < water, the upper case character does affect these comparison?
Q：比如BALL < water，大写字母对这些比较有影响吗？
A:By default, in both SQL Server and MySQL, the expression
A：默认情况下，在 SQL Server 和 MySQL 中，表达式
'BALL' < 'water'
would return TRUE.
将返回 TRUE。
回答by Dan Guzman
In Microsoft SQL Server, collation determines to dictionary rules for comparing and sorting character data with regards to:
在 Microsoft SQL Server 中，排序规则确定用于比较和排序字符数据的字典规则：
- case sensitivity
- accent sensitivity
- width sensitivity
- kana sensitivity
- 区分大小写
- 重音敏感度
- 宽度灵敏度
- 假名敏感性
SQL Server also includes binary collations where comparison and sorting is done by binary code point rather than dictionary rules. Once can choose from many collations according to the desired sensitivity behavior. The default collation selected for Latin-based language locales during SQL installation is case insensitive and accent sensitive.
SQL Server 还包括二进制排序规则，其中比较和排序是通过二进制代码点而不是字典规则完成的。一次可以根据所需的灵敏度行为从许多排序规则中进行选择。在 SQL 安装期间为基于拉丁语的语言区域设置选择的默认排序规则不区分大小写且区分重音。
Collation is specified at the instance (during installation), database, and column level. Instance collation determines the collation of Instance-level objects like logins and database names as well as identifiers for variables, GOTO labels and temporary tables. Database collation (same as instance collation by default), determines the collation of database identifiers like table and column names as well as literal expressions. Column collation (same as database collation by default) determines the collation of that column.
排序规则是在实例（安装期间）、数据库和列级别指定的。实例排序规则确定实例级对象的排序规则，如登录名和数据库名称以及变量标识符、GOTO 标签和临时表。数据库排序规则（默认情况下与实例排序规则相同），确定数据库标识符（如表名和列名以及文字表达式）的排序规则。列排序规则（默认情况下与数据库排序规则相同）确定该列的排序规则。
It is certainly possible compare strings using '<', '>', '<>', ,LIKE, BETWEEN, etc.
当然可以使用 '<'、'>'、'<>'、,LIKE、BETWEEN 等来比较字符串。
