如果仅使用时间组件调用 MySQL 的 STR_TO_DATE() 将返回 NULL [英] MySQL's STR_TO_DATE() returns NULL if called with only a time component

问题描述

我在 MySQL 的函数 STR_TO_DATE() 中遇到了一些奇怪的问题.它似乎无法解析时间，如下所示:

I came across some weirdness with MySQL's function STR_TO_DATE(). It seems to not be able to parse times, like so:

它适用于完整的字符串:

It works on a full string:

mysql> SELECT STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p');
+-------------------------------------------------------+
| STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p') |
+-------------------------------------------------------+
| 2016-08-07 11:59:00                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

仅日期有效:

mysql> SELECT STR_TO_DATE('08/07/16', '%m/%d/%y');
+-------------------------------------+
| STR_TO_DATE('08/07/16', '%m/%d/%y') |
+-------------------------------------+
| 2016-08-07                          |
+-------------------------------------+
1 row in set (0.00 sec)

只有时间没有:

mysql> SELECT STR_TO_DATE('11:59 AM', '%l:%i %p');
+-------------------------------------+
| STR_TO_DATE('11:59 AM', '%l:%i %p') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

格式化时间有效:

mysql> SELECT DATE_FORMAT(NOW(), '%l:%i %p');
+--------------------------------+
| DATE_FORMAT(NOW(), '%l:%i %p') |
+--------------------------------+
| 1:58 PM                        |
+--------------------------------+
1 row in set (0.00 sec)

但是格式化时间然后使用相同的格式字符串将其转回不会.

But formatting the time then turning it back with the same format string doesn't.

mysql> SELECT STR_TO_DATE(DATE_FORMAT(NOW(), '%l:%i %p'), '%l:%i %p');
+---------------------------------------------------------+
| STR_TO_DATE(DATE_FORMAT(NOW(), '%l:%i %p'), '%l:%i %p') |
+---------------------------------------------------------+
| NULL                                                    |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

作为一种解决方法，我最终做了:

As a workaround, I wound up doing:

mysql> SELECT TIME(STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p'));
+-------------------------------------------------------------+
| TIME(STR_TO_DATE('08/07/16 11:59 AM', '%m/%d/%y %l:%i %p')) |
+-------------------------------------------------------------+
| 11:59:00                                                    |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

数据以这种方式存储在一个巨大的 csv 文件中，因此编辑它会很痛苦.我只是想知道这是正常现象还是我忽略了一些简单的事情.

The data is stored that way in a huge csv file, so editing it would be a pain. I am just wondering if this is normal or if I'm overlooking something simple.

重要的服务器信息:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using  EditLine wrapper

Connection id:          19
Current database:
Current user:           michael@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.13-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 29 min 10 sec

推荐答案

请记住，有一个特殊的 NO_ZERO_DATE 和 NO_ZERO_IN_DATE SQL 模式可能不允许零日期和因此，如果你只解析一个时间，让 STR_TO_DATE() 返回 NULL.

Keep in mind that there is a special NO_ZERO_DATE and NO_ZERO_IN_DATE SQL mode which could disallow zero dates and thus let STR_TO_DATE() return NULL if you only parse a time.

在运行 STR_TO_DATE() 之前尝试运行以下命令:

Try running the following command before running STR_TO_DATE() with only a time:

mysql> SET sql_mode = '';

这篇关于如果仅使用时间组件调用 MySQL 的 STR_TO_DATE() 将返回 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！