05｜第五话：基础篇-MySQL数据类型之日期时间类型

每一个常量、变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围。

这句话很容易理解，比如我们新建一个Excel表格，我们希望大家不要乱填，可能对某些列进行填写的限制。

比如《班级学生基本信息统计表》中学号和年龄一定是正整数，一般会限制其类型，不让大家乱填。

其实是一样的道理，任何的数据一定有其自己的类型，我们必须对其做好约束，比如这里的年龄字段，一定不会出现汉字或字母的，也不会是负数，一般就是Int这样的类型来表示；比如性别，总不能写一个日期进去吧，即便写进去了也没有任何意义；也有一些字段不好限制范围或类型，比如家庭住址，就没有太好的限制条件，可以随便填写，此时就可以用万能的字符串类型承担；

MySQL支持各种数据类型，我们主要关注的有三大类：

表示时间的日期时间类型
代表数字的数值类型
存放各种字符的字符串类型

每种类型下都有很多细分的类型，我们逐一来学习下，限于篇幅，本篇来详细聊聊日期时间类型。

本篇文章整体脉络如下：

一、日期时间类型概述

日期时间类型十分普遍，几乎每张表都会有时间类型的字段，比较典型的是记录某条记录的更新时间和创建时间，MySQL为我们提供了多种关于时间和日期的类型，主要有如下：

YEAR类型通常用来表示年
DATE类型通常用来表示年、月、日
TIME类型通常用来表示时、分、秒
DATETIME类型通常用来表示年、月、日、时、分、秒
TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒

MySQL5.6.4这个版本之后的各个类型需要的存储空间和取值范围如下：

类型	存储空间要求	取值范围	含义
YEAR	1字节	1901~2155	年份值
DATE	3字节	‘1000-01-01’ ~ ‘9999-12-31’	日期值
TIME	3字节+小数秒的存储空间	‘-838:59:59[.000000]’ ~ ‘838:59:59[.000000]’	时间值
DATETIME	5字节+小数秒的存储空间	‘1000-01-01 00:00:00[.000000]’ ～ ‘9999-12-31 23:59:59′[.999999]	日期加时间值
TIMESTAMP	4字节+小数秒的存储空间	‘1970-01-01 00:00:01[.000000]’ ～ ‘2038-01-19 03:14:07′[.999999]	时间戳

计算机中8个比特位代表一个字节，平时都是用若干个字节来表示一个数值。容易理解，使用的字节数越多，意味着能表示的数值范围就越大，但是也就越耗费存储空间。

二、YEAR类型

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间。

一般推荐以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。

我们新建一张表，关注里面的year_date字段：

