Mysql timestamp整理

前几天线上一个接口请求异常，导致一个页面挂了。赶紧查了下日志，发现了这么一段

 

ERROR [http-bio-2206-exec-91] (SqlExceptionHelper.java:146) org.hibernate.engine.jdbc.spi.SqlExceptionHelper
 - Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

 

查了下表数据，有个timestamp字段的值，被更新成了'0000-00-00 00:00:00'。通常我们会为自动生成的日期赋CURRENT_TIMESTAMP的值，看了下表结构。有3个timestamp字段。UpdateTime字段默认值为CURRENT_TIMESTAMP，而另外2个为'0000-00-00 00:00:00'，出问题的就是RecommendTime字段。

timestamp的时间范围不同于datetime的'1000-01-01 00:00:00'~'9999-12-31 23:59:59'，他与时区相关，在中国他的时间范围是'1970-01-01 08:00:01'~'2038-01-19 11:14:07'，对应UTC时间'1970-01-01 00:00:01'~'2038-01-19 03:14:07'。（UTC时间等同GMT时间，是基准时间）。timestamp是4字节存utc毫秒数，datetime是8字节存实际格式。

尝试将另外2个字段的默认值改为CURRENT_TIMESTAMP，结果数据库提示

 

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP 
in DEFAULT or ON UPDATE clause

 

说明只能有一个字段的默认值为CURRENT_TIMESTAMP。timestamp 默认允许为 “非空”（not null by default），其他建表时没指定自己的默认值，默认变成'0000-00-00 00:00:00'，但这对于Hibernate来说不是一个有效的时间。
解决方式有2种：
1）连接语句加上zeroDateTimeBehavior=round或者zeroDateTimeBehavior=convertToNull，两者的区别是前者转换成'0001-01-01 00:00:00'，而后者转换成null。
2）修改默认值，可以改成 '1970-01-01 12:00:00'

-------------
update 2017-6-23 20:31:43
发现在mysql 5.6.21 支持设置多列的默认值为 CURRENT_TIMESTAMP

-------------
update 2017-11-1 17:08:30

mysql 5.6.4 支持定义TIMESTAMP(3)，用CURRENT_TIMESTAMP(3)赋值，可取毫秒

-------------
update 2018-9-21 16:35:56

修改timestamp->timestamp(3)

ALTER TABLE `stu_homework`   
  CHANGE `create_time` `create_time` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)   NOT NULL,
  CHANGE `update_time` `update_time` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)  NOT NULL;