DATE
(YYYY-MM-DD) 范围 '1000-01-01' to '9999-12-31' 

DATETIME 支持自动更新
(YYYY-MM-DD HH:MM:SS) 范围 '1000-01-01 00:00:00' to '9999-12-31 23:59:59' 

TIMESTAMP 支持自动更新
(YYYY-MM-DD HH:MM:SS)范围 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

INT 
整数 范围 '1970-01-01 00:00:01' to '2038-01-19 03:14:07'


时间范围  DATETIME > TIMESTAMP = INT

使用方便程度 带有自动更新 DATETIME , TIMESTAMP > INT

时间精度  DATETIME > TIMESTAMP > INT

存储空间 同一精度  DATETIME > TIMESTAMP = INT

查询速度 底层都是整型存储  DATETIME = TIMESTAMP = INT


CREATE TABLE test002 (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT  ,
  date1 date,
  dt1 DATETIME  DEFAULT CURRENT_TIMESTAMP,
  dt2 DATETIME ON UPDATE now(),
  dt3 DATETIME ,
  ts1 timestamp  DEFAULT CURRENT_TIMESTAMP,
  ts2 timestamp  ON UPDATE CURRENT_TIMESTAMP,
  ts3 timestamp,
  value varchar(32) not null unique,
  PRIMARY KEY (id) USING BTREE
) 

insert into test002(date1,dt3,ts3,value) values('2021-08-11','2021-08-11 09:09:09','2021-08-11 09:09:09','a')

insert into test002(date1,dt3,ts3,value) values('2021-08-11','2021-08-11 09:09:09',1628651822,'b')


update test002 set ts2=null,ts3=null where id>0


CREATE TABLE `test002` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date1` date DEFAULT NULL,
  `dt1` datetime DEFAULT CURRENT_TIMESTAMP,
  `dt2` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `dt3` datetime DEFAULT NULL,
  `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `ts3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `value` varchar(32) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

insert into test002(date1,dt3,ts3,value) values('2021-08-11','2021-08-11 08:08:08',null,'c')
插入一条记录 ， ts2 插入默认值 '0000-00-00 00:00:00'
select id,value,ts1,ts2 from test002 where value='c'
查询时报错
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

连接串增加
&zeroDateTimeBehavior=convertToNull

把 ts2 更新成Null之后 ok


insert into test002(date1,dt3,ts2,ts3,value) values('2021-08-11','2050-08-11 09:09:09',null,null,'d')


select * from test002 where value='d'

select UNIX_TIMESTAMP(dt3) from test002 where value='d'
2050-08-11 09:09:09 超出范围 返回 0
select UNIX_TIMESTAMP(dt3) from test002 where value='a'
2021-08-11 09:09:09 返回 1628644149

select FROM_UNIXTIME(1628644149)  
2021-08-11 09:09:09.0