MySQL timestamp时间类型

引发对时间类型研究,源于一个生产bug:

数据库中timestamp类型的列,出现了’0000-00-00 00:00:00’,而应用访问数据库是用的mybatis,抛出SQL异常java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp, ‘0000-00-00 00:00:00’无法转换为timestamp类型

关键表结构如下:

1
2
3
4
5
6
7
CREATE TABLE ttt(
id INT NOT NULL AUTO_INCREMENT ,
t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
t2 TIMESTAMP DEFAULT '0000-00-00 00:00:00',
t3 DATETIME DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
);

表结构定义中的DEFAULT ,INSERT或者UPDATE的时候,如果没有显式设置t2的值,那么,将会使用defalut值,也就是0000了,如果显式设置了NULL,那么,此列就是NULL。

timestamp 我们都知道,时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数,最大可表示到2038-01-19 03:14:07年。

那么,不合法的’0000-00-00 00:00:00’是如何存到数据库中呢。

1. 时间不合法,不在时间戳可表示范围内,是否可以执行

1
2
3
4
5
6
7
INSERT INTO `test`.`ttt` (`id`, `t1`, `t2`)
VALUES
(NULL, NOW(), '2038-05-23 18:30:12') ;
INSERT INTO `test`.`ttt` (`id`, `t1`, `t2`)
VALUES
(NULL, NOW(), '1900-05-23 18:30:12') ;

不合法的日期,可以插入成功,但是,会被mysql转成’0000-00-00 00:00:00’。

2. 表结构定义的defalut值生效条件

设置NULL ,就是NUll,不显式进行设置,才会使用defalut

1
2
3
4
5
6
7
INSERT INTO `ttt` (`id`, `t1`)
VALUES
(NULL, NOW()) ;
INSERT INTO `ttt` (`id`, `t1`, `t2`)
VALUES
(NULL, NOW(), NULL) ;

3. 时间比较

如果数据库中存的数据为NULL了,那么其不能进行大小比较了。

1
SELECT * FROM ttt t WHERE t.`t2`<NOW();

查不到NULL结果

但是NULL会参与排序,在MySQL中,是最小值

1
SELECT * FROM ttt ORDER BY t2

4.timestamp和datetime该如何选择

timestamp可表示范围小,占用空间小(4字节)

datetime可表示范围大(’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’),占用空间大(8字节)

timestamp一般用来记录更新时间,通过设置CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP 在5.5中只能设置一列,数据更新时自动更新时间戳,但是在5.7.11中,可以设置多列自动更新时间戳了!