mysql创建表时，设置timestamp DEFAULT NULL报错1067 - Invalid default value for 'updated_at'

问题背景：

线上的linux服务器上的mysql服务器中导出数据库的结构。想要在本地创建一个测试版本

导出后再本地mysql上运行却报错   1067 - Invalid default value for 

 

mysql数据库中需要使用timestamp列来存储数据的创建时间和更新时间

例如，创建后台管理菜单表，只看created_at和updated_at两个字段

DROP TABLE IF EXISTS `admin_menu`;
CREATE TABLE `admin_menu`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `order` int(11) NOT NULL DEFAULT 0,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uri` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` timestamp(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

执行后发现报错

1067 - Invalid default value for 'updated_at'。updated_at字段的默认值无效。而created_ad字段类型是datetime日期时间类型默认值为NULL没有发生报错

 一开始以为是sql_mode模式问题，然而在sql_mode模式为严格或者宽松模式情况下，都会报错。

本地和线上服务器相同的模式也不行

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

接着，查看线上服务器版本和本地版本对比，

使用select VERSION()

线上mysql版本：5.7.22-log

本地mysql版本：5.7.23-log

版本相差不大，排除版本问题。那只能找资料了。

 

解决方法：

方法1

既然timestamp会报错，就直接用datetime来保存时间就可以解决的。

将updated_at类型timestamp改为datetime，这个创建表的过程能够正常运行

 

方法2

既然不能为NULL,那我改为一个固定值不就可以了吗

`updated_at` timestamp(0) DEFAULT '1970-01-01 08:00:00',

将时间设置为unix时间戳为0对应的日期(默认是1970-01-01 00:00:00，中国时间要加8)。

 

方法3

网上查找，在mysql配置文件中增加配置参数

[mysqld]节点下添加

explicit_defaults_for_timestamp = ON

重启mysql数据库试配置生效。

之后就可以正常执行上面的数据的

 

下面是方法三的具体解释，仅作记录，可以不需要知道

网上搜索资料，

 

mysql5.6.6之前，timestamp时间类型有一个默认行为：
TIMESTAMP列如果没有明确声明NULL属性，默认为NOT NULL。（而其他数据类型，如果没有显示声明为NOT NULL，则允许NULL值。）
insert插入一条数据，TIMESTAMP的列值为NULL，会自动存储时候，会将当前timestamp存储到这个timestamp列中。

也就是说会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性。

每次更新记录都会将timestamp列更新为当前的时间戳对应的时间值

 

 

现在mysql5.6版本以后，timestamp字段的默认行为发生的变化，多了一些限制。

如果timestamp列设置默认值为NULL,

Default NULL 这会发生报错 1067 - Invalid default value for

 

如果需要让timestamp列在创建表时可以为NULL值，需要将explicit_defaults_for_timestamp设为ON

explicit_defaults_for_timestamp默认为OFF关闭状态，打开后可以阻止timestamp的默认行为。