1067 - Invalid default value for 问题
问题描述: 今天在用powerdesinger 创建表结构模型时 有一个字段类型 timestamp 类型, 在执行创建表结构的语句是报错 1067 - Invalid default value for ‘deleted_at’
数据库版本: 5.6.36
问题表结构如下**(实际上这个语句没有问题,和版本及数据库配置有关系)**
create table scrm_sop_general
(
id int(11) not null auto_increment comment 'id',
deleted_at timestamp default NULL comment '删除时间',
primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test表';
解决方案
1.解决问题前查看项
首先用下面的命令看下sql_mode
show variables like 'sql_mode';
如果查询的结果如下:
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
如果结果中含有NO_ZERO_IN_DATE, NO_ZERO_DATE
则执行下面的命令:
set session
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
如果是解压版的mysql 配置如下图
解决方式1.
第一种解决方式就是在 timestamp 后面加 null (表多工作量大)
create table scrm_sop_general
(
id int(11) not null auto_increment comment 'id',
deleted_at timestamp null default NULL comment '删除时间',
primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test表';
解决方式 2. (推荐方式)
网上查找,在mysql配置文件中增加配置参数
[mysqld]节点下添加
explicit_defaults_for_timestamp = ON
重启mysql数据库试配置生效。
之后就可以正常执行上面的说的问题语句
以下是处理成功后的完整的my.ini,可作为参考
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
max_allowed_packet = 100M
#设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:\1_app\mysql-5.7.39-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\1_app\mysql-5.7.39-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
innodb_lock_wait_timeout=500
explicit_defaults_for_timestamp = ON
下面是 解决方式2 具体解释,仅作记录,可以不需要知道
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的默认行为。
=================
逃避式 解决方法:
逃避问题方式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)。