MySQL 什么是mysql的“BETWEEN”性能超过..？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4382892/
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
What's mysql's "BETWEEN" performance over..?
提问by Daveel
Is there any better performance when querying in (particularly) mysql of the following:
在（特别是）mysql 中查询以下内容时是否有更好的性能：
SELECT * FROM `table` WHERE `unix_date` BETWEEN 1291736700 AND 1291737300
over:
超过：
SELECT * FROM `table` WHERE `unix_date` >= 1291736700 AND `unix_date` <= 1291737300
or BETWEEN syntax is just being substituted with the second sql?
或者 BETWEEN 语法只是被第二个 sql 取代？
采纳答案by tpdi
As I recall, there's no difference. But see for yourself if:
我记得，没有区别。但是，如果：
explain plan for
SELECT * FROM `table` WHERE `unix_date` BETWEEN 1291736700 AND 1291737300
and:
和：
explain plan for
SELECT * FROM `table` WHERE `unix_date` >= 1291736700 AND `unix_date` <= 1291737300
produce the same plans.
产生相同的计划。
回答by Paolo Bergantino
From the documentation:
从文档：
exprBETWEENminANDmaxIf
expris greater than or equal tominandexpris less than or equal tomax, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min<=exprANDexpr<=max) if all the arguments are of the same type.Otherwise type conversion takes place according to the rules described in Section 11.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.
expr在min和之间max如果
expr大于或等于min且expr小于或等于max，则 BETWEEN 返回 1，否则返回 0。这等效于表达式 (min<=exprANDexpr<=max) 如果所有参数的类型相同。否则，类型转换将根据第 11.2 节“表达式计算中的类型转换”中描述的规则进行，但适用于所有三个参数。
So it really is just syntax sugar.
所以它真的只是语法糖。
回答by alex
BETWEENshows clearer intent and reads better (something SQL set out to do).
BETWEEN显示更清晰的意图并更好地阅读（SQL 打算做的事情）。
回答by Soufiane Ghzal
The three following statement will produce the same result:
以下三个语句将产生相同的结果：
i BETWEEN x AND yx <= i AND i <= Yi >= x AND i <= Y
i BETWEEN x AND yx <= i AND i <= Yi >= x AND i <= Y
But in configuration 3 mysql might (depending on your indexes) use different indexes: order matters and you should be testing with EXPLAIN query to see the difference
但是在配置 3 mysql 可能（取决于您的索引）使用不同的索引：顺序很重要，您应该使用 EXPLAIN 查询进行测试以查看差异
