在mysql中查询范围的最佳方法
我正在设计一个查询，该查询根据表示为范围（特别是年份）的参数从大型表中提取行。在某些情况下，我想要根据 3 个特定值提取行（例如，提取客户编号为 #001、#002 和 #003 的客户记录）。
对于范围问题，我想知道 WHERE 语句背后的条件语法是否比其他语句运行得更快：
IN()
BETWEEN()
high.range >= data >=low.range
对于 IN () 选项，我可以编写一个脚本，将年份转换为从 low.range 到 high.range（含）的年份列表。因此，对于 2000 到 2004 的范围，它将是 IN('2000','2001','2002','2003','2004')。这将是 MySQL 之外的额外编程步骤，但这对我来说并不难。
我意识到我可以做其他事情来加快速度（比如使用聚集索引），但我只是想看看在 WHERE 语句之后使用哪种 sql 语法是否有任何区别。
I am designing a query that pull rows from a large table based on parameters expressed as ranges (specifically years). There are some cases where I'll want to pull rows based on 3 specific values (say, pull customer records where customer number is #001, #002, and #003).
For the range question, I'm wondering if there is a syntax for the conditional behind the WHERE statement that works faster than the others:
IN()
BETWEEN()
high.range >= data >=low.range
For the IN() option, I could write a script that translate the years into a list of years from low.range to high.range inclusive. So, for the range 2000 to 2004, it would be IN('2000','2001','2002','2003','2004'). This would be an extra programming step outside of MySQL, but it's not hard for me to do.
I realize that there are other things I can do to speed things up (like using a clustered index), but I just wanted to see if there is any difference from which sql syntax to use after the WHERE statement.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在幕后工作之间就像 high.range >= data >=low.range
In(condition1,condition2,condition3) 的工作方式就像 (condition1 or condition2 or condition3)
我希望如果数量日期大于 1 或 2。
就像 (1000 > 1001) 与 (1000 = 998 或 1000 = 996 或 1000 = 995 ETC）。
behind the scenes between works just like high.range >= data >=low.range
In(condition1, condition2, condition3) works just like (condition1 or condition2 or condition3)
I'd expect between to work faster if the number of date is larger than 1 or 2.
its like (1000 > 1001) versus (1000 = 998 or 1000 = 996 or 1000 = 995 etc).
编辑：抱歉，我可能误解了你的问题。
我相信在这种情况下
Between 是in 更合适的函数，但我需要进行一些基准测试来确定。如果您使用 TIMESTAMP、DATE 或 DATETIME 列，则可以使用
year：分别表示
month、week和day也有功能。http://dev.mysql.com/doc/refman/5.1/en /datetime.html
Edit: sorry, I might have misinterpreted your question.
I believe
betweenin this case is the more appropriate function toin, but I'd to some benchmarking to be sure.If you're using a TIMESTAMP, DATE or DATETIME column, you could use
year:There are for
month,week, anddayfunctions as well.http://dev.mysql.com/doc/refman/5.1/en/datetime.html