CREATE TABLE `date_test` (
  `id` int(11),
  `year_date` year
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行完毕后，可以看到表的结构为：

mysql> desc date_test;               
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | YES  |     | NULL    |       |
| year_date | year(4) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

此时我们来插入数据并查询：

mysql> insert into date_test (id,year_date) values (1,2022);
Query OK, 1 row affected (0.00 sec)

mysql> insert into date_test (id,year_date) values (2,'2022');
Query OK, 1 row affected (0.00 sec)

mysql> select * from date_test;       
+------+-----------+
| id   | year_date |
+------+-----------+
|    1 |      2022 |
|    2 |      2022 |
+------+-----------+
2 rows in set (0.00 sec)

从上面的实验可以看到，加单引号和不加，效果都是一样的，建议加上单引号。

我们一开始提到，year的范围是最小值为1901，最大值为2155，如果超出这个范围则会报错：

mysql> insert into date_test (id,year_date) values (3,'2156');
ERROR 1264 (22003): Out of range value for column 'year_date' at row 1
mysql> insert into date_test (id,year_date) values (3,'1900');
ERROR 1264 (22003): Out of range value for column 'year_date' at row 1

三、DATE类型

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示月份，DD表示日期。需要3个字节的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期。

我们分别来看下，首先准备如下的表（为了演示方便，删除了上面已经创建的示例表，新建表来演示），重点关注my_date字段：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` date ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc date_test;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(11) | YES  |     | NULL    |       |
| my_date | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

按照一定的格式插入后可以看到几种格式写法都是可以的：

mysql> insert into date_test(id,my_date) values(1,'2022-08-25');
Query OK, 1 row affected (0.00 sec)

mysql> insert into date_test(id,my_date) values(2,'20220825');
Query OK, 1 row affected (0.00 sec)

mysql> select * from date_test;
+------+------------+
| id   | my_date    |
+------+------------+
|    1 | 2022-08-25 |
|    2 | 2022-08-25 |
+------+------------+
2 rows in set (0.00 sec)

mysql> insert into date_test(id,my_date) values(3,CURRENT_DATE());
Query OK, 1 row affected (0.01 sec)
mysql> insert into date_test(id,my_date) values(4,now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from date_test;
+------+------------+
| id   | my_date    |
+------+------------+
|    1 | 2022-08-25 |
|    2 | 2022-08-25 |
|    3 | 2022-08-27 |
|    4 | 2022-08-27 |
+------+------------+
4 rows in set (0.00 sec)

错误插入演示
mysql> insert into date_test(id,my_date) values(3,'202208');
ERROR 1292 (22007): Incorrect date value: '202208' for column 'my_date' at row 1

但是如果不是年月日的格式，则插入数据会提示报错。

四、TIME类型

DATE类型表示年月日，而TIME类型用来表示时间，不包含日期部分。在MySQL中，需要3字节+小数秒的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。

准备如下的表（为了演示方便，删除了上面已经创建的示例表，新建表来演示），重点关注my_date字段：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` time ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc date_test;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(11) | YES  |     | NULL    |       |
| my_date | time    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据时按照时分秒的格式即可，如需要插入10点10分09秒这个时间：

mysql> insert into date_test(id,my_date) values(1,'10:10:09');
Query OK, 1 row affected (0.00 sec)

mysql> select * from date_test;
+------+----------+
| id   | my_date  |
+------+----------+
|    1 | 10:10:09 |
+------+----------+
1 row in set (0.00 sec)

也有一些旁门左道的写法，这里不展开说明，并无太大意义，按照标准格式来写即可，这个原则也贯穿整个基础篇。

五、DATETIME类型

在格式上为DATE类型和TIME类型的组合，可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒，需要5字节+小数秒的存储空间。

以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

准备如下的表（为了演示方便，删除了上面已经创建的示例表，新建表来演示），重点关注my_date字段：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` datetime ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc date_test;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(11)  | YES  |     | NULL    |       |
| my_date | datetime | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据按照如上原则即可，我们尝试插入几条数据：

mysql> insert into date_test(id,my_date) values
(1,'2022-08-27 18:50:30'),
(2,'20220827185030');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into date_test(id,my_date) values
(3,CURRENT_TIMESTAMP()),
(4,now());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from date_test;
+------+---------------------+
| id   | my_date             |
+------+---------------------+
|    1 | 2022-08-27 18:50:30 |
|    2 | 2022-08-27 18:50:30 |
|    3 | 2022-08-27 18:09:14 |
|    4 | 2022-08-27 18:09:14 |
+------+---------------------+
4 rows in set (0.00 sec)

datetime类型是使用比较广泛的类型。

六、TIMESTAMP类型

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4字节+小数秒的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
针对时区问题，我们展开举例解释下：比方说我们把2018-01-24 11:39:21这个时刻存储到一个TIMESTAMP的列中，那么在中国你看到的时间就是2018-01-24 11:39:21，如果你去了日本，他们那使用的是东京时间，比北京时间早一个小时，所以他们那显示的就是2018-01-24 12:39:21。而如果你用DATETIME存储2018-01-24 11:39:21的话，那不同时区看到的时间值都是一样的。

其用法与datetime几乎一样，只是要注意范围不能超出，否则会提示报错。

准备如下的表（为了演示方便，删除了上面已经创建的示例表，新建表来演示），重点关注my_date字段：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` timestamp ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc date_test;
+---------+-----------+------+-----+-------------------+-----------------------------+
| Field   | Type      | Null | Key | Default           | Extra                       |
+---------+-----------+------+-----+-------------------+-----------------------------+
| id      | int(11)   | YES  |     | NULL              |                             |
| my_date | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

可以注意到timestamp类型默认创建后出现了CURRENT_TIMESTAMP和on update CURRENT_TIMESTAMP。

1、插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
2、更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间

也就是说，插入一条数据时即便不指定my_date字段的值，MySQL则会自动使用当前插入数据的时间作为timestamp的默认值。另外，当对这一行数据进行修改时，即便不指定更新时间，只要数据被成功更新，也会自动更新timestamp的值，举例如下：

mysql> insert into date_test(id) values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from date_test;
+------+---------------------+
| id   | my_date             |
+------+---------------------+
|    1 | 2022-08-27 18:20:27 |
|    2 | 2022-08-27 18:20:27 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> update date_test set id=3 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from date_test;
+------+---------------------+
| id   | my_date             |
+------+---------------------+
|    1 | 2022-08-27 18:20:27 |
|    3 | 2022-08-27 18:26:31 |
+------+---------------------+
2 rows in set (0.00 sec)

这个特性也可以用在datetime类型上，只需要我们在创建表结构的时候指定好即可：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc date_test;
+---------+----------+------+-----+-------------------+-----------------------------+
| Field   | Type     | Null | Key | Default           | Extra                       |
+---------+----------+------+-----+-------------------+-----------------------------+
| id      | int(11)  | YES  |     | NULL              |                             |
| my_date | datetime | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------+----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

此外，timestamp类型插入数据与datetime使用方法一致：

mysql> insert into date_test(id,my_date) values
(1,'2022-08-27 18:00:00'),
(2,'20220827180000'),
(3,now()),
(4,CURRENT_TIMESTAMP());
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from date_test;
+------+---------------------+
| id   | my_date             |
+------+---------------------+
|    1 | 2022-08-27 18:00:00 |
|    2 | 2022-08-27 18:00:00 |
|    3 | 2022-08-27 18:28:59 |
|    4 | 2022-08-27 18:28:59 |
+------+---------------------+
4 rows in set (0.00 sec)

七、DATETIME和TIMESTAMP如何选

读者朋友们一定对datetime和timestamp两个字段如何选择有一定的疑惑，我们来简单做个横向对比：

占用空间不同，TIMESTAMP（4字节+小数秒的存储空间）相对于DATETIME（5字节+小数秒的存储空间）存储空间比较小，但TIMESTAMP（只到2038年）表示的日期时间范围也比较小。
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 00:00:00毫秒的毫秒值。
时区机制不同：TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

项目中用得最多的日期时间类型就是DATETIME，因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。

如果必须考虑时区问题，比如你的业务涉及到海外，那么可能使用timestamp更加方便，其他的特性，基本上datetime都能干，请记住timestamp最大的风险是其取值最大是到2038年，这是一个很大的业务风险。有些书推荐使用timestamp，原因是它占用的存储比较少，但如一开始对比表所示，实际上我们可以看到，本版本mysql中其实差别不是很大（在老的版本中DATETIME固定占用8字节，而timestamp才4字节）。总结：项目中建议使用datetime类型。

八、小数秒问题

让我们回到文章一开始介绍的表格，MySQL5.6.4这个版本之后的各个类型需要的存储空间和取值范围如下：

类型	存储空间要求	取值范围	含义
YEAR	1字节	1901~2155	年份值
DATE	3字节	‘1000-01-01’ ~ ‘9999-12-31’	日期值
TIME	3字节+小数秒的存储空间	‘-838:59:59[.000000]’ ~ ‘838:59:59[.000000]’	时间值
DATETIME	5字节+小数秒的存储空间	‘1000-01-01 00:00:00[.000000]’ ～ ‘9999-12-31 23:59:59′[.999999]	日期加时间值
TIMESTAMP	4字节+小数秒的存储空间	‘1970-01-01 00:00:01[.000000]’ ～ ‘2038-01-19 03:14:07′[.999999]	时间戳

其中小数秒是什么意思呢？这里我们回过头来补充说明下。

在MySQL5.6.4这个版本之后，TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微秒的支持。由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，前三位标识毫秒，后三位标识微秒。

以datetime为例，一般情况下时间格式为YYYY-MM-DD HH:MM:SS，为了让我们的datetime类型支持小数秒，可以这么写：

dateime(小数秒位数)，其中小数秒位数可以可以在0、1、2、3、4、5、6中选择

比如DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到10微秒。如果你在选择TIME、DATETIME、TIMESTAMP这几种类型的时候添加了对小数秒的支持，那么所需的存储空间需要相应的扩大，保留不同的小数秒位数，那么增加的存储空间大小也不同，如下表：

保留的小数秒位数	额外需要的存储空间要
0	0字节
1或2	1字节
3或4	2字节
5或6	3字节

datetime类型下，如果不使用小数秒，则只占用5个字节长度，datetime相当于是datetime(0)，仅占用5字节空间，而datetime(1)或datetime(2)占用6字节、datetime(3)或datetime(4)占用7字节、datetime(5)或datetime(6)占用8字节，下面我们来结合实验看下效果。

以datetime为例，创建如下表结构：

mysql> CREATE TABLE `date_test` (   
`id` int(11),   
`my_date` datetime,
`my_date1` datetime(1),
`my_date2` datetime(2),
`my_date3` datetime(3),
`my_date4` datetime(4),
`my_date5` datetime(5),
`my_date6` datetime(6)) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> desc date_test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| my_date  | datetime    | YES  |     | NULL    |       |
| my_date1 | datetime(1) | YES  |     | NULL    |       |
| my_date2 | datetime(2) | YES  |     | NULL    |       |
| my_date3 | datetime(3) | YES  |     | NULL    |       |
| my_date4 | datetime(4) | YES  |     | NULL    |       |
| my_date5 | datetime(5) | YES  |     | NULL    |       |
| my_date6 | datetime(6) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

下面插入数据看下区别：

mysql> insert into date_test values(
1,
'2022-08-27 19:15:21',
'2022-08-27 19:15:21',
'2022-08-27 19:15:21',
'2022-08-27 19:15:21',
'2022-08-27 19:15:21',
'2022-08-27 19:15:21',
'2022-08-27 19:15:21');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date_testG
*************************** 1. row ***************************
      id: 1
my_date: 2022-08-27 19:15:21
my_date1: 2022-08-27 19:15:21.0
my_date2: 2022-08-27 19:15:21.00
my_date3: 2022-08-27 19:15:21.000
my_date4: 2022-08-27 19:15:21.0000
my_date5: 2022-08-27 19:15:21.00000
my_date6: 2022-08-27 19:15:21.000000
1 row in set (0.00 sec)

这里由于没有指定具体的毫秒和微秒是多少，因此默认存储为0，如果指定了具体的毫秒或微秒，自然就按照实际情况展示，如下示例：

mysql> insert into date_test values(
2,
'2022-08-27 19:15:21',
'2022-08-27 19:15:21.9',
'2022-08-27 19:15:21.99',
'2022-08-27 19:15:21.999',
'2022-08-27 19:15:21.9999',
'2022-08-27 19:15:21.99999',
'2022-08-27 19:15:21.999999');
Query OK, 1 row affected (0.01 sec)

mysql> select * from date_testG
*************************** 1. row ***************************
      id: 1
my_date: 2022-08-27 19:15:21
my_date1: 2022-08-27 19:15:21.0
my_date2: 2022-08-27 19:15:21.00
my_date3: 2022-08-27 19:15:21.000
my_date4: 2022-08-27 19:15:21.0000
my_date5: 2022-08-27 19:15:21.00000
my_date6: 2022-08-27 19:15:21.000000
*************************** 2. row ***************************
      id: 2
my_date: 2022-08-27 19:15:21
my_date1: 2022-08-27 19:15:21.9
my_date2: 2022-08-27 19:15:21.99
my_date3: 2022-08-27 19:15:21.999
my_date4: 2022-08-27 19:15:21.9999
my_date5: 2022-08-27 19:15:21.99999
my_date6: 2022-08-27 19:15:21.999999
2 rows in set (0.01 sec)