为什么在MySQL中创建表时，TIMESTAMP字段的默认值会无效？

MySQL 1067错误：建表时timestamp字段默认值无效

在MySQL数据库中，当使用CREATE TABLE语句创建新表时，如果指定了TIMESTAMP类型的字段并设置了一个无效的默认值，就会抛出一个错误码为1067的错误，这个错误通常发生在严格模式（STRICT_TRANS_TABLES）下，因为此时MySQL不允许非法数据插入，本文将详细介绍该问题的原因、解决方法及常见问题解答。

一、问题原因

MySQL对TIMESTAMP类型字段的默认值有严格的要求，TIMESTAMP列的默认值不能是NULL或不合法的日期时间字符串，以下SQL语句会导致1067错误：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT NULL
);

这是因为create_time字段的默认值被设置为NULL，而MySQL不允许TIMESTAMP列有这样的默认值。

二、解决方法

1. 修改默认值为当前时间戳

最常见的解决方法是将TIMESTAMP列的默认值设置为CURRENT_TIMESTAMP，这表示每次插入新记录时，如果没有提供该字段的值，则自动使用当前的日期和时间。

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

或者，如果需要记录最后一次更新的时间，可以使用ON UPDATE CURRENT_TIMESTAMP：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 使用合法的默认日期时间字符串

另一种方法是将TIMESTAMP列的默认值设置为一个合法的日期时间字符串，如’1970-01-01 00:00:00’：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT '1970-01-01 00:00:00'
);

这种方法适用于那些希望手动控制默认值的情况。

3. 修改SQL模式

如果不想更改字段定义，也可以通过修改SQL模式来允许NULL作为默认值，具体步骤如下：

1、查看当前SQL模式：

SELECT @@sql_mode;

2、修改SQL模式以禁用NO_ZERO_DATE和NO_ZERO_IN_DATE选项：

SET GLOBAL sql_mode='IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这种方法虽然可以解决问题，但可能会引入其他潜在的数据完整性问题，因此应谨慎使用。

4. 修改explicit_defaults_for_timestamp参数

在某些版本的MySQL中，可以通过设置explicit_defaults_for_timestamp参数来控制TIMESTAMP列的行为，将其设置为ON可以阻止TIMESTAMP列的隐式默认行为：

1、编辑MySQL配置文件（如my.cnf或my.ini），添加以下行：

[mysqld]
explicit_defaults_for_timestamp=ON

2、重启MySQL服务使配置生效：

For Linux systems
sudo systemctl restart mysqld
For Windows systems
net stop mysql
net start mysql

三、常见问题解答

Q1: 为什么MySQL不允许将NULL作为TIMESTAMP列的默认值？

A1: MySQL不允许将NULL作为TIMESTAMP列的默认值是因为NULL不是一个有效的日期时间值，TIMESTAMP列需要存储实际的日期和时间信息，而NULL无法满足这一要求，为了保证数据的完整性和准确性，MySQL禁止这样做。

Q2: 如何更改现有表中的TIMESTAMP列的默认值？

A2: 要更改现有表中的TIMESTAMP列的默认值，可以使用ALTER TABLE语句结合MODIFY子句来实现，要将create_time列的默认值改为当前时间戳，可以执行以下SQL语句：

ALTER TABLE example MODIFY create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

同样地，也可以将其改为一个合法的日期时间字符串或其他合适的默认值，在修改过程中要确保新的默认值符合MySQL的要求，并且不会破坏现有的数据完整性。