mysql - 需要允许 '0000-00-00 00:00:00' 日期 [英] mysql - Need to allow '0000-00-00 00:00:00' dates

问题描述

我希望在 MySQL 中允许零日期.我已将 sql_mode 更改为 ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION.

I want zero dates to be allowed in MySQL. I have changed the sql_mode to ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION.

我已经在/etc/mysql/my.cnf中修改了.

但是，当我尝试插入数据时出现错误，

Yet, I when I try to insert data I get the error,

数据截断:日期时间值不正确:'0000-00-00 00:00:00'

Data truncation: Incorrect datetime value: '0000-00-00 00:00:00'

MySQL 版本为 5.7.18.

The MySQL version is 5.7.18.

对此的任何想法都会有很大帮助.

Any ideas on this would be of great help.

推荐答案

要允许零日期 (0000-00-00 00:00:00)，您需要取消该限制.

To allow zero dates (0000-00-00 00:00:00), you need to remove that restriction.

要删除特定的 SQL 模式(在本例中为 NO_ZERO_DATE)，请找到当前的 SQL 模式:

To remove particular SQL mode (in this case NO_ZERO_DATE), find the current SQL mode:

SELECT @@GLOBAL.sql_mode;

复制结果并从中删除您不需要的内容 (NO_ZERO_DATE)

copy the result and remove from it what you don't need (NO_ZERO_DATE)

例如:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

到

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

创建并打开这个文件:

sudo vim /etc/mysql/conf.d/disable_strict_mode.cnf

并写入并粘贴到您的新 SQL 模式中:

and write and past into it your new SQL mode:

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启 MySQL:

sudo service mysql restart

这篇关于mysql - 需要允许 '0000-00-00 00:00:00' 日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！