MySQL STR_TO_DATE 函数不工作 [英] MySQL STR_TO_DATE function not work for time

问题描述

我只想将时间与另一个时间字符串进行比较，所以我使用 STR_TO_DATE 函数将我的时间字符串转换为日期时间格式

I want only time to compare with another time string, so i am using STR_TO_DATE function to convert my time string to date time format

下面的语句对我来说很好用，所以我认为 MySQL 没有任何问题

Below statement works fine for me, so i think MySQL don't have any problem

SELECT STR_TO_DATE('26/04/2011', '%d/%m/%Y');

但我不知道为什么下面的对我不起作用

But i don't know why below one is not working for me

SELECT STR_TO_DATE('11:59:59','%h:%i:%s');  

当我在 PhpMyAdmin 的 SQL 控制台中运行时，上述语句总是返回 NULL

Above statement always return NULL when i run in SQL Console of PhpMyAdmin

推荐答案

这确实是因为 SQL 模式 NO_ZERO_DATE.当 Anand Rockzz 链接到手册时，您应该已经看到了，但该链接只是解释的一半.从 MySQL 5.7.4 开始，该设置已被弃用.在你的第一个例子中，你正在设置一个日期；在第二个中， NO_ZERO_DATE 导致返回 NULL，因为您没有指定日期.从 5.7.8 开始，NO_ZERO_DATE 和 NO_ZERO_IN_DATE 已包含在默认 sql_mode 值中.根据手册，看起来好像弃用和删除已部分恢复(从 5.7.8 开始)，因此您可以在 my.cnf(或每个会话)中明确设置它.

This is indeed because of the SQL mode NO_ZERO_DATE. You should have seen that when Anand Rockzz linked to the manual, but that link is only half of the explanation. As of MySQL 5.7.4, the setting is deprecated. In your first example, you're setting a date; in the second one, NO_ZERO_DATE causes the return of NULL because you're not specifying a date. Starting with 5.7.8, NO_ZERO_DATE and NO_ZERO_IN_DATE have been included with the default sql_mode value. According to the manual, it appears as if the deprecation and removal has been partially reverted (starting with 5.7.8), so you may be able to explicitly set this in your my.cnf (or per-session).

您还应该参考有关的手册NO_ZERO_DATE 指令.

You should also refer to the manual regarding the NO_ZERO_DATE directive.

这篇关于MySQL STR_TO_DATE 函数不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！