MySQL 用于文本与数值的 SQL BETWEEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15720950/
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 BETWEEN for text vs numeric values
提问by Cratylus
BETWEENis used in a WHEREclause to select a range of data between two values.
If I am correct whether the range's endpoint are excluded or not is DBMS specific.
What I can not understand in the following:
If I have a table of values and I do the following query:
BETWEEN在WHERE子句中用于选择两个值之间的数据范围。
如果我是正确的，范围的端点是否被排除是 DBMS 特定的。
我无法理解以下内容：
如果我有一个值表并且我执行以下查询：
SELECT food_name
FROM health_foods
WHERE calories BETWEEN 33 AND 135;`
The query returns as results rows includingcalories =33 and calories =135 (i.e. range endpoints are included).
查询返回的结果行包括卡路里 =33 和卡路里 =135（即包括范围端点）。
But if I do:
但如果我这样做：
SELECT food_name
FROM health_foods
WHERE food_name BETWEEN 'G' AND 'O';
I do notget rows with food_namestarting with O. I.e. the end of the range is excluded.
For the query to work as expected I type:
我没有food_name以O.开头的行。即范围的末尾被排除。
为了让查询按预期工作，我输入：
SELECT food_name
FROM health_foods
WHERE food_name BETWEEN 'G' AND 'P';`
My question is why is there such a difference for BETWEENfor numbers and text data?
我的问题是为什么BETWEEN数字和文本数据有这么大的区别？
回答by Gordon Linoff
Between is operating exactly the same way for numbers and for character strings. The two endpoints areincluded. This is part of the ANSI standard, so it is how all SQL dialects work.
之间对数字和字符串的操作方式完全相同。两个端点都包括在内。这是 ANSI 标准的一部分，因此它是所有 SQL 方言的工作方式。
The expression:
表达方式：
where num between 33 and 135
will match when num is 135. It will not match when number is 135.00001.
当 num 为 135 时匹配。当 number 为 135.00001 时不匹配。
Similarly, the expression:
同样，表达式：
where food_name BETWEEN 'G' AND 'O'
will match 'O', but not any other string beginning with 'O'.
将匹配 'O'，但不匹配任何其他以 'O' 开头的字符串。
Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:
曾经简单的混搭就是使用“~”。它具有最大的 7 位 ASCII 值，因此对于英语语言应用程序，它通常运行良好：
where food_name between 'G' and 'O~'
You can also do various other things. Here are two ideas:
您还可以做其他各种事情。这里有两个想法：
where left(food_name, 1) between 'G' and 'O'
where food_name >= 'G' and food_name < 'P'
The important point, though, is that betweenworks the same way regardless of data type.
不过，重要的一点是，between无论数据类型如何，它的工作方式都是相同的。
回答by SteveP
Take the example of 'Orange' vs. 'O'. The string 'Orange' is clearly not equal to the string 'O', and as it is longer, it must be greater, not less than.
以“Orange”与“O”为例。字符串 'Orange' 显然不等于字符串 'O'，因为它更长，所以它必须大于而不是小于。
You could do 'Orange' < 'OZZZZZZZZZZZZZZ' though.
不过，你可以做 'Orange' < 'OZZZZZZZZZZZZZZZ'。
