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的问题