SQL 字符串比较，大于和小于运算符 [英] SQL string comparison, greater than and less than operators

问题描述

今天查看了一些查询示例，发现了WHERE条件中的一些字符串比较.

Today I viewed some query examples, and I found some string comparisons in the WHERE condition.

比较是使用大于 (>) 和小于 (<) 符号进行的，这是在 SQL 中比较字符串的可能方法吗?它是如何运作的?一个小于另一个的字符串按字典顺序排在前面?例如，球小于水?这个比较区分大小写?例如 BALL <水，大写字符会影响这些比较吗?

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 uppercase character does affect these comparison?

我已经用谷歌搜索了几个小时，但没有找到可以让我摆脱这些疑问的任何东西.

I've googled for hours but I was not able to find nothing that can drive me out these doubt.

推荐答案

比较运算符(包括 < 和 >) 处理字符串值和数字.

The comparison operators (including < and >) "work" with string values as well as numbers.

对于 MySQL

默认情况下，字符串比较不区分大小写并使用当前字符集.默认为 latin1(cp1252 西欧)，也适用于英语.

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.

当被比较的字符串的字符集排序规则区分大小写时，字符串比较将区分大小写，即字符集的名称以 _cs 而不是 _ci 结尾.在这里重复 MySQL 参考手册中提供的所有信息真的没有意义.

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 _cs rather than _ci. There's really no point in repeating all of the information that's available in MySQL Reference Manual here.

MySQL 比较运算符参考:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

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/charset.html

More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html

回答您提出的具体问题:

To answer the specific questions you asked:

问:这是一种在 SQL 中比较字符串的可能方法吗?

Q: is this a possible way to compare strings in SQL?

答:是的，在 MySQL 和 SQL Server 中都是

A: Yes, in both MySQL and SQL Server

问:它是如何运作的?

A: 比较运算符返回一个布尔值，TRUE、FALSE 或 NULL.

A: A comparison operator returns a boolean, either TRUE, FALSE or NULL.

问:一个小于另一个的字符串按字典顺序排在前面?例如，球小于水?

Q: a string less than another one comes before in dictionary order? For example, ball is less than water?

A: 是的，因为在字符集排序规则中，'b' 出现在 'w' 之前，表达式

A: Yes, because 'b' comes before 'w' in the characteset collation, the expression

  'ball' < 'water'

将返回 TRUE.(这取决于字符集和排序规则.

will return TRUE. (This depends on the characterset and on the collation.

问:这个比较是否区分大小写?

Q: and this comparison is case sensitive?

A:特定比较是否区分大小写取决于数据库服务器；默认情况下，SQL Server 和 MySQL 都不区分大小写.

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.

在 MySQL 中，可以通过指定区分大小写的字符集排序规则来进行字符串比较(字符集名称将以 _cs 而不是 _ci 结尾)

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)

问:例如 BALL <水，大写字符会影响这些比较吗?

Q: For example BALL < water, the upper case character does affect these comparison?

答:默认情况下，在 SQL Server 和 MySQL 中，表达式

A: By default, in both SQL Server and MySQL, the expression

  'BALL' < 'water'

将返回 TRUE.

这篇关于SQL 字符串比较，大于和小于运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！