mysql - timestamp not null field insert null value

1. 问题描述

我们创建的数据库表的结构如下：

CREATE TABLE `gw_service_provider` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uuid` varchar(50) NOT NULL DEFAULT '' COMMENT '该记录的唯一标识，用于表关联',
  `provider_code` varchar(20) NOT NULL DEFAULT '' COMMENT '服务提供方编码',
  `provider_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务提供方名称',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_user` varchar(50) DEFAULT NULL COMMENT '修改用户',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳，用户判定记录是否发生变更',
  `dr` tinyint(4) DEFAULT NULL COMMENT '0: 未删除；1: 已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='服务提供方';

请注意其中的ts字段的配置，该字段是timestamp类型，非空，默认值为当前数据库时间，数据发生变更时会自动更新该字段的值。

我们存在两套环境：测试环境、线上环境
测试环境下执行插入操作，ts字段设置为NULL。插入成功！

INSERT INTO gw_service_provider(`uuid`, `provider_code`, `provider_name`, `create_time`, `create_user`, `ts`, `dr`)
VALUES ('uuid-002', 'test-001', 'test-002', NOW(), 'admin', NULL, 0);

但是，线上环境执行同样的语句插入失败，错误信息如下：

org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'ts' cannot be null
### The error may involve com.jd.chainet.gateway.dao.GwServiceProviderMapper.insert-Inline
### The error occurred while setting parameters

2. 问题分析

测试环境和线上环境的区别在于，他们对timestamp字段插入NULL的处理逻辑不同，在测试环境中是允许向非空字段ts插入NULL值的，它默认会将ts字段设置为数据库当前时间，但是线上环境却不允许向ts字段插入NULL值。

测试环境数据库版本： 5.5.14-log
线上环境数据库版本： 5.6.36-log

经查证发现：MySQL5.6版本后引用了新的参数 explicit_defaults_for_timestamp
如果不开启该参数，则对timestamp NOT NULL插入NULL值，不报错，无warning，插入后的值为当前时间
如果开启该参数，则插入NULL时会提示 Column ‘xxx’ cannot be null。

3. 结论

测试环境5.5.14-log并不存在explicit_defaults_for_timestamp参数，它的默认操作是允许对 timestamp NOT NULL字段插入NULL值，此时会设置timestamp字段为数据库当前时间。

线上环境5.6.36-log存在explicit_defaults_for_timestamp参数，且为开启状态，因此不允许对 timestamp NOT NULL字段插入NULL值。
如果关闭该参数，则操作与MySQL 5.6 之前版本的操作保持一致。

show variables like '%explicit_defaults_for_timestamp%'

参考

MySQL timestamp NOT NULL插入NULL的问题