mysql timestamp NULL 问题处理

问题描述

在进行mysql的8.0到5.7数据结构迁移时
在5.7执行

create table `t1`(
field timestamp NOT NULL commont 'commot'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行结果如下：

[42000][1067] Invalid default value for 'field'

然而在8.0中执行却没问题
因此需要解决这一个类型的问题

• 这里的field其实默认可以为空

  两个方向解决：

  1. 可以将上述执行成功
  2. 将值默认设置值可为NULL

问题解决过程

1. 出现问题先搜索网络解决方法，没找到
2. 查询官方文档得到5.7时，timestamp默认值为NULL的时候需要开启explicit_defaults_for_timestamp, 在8.0此参数默认开启
   5.7的参数
   8.0的参数
3. 当不进行系统变量更改的时候，在文档中找到

If the `TIMESTAMP` column permits `NULL` values but its definition does not include `DEFAULT CURRENT_TIMESTAMP`, 
you must explicitly insert a value corresponding to the current date and time. 
Suppose that tables `t1` and `t2` have these definitions :

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

问题答案

1. 第一种方案，在5.7中将系统变量explicit_defaults_for_timestamp设置为开启ON
   • set explicit_defaults_for_timestamp = 'NO';
2. 第二种方案，显示指定字段类型为NULL，可以处理这个问题，如果单单是设置默认值也需要显示声明NULL

create table `t1`(
field timestamp NULL [default xxx] commont 'commot'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;