mysql 日期时间比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3989385/
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 datetime comparison
提问by GSTAR
For example the following query works fine:
例如，以下查询工作正常：
SELECT *
FROM quotes
WHERE expires_at <= '2010-10-15 10:00:00';
But this is obviously performing a 'string' comparison - I was wondering if there was a function built in to MySQL that specifically does 'datetime' comparisons.
但这显然是在执行“字符串”比较——我想知道 MySQL 是否内置了一个专门进行“日期时间”比较的函数。
回答by OMG Ponies
...this is obviously performing a 'string' comparison
...这显然是在执行“字符串”比较
No - if the date/time format matches the supported format, MySQL performs implicit conversion to convert the value to a DATETIME, based on the column it is being compared to. Same thing happens with:
否 - 如果日期/时间格式与支持的格式匹配，MySQL 会执行隐式转换以根据要比较的列将该值转换为 DATETIME。同样的事情发生在：
WHERE int_column = '1'
...where the string value of "1" is converted to an INTeger because int_column's data type is INT, not CHAR/VARCHAR/TEXT.
...其中 "1" 的字符串值被转换为 INTeger，因为int_column的数据类型是 INT，而不是 CHAR/VARCHAR/TEXT。
If you want to explicitly convert the string to a DATETIME, the STR_TO_DATE functionwould be the best choice:
如果要将字符串显式转换为 DATETIME，则STR_TO_DATE 函数将是最佳选择：
WHERE expires_at <= STR_TO_DATE('2010-10-15 10:00:00', '%Y-%m-%d %H:%i:%s')
回答by Pekka
But this is obviously performing a 'string' comparison
但这显然是在执行“字符串”比较
No. The string will be automatically cast into a DATETIME value.
否。该字符串将自动转换为 DATETIME 值。
See 11.2. Type Conversion in Expression Evaluation.
When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.
当运算符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将数字转换为字符串，反之亦然。
回答by guillaume latour
I know its pretty old but I just encounter the problem and there is what I saw in the SQL doc :
我知道它已经很老了，但我只是遇到了这个问题，我在 SQL 文档中看到了：
[For best results when using BETWEEN with date or time values,] use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
[为了在将 BETWEEN 与日期或时间值一起使用时获得最佳结果，] 使用 CAST() 将值显式转换为所需的数据类型。示例：如果将 DATETIME 与两个 DATE 值进行比较，请将 DATE 值转换为 DATETIME 值。如果在与 DATE 的比较中使用诸如“2001-1-1”之类的字符串常量，请将字符串转换为 DATE。
I assume it's better to use STR_TO_DATE since they took the time to make a function just for that and also the fact that i found this in the BETWEEN doc...
我认为最好使用 STR_TO_DATE ，因为他们花时间为此创建了一个函数，而且我在 BETWEEN 文档中发现了这个事实......
