mysql like匹配date,MySQL SELECT WHERE datetime与日期匹配（而不一定是时间）

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.

2012-12-24 00:00:00

2012-12-25 00:00:00

2012-12-25 06:00:00

2012-12-26 05:00:00

解决方案

NEVER EVER use a selector like DATE(datecolumns) = '2012-12-24' - it is a performance killer:

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

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

SELECT * FROM tablename

WHERE columname >='2012-12-25 00:00:00'

AND columname

The gist of the answer, i.e. the avoidance of a selector on a calculated expression, of course still stands.