SQLIntegrityConstraintViolationException: Column ‘create_time‘ cannot be null

概述

在使用MySQL + MyBatis时遇到的问题，记录一下。

问题

在测试环境里，往MySQL数据表里插入数据时报错：SQLIntegrityConstraintViolationException: Column 'create_time' cannot be null

表结构字段定义：

create_time datetime default CURRENT_TIMESTAMP not null comment '创建日期',

备注：
MySQL数据库版本：

select version();
5.7.30

使用的MyBatis版本：

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.0.3</version>
</dependency>

使用的MySQL驱动版本：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

排查

参考mysql-insert-error-cannot-be-null-datetime-not-null-default-current-timestamp。

给出的解释是：

The MySQL 5.7 manual states that…

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

The manual does not say you can do this for DATETIME fields.

简单翻译下，就是TIMESTAMP字段能够实现插入当前时间，datetime字段则不一定能保证。

修改测试环境下的表结构定义语句：

create_time timestamp default CURRENT_TIMESTAMP not null comment '创建日期',

stackoverflow还是非常靠谱，问题解决。

datetime & timestamp

相同：都可以表示YYYY-MM-DD HH:MM:SS这种年月日时分秒格式的数据。MySQL5.6.4之前，都不能表示小数。MySQL5.6.4后这两者都可以包含秒后的小数部分，精度最高为微妙（6位）

不同：

• 存储范围
  datetime的存储范围是1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999，而timestamp的范围是1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999(准备的来讲应该是UTC范围)；
• 时区
  datetime存储与时区无关（准备来说是datetime只支持一个时区，即存储时当前服务器的时区），而timestamp存储的是与时区有关。
  MySQL在存储TIMESTAMP时，会先将时间从当前服务器的时区转换为UTC（世界协调时）以进行存储，然后查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库的时区而发生改变。而datetime的存储则与时区无关，数据是什么就存储什么，也就返回什么
• 存储大小
  在5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节；但是在5.6.4之后，由于这两个类型允许有小数部分，所以占用的存储空间和以前不同；
  MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度

反思

除上面的create_time字段在insert到数据库时没有设置系统当前时间，另外还有一个字段is_delete也有类似问题：

is_delete       int(1)      default 0                 null comment '是否删除 0-未删除 1-已删除',

理论上代码层面没有设置is_delete时，落到数据库时使用default值，即0。

这就很懵逼啊，代码就那么几行。

突然发现使用的时MyBatis的insert方法，而没有使用insertSelective方法。

insert & insertSelective

insert这个API转化成XML语法如下：

<insert id="insert">
INSERT INTO merchant_app(id,is_delete,create_time) values (#{id,jdbcType=BIGINT}, #{isDelete,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP})
</insert>

转换成SQL如下：

INSERT INTO merchant_app(id,is_delete,create_time) VALUES( ?,?,? )

insertSelective这个API转化成XML语法如下：

insert into HSP_MEDIA_INF
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="isDelete != null" >
        is_delete,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="isDelete != null" >
        #{isDelete,jdbcType=INTEGER},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>

发现insertSelective对应的sql语句加入了NULL校验，只会插入数据不为null的字段值。insert则会插入所有字段，会插入null

INSERT INTO merchant_app(id,is_delete,create_time) VALUES( ?,?,? )

也就是说，此时即使数据表字段有default定义，遇到MyBatis的insert方法，也无能为力。

验证

不再使用insert方法，而使用insertSelective方法，同时，把数据表的TIMESTAMP类型改回为datetime类型：

alter table merchant_app modify create_time datetime default CURRENT_TIMESTAMP not null comment '创建日期';

可以插入当前时间now()数据。

参考