MySQL 错误 1067 (42000)：“created_at”的默认值无效

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

提问by iamsujit

When I tried to alter the table it showed the error:

当我尝试更改表格时，它显示了错误：

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

I googled for this error but all I found was as if they tried to alter the timestamp so it occurred. However here I am trying to add a new column and I am getting this error:

我用谷歌搜索了这个错误，但我发现的只是他们试图改变时间戳所以它发生了。但是，在这里我尝试添加一个新列，但出现此错误：

mysql> ALTER TABLE investments ADD bank TEXT;
ERROR 1067 (42000): Invalid default value for 'created_at'

and my table's last two columns are created_atand updated_at.

我的表的最后两列是created_atand updated_at。

Here is my table structure:

这是我的表结构：

回答by Aman Aggarwal

The problem is because of sql_modes. Please check your current sql_modes by command:

问题是因为sql_modes。请通过命令检查您当前的 sql_modes：

show variables like 'sql_mode' ; 

And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work. This is the default sql_mode in mysql new versions.

并删除 sql_mode " NO_ZERO_IN_DATE,NO_ZERO_DATE" 以使其工作。这是 mysql 新版本中的默认 sql_mode。

You can set sql_mode globally as root by command:

您可以通过命令将 sql_mode 全局设置为 root：

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

回答by Ahmed Mohamed

Simply, before you run any statements put this in the first line:

简单地说，在你运行任何语句之前，把它放在第一行：

SET sql_mode = '';

回答by Martin Mohan

I came across the same error while trying to install a third party database. I tried the solution proposed unsuccessfully i.e.
SET sql_mode = '';

我在尝试安装第三方数据库时遇到了同样的错误。我尝试了未成功提出的解决方案，即
SET sql_mode = '';

Then I tried the command below which worked allowing the database to be installed
SET GLOBAL sql_mode = '';

然后我尝试了下面的命令，该命令允许安装数据库
SET GLOBAL sql_mode = '';

回答by Michele La Ferla

Try and run the following command:

尝试运行以下命令：

ALTER TABLE `investments` 
MODIFY created_at TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP 
NOT NULL;

and

和

ALTER TABLE `investments` 
MODIFY updated_at TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP 
NOT NULL;

The reason you are getting this error is because you are not setting a default value for the created_atand updated_atfields. MySQL is not accepting your command since the values for these columns cannot be null.

您收到此错误的原因是您没有为created_at和updated_at字段设置默认值。MySQL 不接受您的命令，因为这些列的值不能为空。

Hope this helps.

希望这可以帮助。

回答by nyenius

Run this query:

运行此查询：

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

it works for me

这个对我有用

回答by Tomás

In my case I have a file to import. So I simply added SET sql_mode = ''; at the beginning of the file and it works!

就我而言，我有一个要导入的文件。所以我简单地添加了 SET sql_mode = ''; 在文件的开头，它的工作原理！

回答by Bernd Buffen

You can do it like this:

你可以这样做：

 CREATE TABLE `ttt` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `t1` TIMESTAMP  NULL DEFAULT '0000-00-00 00:00:00',
  `t2` TIMESTAMP  NULL DEFAULT '0000-00-00 00:00:00',
  `t3` TIMESTAMP  NULL DEFAULT '0000-00-00 00:00:00',
  `t4` TIMESTAMP  NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• Because the TIMESTAMP value is stored as Epoch Seconds, the timestamp value '1970-01-01 00:00:00' (UTC) is reserved since the second #0 is used to represent '0000-00-00 00:00:00'.
• In MariaDB 5.5 and before there could only be one TIMESTAMP column per table that had CURRENT_TIMESTAMP defined as its default value. This limit has no longer applied since MariaDB 10.0.

• 由于 TIMESTAMP 值存储为 Epoch Seconds，因此保留时间戳值 '1970-01-01 00:00:00' (UTC)，因为第二个 #0 用于表示 '0000-00-00 00:00:00 '。
• 在 MariaDB 5.5 及之前，每个表只能有一个 TIMESTAMP 列，将 CURRENT_TIMESTAMP 定义为其默认值。自 MariaDB 10.0 起，此限制不再适用。

see: https://mariadb.com/kb/en/mariadb/timestamp/

见：https: //mariadb.com/kb/en/mariadb/timestamp/

sample

样本

MariaDB []> insert into ttt (id) VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB []> select * from ttt;
+----+---------------------+---------------------+---------------------+---------------------+
| id | t1                  | t2                  | t3                  | t4                  |
+----+---------------------+---------------------+---------------------+---------------------+
|  1 | 0000-00-00 00:00:00 | 2000-01-01 12:01:02 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  2 | 0000-00-00 00:00:00 | 2000-01-01 12:01:02 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  3 | 0000-00-00 00:00:00 | 2000-01-01 12:01:02 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

MariaDB []>

回答by Bhushan

I had similar problem. Following solved it:

我有类似的问题。以下解决了它：

Change:

改变：

recollect_date TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP',

recollect_date TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP',

to:

到：

recollect_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

recollect_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

i.e. just remove the quotes around CURRENT_TIMESTAMP.

即只需删除CURRENT_TIMESTAMP周围的引号。

Hope this helps someone.

希望这可以帮助某人。

回答by Hassaan

As mentioned in @Bernd Buffen's answer. This is issue with MariaDB 5.5, I simple upgrade MariaDB 5.5 to MariaDB 10.1 and issue resolved.

正如@Bernd Buffen 的回答中所述。这是 MariaDB 5.5 的问题，我简单地将 MariaDB 5.5 升级到 MariaDB 10.1 并解决了问题。

Here Steps to upgrade MariaDB 5.5 into MariaDB 10.1 at CentOS 7 (64-Bit)

在 CentOS 7 （64 位）上将 MariaDB 5.5 升级到 MariaDB 10.1 的步骤在这里

1. Add following lines to MariaDB repo.

   nano /etc/yum.repos.d/mariadb.repoand paste the following lines.

1. 将以下行添加到 MariaDB 存储库。

   nano /etc/yum.repos.d/mariadb.repo并粘贴以下几行。

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey= https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

1. Stop MariaDB, if already running service mariadb stop

2. Perform update

   yum update

3. Starting MariaDB & Performing Upgrade

   service mariadb start

   mysql_upgrade

2. 停止 MariaDB（如果已经运行） service mariadb stop

3. 执行更新

   yum update

4. 启动 MariaDB 并执行升级

   service mariadb start

   mysql_upgrade

Everything Done.

一切都完成了。

Check MariaDB version: mysql -V

检查 MariaDB 版本： mysql -V

---

---

NOTE注意：在执行升级之前，请始终备份数据库。如果升级失败或出现问题，数据可能会丢失。

回答by ondi gusho

SET GLOBAL sql_mode = '';

Resolved my problem.

解决了我的问题。