mysql中的date类型直接比较大小是按照字符串比较还是时间戳

Question

mysql中的时间比如date类型的字段如果比较大小的话是按照字符串比较还是按照时间戳比较呢？

Answer 1

When Mysql compares two different data types, the first step is to convert them into the same type and then compare. So when comparing Date and String, must String be converted into Date? The answer is Yes.
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed.

For a simple example, if string comparison is used, the string "2004-04-31" should be larger than the date 2004-01-01, but 4-31 is an invalid date (April is smaller month), will be converted to "0000-00-00", so 2004-01-01 (date) > "2014-04-31".

Mysql documentation on Date type
http://dev.mysql.com/doc/refman/5.5/en/datetime.html
There is a passage like this: invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error.