由于日期时间值“ ...”不正确，因此无法创建mysql表。对于功能str_to_date [英] Can't create mysql table due to Incorrect datetime value "..." for function str_to_date

问题描述

以下查询有效

select date(str_to_date("08-Nov-2005 22:07","%d-%M-%Y %H:%i:%S"))

返回

2005-11-08

以下查询也有效

select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"))

返回

NULL

我应该注意有一个警告，但是它不会阻止查询执行并返回NULL结果

I should note there is a warning, but it doesn't stop the query from executing and returning a NULL result

show warnings

收益

< a href = https://i.stack.imgur.com/Q3f0D.png rel = nofollow>

但是当我尝试从结果中创建表时会出现问题。

But the problem occurs when I try to create a table from the result.

这有效

CREATE TABLE myTable AS select date(str_to_date("08-Nov-2005 22:07","%d-%M-%Y %H:%i:%S"))

但这不是

CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"))

错误消息是

Incorrect datetime value: 'XXXX' for function str_to_date

这是一个非常简单的玩具示例，但是我试图创建一个更大的表，其中包含正确解析的许多日期值，并且

This is a very simple toy example, but I am trying to create a much larger table with many date values correctly parsed and it has the same effect.

我在做什么错了？

推荐答案

您看到的行为对我而言意味着您正在严格的 SQL_MODE 中运行（这在一般的BTW中是个好主意）。

The behavior you see implies to me that you are running in a strict SQL_MODE (which is a good idea in general BTW).

您可以通过为会话设置不太严格的 SQL_MODE 来实现所需的功能。

You may be able to accomplish what you want by setting a less strict SQL_MODE for your session.

下面是一个示例，显示您的 CREATE TABLE 语句在MySQL 5.7中失败h STRICT_ALL_TABLES 模式，但是一旦我删除该限制，便成功了：

Here's an example showing your CREATE TABLE statement failing in MySQL 5.7 with STRICT_ALL_TABLES mode, but succeeding once I remove that restriction:

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES  |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"));
ERROR 1411 (HY000): Incorrect datetime value: 'XXXX' for function str_to_date

mysql> set session sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"));
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from myTable;
+-----------------------------------------------+
| date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S")) |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.01 sec)

这篇关于由于日期时间值“ ...”不正确，因此无法创建mysql表。对于功能str_to_date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！