Mysql 时间日期类型

一、概述

Mysql 中有多种数据类型可以用于日期和时间的表示,不同的版本可能会存在差异,下面的表格中列出了 Mysql 5.7 中所支持的日期和时间类型

日期和时间类型	存储占用磁盘空间大小	最小值	最大值
YEAR	1 个字节	1901	2155
TIME	3 个字节	-839:59:59	839:59:59
DATE	4 个字节	1000-01-01	9999-12-31
TIMESTAMP	4 个字节	1970 年的某个时刻	2038 年的某个时刻
DATETIME	8 个字节	1000-01-01 00:00:00	9999-12-31 00:00:00

每一种时间日期格式都有一个有效范围,如果超出这个时间范围,在默认的 SQLMode 下,系统会进行错误提示,并且都以 "零值" 来进行存储

二、各种类型的区别

每一种时间日期类型都有不同的使用场景,一般来说使用最多的是 TIME、DATE、TIMESTAMP、DATETIME,下面就来简单看一下这几种类型的区别

创建 test 表

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `d` date NOT NULL DEFAULT '1900-01-01' COMMENT '年月日格式',
  `t` time NOT NULL DEFAULT '00:00:00' COMMENT '时间格式',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '年月日时分秒',
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '年月日时分秒',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

需要注意的是 date 类型不能设置默认值 current_date, time 类型也不能设置默认值 current_time,需要用字符串设置一个默认的时间日期格式

现在使用 now() 函数插入一组数据

insert into test (d,t,ts,dt) values (now(),now(),now(),now());

查看插入的数据

从上面的信息可以得知

date 表示的是 年月日 格式

time 表示的是 时分秒 格式

timestamp 和 datetime 都是表示 年月日时分秒 格式,并且都可以设置默认值 CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性,使得日期列可以随其它列的更新而自动更新(一般建表时 update_time 字段设置为 default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

timestamp 和 datetime 都可以表示 ''年月日 时分秒",但是它们之间还是存在一些区别的,具体如下

1、取值范围

timestamp 表示的范围较小,其取值范围从  1970 年的某个时刻  到 2038 年的某个时刻,而 datetime 的取值范围是 1000-01-01 00:00:00 到 9999-12-31 00:00:00

2、存储时占用空间大小

timestamp 存储时占用磁盘空间大小是 4 个字节,而 datetime 是 8 个字节

3、时区影响

timestamp 插入日期时,会先将日期时间转换为本地时区后存放,查询日期时也会先将日期转换为本地时区后显示,这样不同的时区用户看到的同一个日期可能是不一样的,由于 timestamp 插入和查询都受当地时区的影响,所以更能反映出实际的日期,而 datetime 只能反映出插入时当地的时区,其它时区的用户查看数据时必然会收到影响,也就是说 timestamp 会根据时区自适应,而 datetime 不会

 

三、应用场景

如何选择合适的日期时间类型

1、先分析需求,根据实际需要选择能够满足应用的最小存储的日期类型,如果只需要记录 "年份",那么选用存储只占用一个字节的 YEAR 类型就可以了,而不用选择需要使用 4 个字节来存储的 DATE 类型,这样不仅可以节约存储空间,还能提升表的操作性能

2、如果是要记录 "年月日时分秒",并且记录的年份跨度范围比较大,那么最好使用 DATETIME,而不要使用 TIMESTAMP,因为 TIMESTAMP 记录的时间范围是 1970~2038,如果超出该范围之后就会以零值来进行填充

3、如果记录的日期类型是要让不同时区的用户使用,那么最好选用 TIMESTAMP,因为日期时间类型中只有它可以和实际的时区相适应