在日常开发中,基本都会使用到时间数据,在MySQL中也存在着数个时间类型的数据,那这么多时间数据,我们该如何选择使用呢?本文就来讨论一下MySQL中时间数据类型的选择以及使用.

MySQL时间类型

MySQL的时间类型一共有5个,分别为:DATA、DATATIME、TIME、TIMESTAMP、YEAR,每个数据都有其适应范围,下边分别进行介绍.

1. DATE

DATE数据类型,用来存储日期,不包含时间信息,在应用中可以存储如生日等数据.

MySQL默认的展示格式: YYYY-MM-DD

日期范围 1000-01-01 ~ 9999-12-31.

针对日期显示,也支持数据格式: YY-MM-DD,两位年的显示,遵循以下规则:

70-99 范围内的日期,MySQL会解释为: 1970-199900-69 范围内的日期,MySQL会解释为 2000-20692. DATETIME

DATETIME数据类型,用来存储日期以及时间.

MySQL默认的展示格式: YYYY-MM-DD hh:mm:ss[.fraction]

表示时间范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999

在使用中,可以指定 DATETIME[(fsp)],其中fsp即时间展示的小数部分,用来表示更加精确的时间范围.

fsp的取值范围: 0-6, 为0或者忽略fsp值时,表示时间抛弃小数部分,只保留到秒的数据

时间精度

时间单位主要有: 秒、毫秒、微妙、纳秒等,他们的时间转换为如下:

1s = 1000ms

1ms = 1000μs

1μs = 1000ns

从以上的时间换算可以看出,MySQL最小能支持的时间单位为微妙

以下示例可以更好的理解数据精度信息

CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );INSERT INTO fractest VALUES('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

进行数据查询时,可以看到如下信息:

mysql> SELECT * FROM fractest;+-------------+------------------------+------------------------+| c1          | c2                     | c3                     |+-------------+------------------------+------------------------+| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |+-------------+------------------------+------------------------+

在开启 TIME_TRUNCATE_FRACTIONAL SQL mode时,数据查询返回结果又会有所不同(舍弃最后一位数据)

SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

mysql> SELECT * FROM fractest;+-------------+------------------------+------------------------+| c1          | c2                     | c3                     |+-------------+------------------------+------------------------+| 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 |+-------------+------------------------+------------------------+

TIME数据类型,用来存储时间,不包含日期信息.

MySQL默认的展示格式: hhh:mm:ss[.fraction]

表示时间范围 -838:59:59.000000 ~ 838:59:59.000000

可以用来表示某个时间点或者某个时间经历的时间范围,所以TIME的范围可以大于24

在使用中,可以指定 TIME[(fsp)],其中fsp即时间展示的小数部分,用来表示更加精确的时间范围.

fsp的取值范围: 0-6, 为0或者忽略fsp值时,表示时间抛弃小数部分,只保留到秒的数据

可以参考DATETIME段关于fsp的使用示例

4. TIMESTAMP

TIMESTAMP数据类型,用来存储日期以及时间.

MySQL默认的展示格式: YYYY-MM-DD hh:mm:ss[.fraction]

表示时间范围 1970-01-01 00:00:01.000000 UTC ~ 2038-01-19 03:14:07.499999 UTC

和DATETIME有所不同, MySQL存储TIMESTAMP时间时,会将本地时间转换为UTC时间,在取出展示时将数据转换为本地时间.

在使用中,可以指定 TIMESTAMP[(fsp)],其中fsp即时间展示的小数部分,用来表示更加精确的时间范围.

fsp的取值范围: 0-6, 为0或者忽略fsp值时,表示时间抛弃小数部分,只保留到秒的数据

可以参考DATETIME段关于fsp的使用示例

TIMESTAMP的默认0时间数据为: 1970-01-01 00:00:00

5. YEAR

YEAR数据类型,MySQL使用1B内存用来存储日期年,不包含月、日、时间等信息.

MySQL默认的展示格式: YYYY

表示时间范围 1901 ~ 2155.

如果是4个字符表示的年,则时间范围 ‘1901’ ~ ‘2155’

如果是4个数字表示的年,则时间范围 1901 ~ 2155

年的数据可以使用2位数字进行表示,其表示范围如下:

作为字符串存储时

‘0’ ~ ‘69’ 表示年份范围 2000 ~ 2069

‘70’ ~ ‘99’ 表示年份范围 1970 ~ 1999

作为数字存储时

1 ~ 69 表示年份范围 2001 ~ 2069

70 ~ 99 表示年份范围 1970 ~ 1999

如果想要存储年为2000年时,可以直接设置year_col=‘0’ 或者 year_col=‘00’

时间的零值

每个数据类型都有其自身的零值时间,可以参考下图:

Data Type

“Zero” Value

DATE

‘0000-00-00’

TIME

‘00:00:00’

DATETIME

‘0000-00-00 00:00:00’

TIMESTAMP

‘0000-00-00 00:00:00’

YEAR

0000

时间自动更新

DATETIME和TIMESTAMP均可以指定默认值以及自动更新到当前值,有以下几种情况可以进行操作

同时包含 DEFAULT CURRENT_TIMESTAMP 以及 ON UPDATE CURRENT_TIMESTAMP.

如下数据表,ts、dt拥有默认值当前时间以及在数据更新时会自动更新到当前值

CREATE TABLE t1 (   ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

时间转换过程中,会造成时间数据精度的丢失,主要是数据类型包含的信息不同以及数据类型对应的有效时间范围不同

DATE数据转换转换为DATETIME 或者 TIMESTAMP,将在DATE日期后添加 ‘00:00:00’,因为DATE数据没有时间信息转换为TIME,则为 ‘00:00:00’,因为DATE数据没有时间信息DATETIME、TIMESTAMP数据转换转换为 DATE,会根据时间的小数值进行四舍五入,如时间 ‘1999-12-31 23:59:59.499’ 会转换为 ‘1999-12-31’, 然而时间 ‘1999-12-31 23:59:59.500’ 会转换为 ‘2000-01-01’.转换为 TIME,数据将丢弃日期部分,因为TIME不存储日期信息TIME数据转换,转换为其他时间时,将会使用 CURRENT_DATE() 获取当前的日期,如当前日期为: 2012-01-01,因为TIME的时间范围会大于等于24,所以日期部分会因为时间不同而有所变化,可以参考如下表格TIMEDATETIME/TIMESTAMPDATE12:00:002012-01-01 12:00:002012-01-0124:00:002012-01-02 00:00:002012-01-02-12:00:002011-12-31 12:00:002011-12-31

因为隐性转换数据的不确定性,建议可以使用显式的数据转换过程,使用诸如 CAST 函数进行数据转换,如

date_col = CAST(datetime_col AS DATE)

了解了以上有关MySQL的时间数据类型的特性,我们在业务中,可以根据需要选择合适的数据类型,可以更好的利用MySQL的服务器性能,提升业务处理能力.

参考

[Date and Time Data Types]: