MySQL SELECT WHERE datetime 匹配日期（不一定是时间）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14104304/
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
MySQL SELECT WHERE datetime matches day (and not necessarily time)
提问by user1032531
I have a table which contains a datetime column. I wish to return all records of a given day regardless of the time. Or in other words, if my table only contained the following 4 records, then only the 2nd and 3rd would be returned if I limit to 2012-12-25.
我有一个包含日期时间列的表。无论何时，我都希望返回给定日期的所有记录。或者换句话说，如果我的表只包含以下 4 条记录，那么如果我限制为 2012-12-25，那么只会返回第 2 条和第 3 条记录。
2012-12-24 00:00:00
2012-12-25 00:00:00
2012-12-25 06:00:00
2012-12-26 05:00:00
回答by Eugen Rieck
NEVER EVERuse a selector like DATE(datecolumns) = '2012-12-24'- it is a performance killer:
永远不要使用像这样的选择器DATE(datecolumns) = '2012-12-24'- 它是性能杀手：
- it will calculate
DATE()for all rows, including those, that don't match - it will make it impossible to use an index for the query
- 它将计算
DATE()所有行，包括那些不匹配的行 - 这将使查询无法使用索引
It is much faster to use
使用起来要快得多
SELECT * FROM tablename
WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59'
as this will allow index use without calculation.
因为这将允许无需计算即可使用索引。
EDIT
编辑
As pointed out by Used_By_Already, in the time since the inital answer in 2012, there have emerged versions of MySQL, where using '23:59:59' as a day end is no longer safe. An updated version should read
正如 Used_By_Already 所指出的那样，自 2012 年首次回答以来，出现了 MySQL 版本，其中使用“23:59:59”作为一天结束不再安全。更新的版本应该阅读
SELECT * FROM tablename
WHERE columname >='2012-12-25 00:00:00'
AND columname <'2012-12-26 00:00:00'
The gist of the answer, i.e. the avoidance of a selector on a calculated expression, of course still stands.
答案的要点，即在计算表达式上避免选择器，当然仍然有效。
回答by a1ex07
... WHERE date_column >='2012-12-25' AND date_column <'2012-12-26'may potentially work better(if you have an index on date_column) than DATE.
... WHERE date_column >='2012-12-25' AND date_column <'2012-12-26'可能比DATE.
回答by Ghilas BELHADJ
You can use %:
您可以使用%：
SELECT * FROM datetable WHERE datecol LIKE '2012-12-25%'
