MySQL “create_date”时间戳字段的默认值无效

Invalid default value for 'create_date' timestamp field

提问by robert

I have the following sql create statement

我有以下 sql create 语句

mysql> CREATE  TABLE IF NOT EXISTS `erp`.`je_menus` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT ,
    ->   `name` VARCHAR(100) NOT NULL ,
    ->   `description` VARCHAR(255) NOT NULL ,
    ->   `live_start_date` DATETIME NULL DEFAULT NULL ,
    ->   `live_end_date` DATETIME NULL DEFAULT NULL , 
    ->   `notes` VARCHAR(255) NULL ,
    ->   `create_date` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00',
    ->   `created_by` INT(11) NOT NULL ,
    ->   `update_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP  ,
    ->   `updated_by` INT(11) NOT NULL , 
    ->   `status` VARCHAR(45) NOT NULL ,
    ->   PRIMARY KEY (`id`) ) 
    -> ENGINE = InnoDB;

giving following error

给出以下错误

ERROR 1067 (42000): Invalid default value for 'create_date'

ERROR 1067 (42000): Invalid default value for 'create_date'

What is the error here?

这里的错误是什么？

采纳答案by Devart

That is because of server SQL Mode - NO_ZERO_DATE.

那是因为服务器 SQL 模式 - NO_ZERO_DATE。

From the reference: NO_ZERO_DATE- In strict mode, don't allow '0000-00-00'as a valid date. You can still insert zero dates with the IGNOREoption. When not in strict mode, the date is accepted but a warning is generated.

从参考：NO_ZERO_DATE- 在严格模式下，不允许'0000-00-00'作为有效日期。您仍然可以使用IGNORE选项插入零日期。如果不是严格模式，则接受日期但会生成警告。

回答by Pankaj Shrestha

If you generated the script from the MySQL workbench.

如果您从 MySQL 工作台生成脚本。

The following line is generated

生成以下行

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Remove TRADITIONAL from the SQL_MODE, and then the script should work fine

从 SQL_MODE 中删除 TRADITIONAL，然后脚本应该可以正常工作

Else, you could set the SQL_MODE as Allow Invalid Dates

否则，您可以将 SQL_MODE 设置为 Allow Invalid Dates

SET SQL_MODE='ALLOW_INVALID_DATES';

回答by Bret VvVv

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC (see doc). The default value must be within that range.

TIMESTAMP 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC（参见doc）。默认值必须在该范围内。

Other odd, related, behavior:

其他奇怪的、相关的行为：

CREATE TABLE tbl1 (
    ts TIMESTAMP);  
Query OK, 0 rows affected (0.01 sec)

CREATE TABLE tbl2 (
    ts TIMESTAMP,
    ts2 TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'ts2'

CREATE TABLE tbl3 (
    ts TIMESTAMP,
    ts2 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
Query OK, 0 rows affected (0.01 sec)

Side note, if you want to insert NULLS:

旁注，如果你想插入 NULLS：

CREATE TABLE tbl4 (
    ts TIMESTAMP NULL DEFAULT NULL);

回答by Mubashar Abbas

In ubuntu desktop 16.04, I did this:

在 ubuntu 桌面 16.04 中，我这样做了：

1. open file: /etc/mysql/mysql.conf.d/mysqld.cnfin an editor of your choice.

2. Look for: sql_mode, it will be somewhere under [mysqld].

3. and set sql_modeto the following:

   NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

4. Save and then restart mysql service by doing:

   sudo service mysql restart

1. 打开文件：/etc/mysql/mysql.conf.d/mysqld.cnf在您选择的编辑器中。

2. 寻找：sql_mode，它将在[mysqld].

3. 并设置sql_mode为以下内容：

   NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

4. 保存然后通过执行以下操作重新启动 mysql 服务：

   sudo service mysql restart

回答by Joy Zhu

Using OS X, install mysql from Homebrew, System Variables based on its compiled-in defaults. Solution is to remove "NO_ZERO_DATE" from System Variables "sql_mode".

使用OS X，从Homebrew安装 mysql ，基于其编译默认值的系统变量。解决方案是从系统变量“sql_mode”中删除“NO_ZERO_DATE”。

Just please keep in mind that scope involve.

请记住，范围涉及。

If you want to affect only in your session, please use "@@session", For example:

如果您只想在您的会话中影响，请使用"@@session"，例如：

SET @@session.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".

In this case, it will not affect once your session ends or your change it. It has not effect on other session.

在这种情况下，一旦您的会话结束或您更改它，它就不会影响。它对其他会话没有影响。

If you want to affect on all client, please use "@@global", for example:

如果要影响所有客户端，请使用"@@global"，例如：

SET @@global.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".

In this case, it only affects on the clients that connect after the change(not affect on current all clients), and will not work once server exit.

在这种情况下，它只影响更改后连接的客户端（不影响当前所有客户端），并且一旦服务器退出就不起作用。

回答by dgitman

I was able to resolve this issue on OS X by installing MySQL from Homebrew

通过从 Homebrew 安装 MySQL，我能够在 OS X 上解决这个问题

brew install mysql

by adding the following to /usr/local/etc/my.cnf

通过将以下内容添加到 /usr/local/etc/my.cnf

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

and restarting MySQL

并重新启动 MySQL

brew tap homebrew/services
brew services restart mysql

回答by pyb

I had a similar issue with MySQL 5.7 with the following code:

我在 MySQL 5.7 中遇到了类似的问题，代码如下：

`update_date` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

`update_date` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

I fixed by using this instead:

我改用这个来修复：

`update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

`update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

回答by Antonio Reyes

To avoid this issue, you need to remove NO_ZERO_DATEfrom the mysql mode configuration.

为避免此问题，您需要NO_ZERO_DATE从 mysql 模式配置中删除。

1. Go to 'phpmyadmin'.
2. Once phpmyadmin is loaded up, click on the 'variables' tab.
3. Search for 'sql mode'.
4. Click on the Edit option and remove NO_ZERO_DATE(and its trailing comma) from the configuration.

1. 转到“phpmyadmin”。
2. 加载 phpmyadmin 后，单击“变量”选项卡。
3. 搜索“sql 模式”。
4. 单击编辑选项并NO_ZERO_DATE从配置中删除（及其尾随逗号）。

This is a very common issue in the local environment with wamp or xamp.

这在wamp或xamp的本地环境中是一个很常见的问题。

回答by Ashish Odich

Just Define following lines at top of your Database SQL file.

只需在数据库 SQL 文件的顶部定义以下几行即可。

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

It is working for me.

它对我有用。

回答by Jatin Bhatti

To disable strict SQL mode

禁用严格的 SQL 模式

Create disable_strict_mode.cnf file at /etc/mysql/conf.d/

In the file, enter these two lines:

在文件中，输入以下两行：

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Finally, restart MySQL with this command:

最后，使用以下命令重新启动 MySQL：

sudo service mysql restart