MySQL 在 Where 子句中处理日期的更好方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3325705/
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
Better Way to Handle Dates in Where Clause
提问by brant
I was wondering which type of query is better to use:
我想知道哪种类型的查询更适合使用：
SELECT * FROM table WHERE DATE >= '2010-7-20' AND DATE <= '2010-7-24'
OR
或者
SELECT * FROM table WHERE DATE_SUB('2010-07-24',INTERVAL 4 DAY) <= DATE
回答by Dog Ears
I always use the >=and <combination.
我总是使用>=和<组合。
So if you want to serch for a those four days you would use
所以如果你想搜索那四天你会用
where DATE >= '2010-07-20' AND DATE < '2010-07-24'
This ensures that you only get dates your interesrted in even if there's a mixtrure of acutal dates(with no time component ) and datetimes.
这确保您只获得您感兴趣的日期，即使有实际日期（没有时间组件）和日期时间的混合。
So if you had a date stored as 2010-07-20 09:00:00 and a date stored as 2010-07-20 they would both be included, but the date 2010-07-24 wouln't.
因此，如果您有一个存储为 2010-07-20 09:00:00 的日期和一个存储为 2010-07-20 的日期，它们都将被包括在内，但日期 2010-07-24 不会。
I don't trust Between!I'm not sure about MySQL but BETWEEN in SQL Server would give you inclusive results so a row with a date of 2010-07-24 would be included when I wouldn't expect it to be if you wanted the four date period from the 20th? (20th, 21st, 22nd & 23rd)
我不信任之间！我不确定 MySQL，但 SQL Server 中的 BETWEEN 会为您提供包含结果，因此如果您想要从20号？(20日、21日、22日、23日)
So ANY date on the 24th shouldn't be included even if it was midnight..!
因此，即使是午夜，也不应该包括 24 日的任何日期……！
Also...Typically using functions in your criteria can prevent the optimizer using any indexes..! Something to be aware of.
另外...通常在您的条件中使用函数可以防止优化器使用任何索引..！有什么需要注意的。
More Importantly...The second query doesn't produce the same results as the first query...Is it all there?
更重要的是......第二个查询不会产生与第一个查询相同的结果......是否全部存在？
回答by Marcx
use between?
之间使用？
SELECT * FROM table WHERE DATE BETWEEN '2010-7-20' AND '2010-7-24'
SELECT * FROM table WHERE DATE BETWEEN '2010-7-20' AND '2010-7-24'
回答by OMG Ponies
Depending on data and business rules, the two queries will produce different results.
根据数据和业务规则，两个查询会产生不同的结果。
The first query, which can be re-written to use BETWEEN (for legibility, no performance change), is guaranteed to return rows whose datecolumn value is between July 20th at 00:00:00 and July 24th at 00:00:00.
第一个查询可以重写为使用 BETWEEN（为了易读性，没有性能变化），保证返回date列值介于 7 月 20 日 00:00:00 和 7 月 24 日 00:00:00 之间的行。
The second query will return records whose datecolumn value is greater or equal to July 20th, 00:00:00. If there are dates in the future, this query will return those as well. Because a default constraint will only set the datecolumn if no value is provided - you need a check constraint (not supported by MySQL on any engine though syntax exists) or trigger to ensure that data matches your business rules.
第二个查询将返回date列值大于或等于 7 月 20 日 00:00:00 的记录。如果将来有日期，此查询也会返回这些日期。因为默认约束只会在date未提供值的情况下设置该列 - 您需要一个检查约束（尽管存在语法，但任何引擎上的 MySQL 都不支持）或触发器以确保数据符合您的业务规则。
Conclusion
结论
Being explicit is best - easier to understand, which makes it easier to maintain.
明确是最好的 - 更容易理解，这使得维护更容易。
回答by Andreas Rehm
Your examples don't produce the same result!
您的示例不会产生相同的结果！
The first one would be better with BETWEEN - it searches between two dates
第一个会更好用 BETWEEN - 它在两个日期之间搜索
The second one just searches for entries with DATE => 2010-07-20
第二个只是搜索 DATE => 2010-07-20 的条目
These are totally differen operations!
这些是完全不同的操作！
