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

时间：2021-04-04 11:45:05

问题背景：

线上的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之前，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的默认行为。

相关文章