前言
假设现在有一个需求，需要设计一个在线机票订票系统的数据库，此时已经完成了大部分字段的设计，可能如下:
CREATE TABLE flights (
flight_id INT AUTO_INCREMENT PRIMARY KEY,
flight_number VARCHAR(10),
departure_airport_code VARCHAR(3),
arrival_airport_code VARCHAR(3),
price DECIMAL(10, 2)
);
这里还需要存储飞机航班的起飞时间和降落时间，此时需要新增departure_time 和 arrival_time 两个字段来存储。而时间的存储有多种选择:
- 字符串类型 (
VARCHAR或CHAR): 将时间存储为格式化的字符串，如 “2023-04-25 14:30:00”。 - 整数类型 (
INT或BIGINT): 使用 Unix 时间戳存储时间，它表示自 1970-01-01 00:00:00 UTC 至今的秒数。 - 原生日期时间类型 (
DATETIME或TIMESTAMP): 使用数据库提供的专门用于日期和时间的数据类型。
这里我们该如何选择，才能让数据库在实现需求的同时，也保证数据库的高性能呢? 下面我们先充分了解下所有可能的选择，在这个基础上再来对比比较，从而选出最为合适的类型。
时间类型介绍
DATE
在 MySQL 中，DATE 类型用于表示日期值，不包含时间部分。DATE 类型的值格式为 ‘YYYY-MM-DD’，其中：
- YYYY 表示年份，占 4 位数字。
- MM 表示月份，占 2 位数字，范围从 01 到 12。
- DD 表示日，占 2 位数字，范围从 01 到月份对应的最大天数（例如，1月为 31，2月通常为 28 或 29，依此类推）。
DATE 类型的值范围是从 1000-01-01 到 9999-12-31，所以它可以覆盖公历中的几乎所有日期。
在实际使用时，可以用 DATE 类型来存储任何需要日期但不需要时间信息的数据，例如生日、节日、事件发生的日期等。
以下是 DATE 类型的示例，创建一个包含 DATE 类型列的表：
CREATE TABLE events (
id INT,
event_name VARCHAR(100),
event_date DATE
);
向表中插入一个日期值：
INSERT INTO events (id, event_name, event_date) VALUES (1, 'New Year', '2024-01-01');
查询 DATE 类型的列，数据正常返回，如下:
mysql> SELECT * FROM events WHERE event_date >= '2024-01-01';
+------+------------+------------+
| id | event_name | event_date |
+------+------------+------------+
| 1 | New Year | 2023-01-01 |
+------+------------+------------+
此外，DATE 类型会对传入的数据进行检查，如果存储的日期不在1000-01-01 到 9999-12-31之间，此时将会报错，不允许其写入，能过预防异常数据的插入:
mysql> INSERT INTO events (id, event_name, event_date) VALUES (1, 'New Year', '12023-01-01');
ERROR 1292 (22007): Incorrect date value: '12023-01-01' for column 'event_date' at row 1
在存储方面，DATE 类型的每个值在 MySQL 中占用 4 个字节。这种存储方式比存储相同日期的字符串形式更为高效，同时也便于快速的日期计算和比较。
DATETIME
对于 DATE 类型，只存储日期信息，但是如果需要更高精度的时间，DATE 类型就不能满足需求，此时可以考虑使用DATETIME 类型来存储数据。
在 MySQL 中，DATETIME 类型用于存储日期和时间信息。它可以记录年、月、日、时、分、秒，格式为 YYYY-MM-DD HH:MM:SS，其中：
- YYYY 表示四位数字的年份。
- MM 表示两位数字的月份，范围是 01 到 12。
- DD 表示两位数字的日，范围是 01 到月份对应的最大天数。
- HH 表示两位数字的小时，以 24 小时制表示，范围是 00 到 23。
- MM 表示两位数字的分钟，范围是 00 到 59。
- SS 表示两位数字的秒，范围是 00 到 59。
DATETIME 类型的值范围是从 1000-01-01 00:00:00 到 9999-12-31 23:59:59，可以精确到秒。下面举一个例子说明一下，创建一个包含 DATETIME 类型列的表：
CREATE TABLE appointments (
id INT,
description VARCHAR(100),
start_time DATETIME,
end_time DATETIME
);
向表中插入一个日期和时间值：
INSERT INTO appointments (id, description, start_time, end_time) VALUES (1, 'Meeting', '2023-04-12 14:00:00', '2023-04-12 15:00:00');
查询 DATETIME 类型的列，此时数据正常返回，如下：
mysql> SELECT * FROM appointments WHERE start_time BETWEEN '2023-04-01 00:00:00' AND '2023-04-30 23:59:59';
+------+-------------+---------------------+---------------------+
| id | description | start_time | end_time |
+------+-------------+---------------------+---------------------+
| 1 | Meeting | 2023-04-12 14:00:00 | 2023-04-12 15:00:00 |
+------+-------------+---------------------+---------------------+
DATETIME 类型同样会对传入的数据进行校验，如果数据不是合法的时间，亦或者不在1000-01-01 00:00:00 到 9999-12-31 23:59:59 范围内，此时也会报错，示例如下:
mysql> INSERT INTO appointments (id, description, start_time, end_time) VALUES (2, 'Meeting', '2023-04-12 14:00:00', '2023-04-12 25:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2023-04-12 25:00:00' for column 'end_time' at row 1
在存储方面，DATETIME 类型的每个值在 MySQL 中占用 8 个字节。这比存储相同的日期和时间信息的字符串更为高效，并允许进行快速的日期和时间计算及比较。
TIMESTAMP
在某些情况下，我们想要在记录中增加一个记录更新时间，当记录更新时，这个列的值能够自动更新为当前的日期和时间，DATE 和 DATETIME并没有这种功能，此时就可以使用TIMESTAMP类型。
在 MySQL 中，TIMESTAMP 类型用于存储日期和时间信息，TIMESTAMP 类型的格式与 DATETIME 类型相同，为 YYYY-MM-DD HH:MM:SS，与DATETIME 类型类似：
- YYYY 表示四位数字的年份。
- MM 表示两位数字的月份，范围是 01 到 12。
- DD 表示两位数字的日，范围是 01 到月份对应的最大天数。
- HH 表示两位数字的小时，以 24 小时制表示，范围是 00 到 23。
- MM 表示两位数字的分钟，范围是 00 到 59。
- SS 表示两位数字的秒，范围是 00 到 59。
TIMESTAMP 类型的值范围是从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。TIMESTAMP 类型的值实际上是存储为自 1970-01-01 00:00:00 UTC 以来的秒数（也称为 Unix 时间戳），并且在检索或显示时根据当前机器的时区设置转换为本地时间。
TIMESTAMP 类型的特殊属性是其能够根据表结构的定义，自动设置和更新时间戳，也就是上面我们提到自动更新功能，这里我们创建一个表来验证一下:
CREATE TABLE user_actions (
id INT,
action VARCHAR(100),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
在user_actions 表中的 action_time 字段中，我们设置默认值为 CURRENT_TIMESTAMP，同时更新数据时，会自动更新action_time 字段的值为 CURRENT_TIMESTAMP, 下面我们通过一个 INSERT 和 UPDATE 语句来证明一下。
当插入新记录时，MySQL 会自动将当前的日期和时间作为值插入，如下:
mysql> INSERT INTO user_actions (id, action) VALUES (1, 'Login');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user_actions;
+------+--------+---------------------+
| id | action | action_time |
+------+--------+---------------------+
| 1 | Login | 2024-03-04 20:50:56 |
+------+--------+---------------------+
1 row in set (0.02 sec)
可以看到，在执行 INSERT 语句时，我们并没有指定 action_time 的值，但是查询出来的数据与与我插入数据的时间是一致的，说明MySQL确实是会自动将当前的日期和时间作为值插入。
其次，如果 TIMESTAMP 列在表定义中有自动更新的属性，当记录更新时，这个列的值会自动更新为当前的日期和时间，下面我们通过更新上面插入的记录来进行说明:
mysql> update user_actions set action = 'logout' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user_actions;
+------+--------+---------------------+
| id | action | action_time |
+------+--------+---------------------+
| 1 | logout | 2024-03-04 20:54:27 |
+------+--------+---------------------+
1 row in set (0.02 sec)
可以看到，我们执行update 语句时并没有指定action_time的值，在执行完update 语句后，这个列的值会自动更新为当前的日期和时间。
除此之外，TIMESTAMP 还有一个特点是其返回的时间是带有时区特性的，这里也举个例子来说明一下:
首先执行下面命令，将时区设置为+08:00 时区:
mysql> SET time_zone = '+08:00';
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+
1 row in set (0.02 sec)
此时往前面的user_actions 插入数据，不指定 action_time 的值，默认使用当前时间:
mysql> INSERT INTO user_actions (id, action) VALUES (1, 'test');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_actions;
+------+--------+---------------------+
| id | action | action_time |
+------+--------+---------------------+
| 1 | test | 2024-03-05 08:11:28 |
+------+--------+---------------------+
1 row in set (0.02 sec)
此时 mysql 存储action_time 其实是带有时区属性的，为+08:00 时区的2024-03-05 08:11:28，下面我们修改服务器时区为-04:00，然后看查询user_actions 表会返回什么数据:
mysql> SET time_zone = '-04:00';
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | -04:00 |
+---------------+--------+
1 row in set (0.02 sec)
mysql> select * from user_actions;
+------+--------+---------------------+
| id | action | action_time |
+------+--------+---------------------+
| 1 | Login | 2024-03-04 20:11:28 |
+------+--------+---------------------+
1 row in set (0.01 sec)
可以看到，返回的action_time的值已经从2024-03-05 08:11:28 变成了 2024-03-04 20:11:28, 往前提前了12个小时，与时区的变化完全一致，从而能够证明 TIMESTAMP 返回的时间其实是带有时区属性的。
其实 TIMESTAMP 其实存储的是自 1970-01-01 00:00:00 UTC 以来的秒数(也称为 Unix 时间戳)， 返回数据时，MySQL 服务器会根据其配置的时区设置将这个 Unix 时间戳转换为相应的时间，然后返回。
这里需要注意的一个点，因为 TIMESTAMP 存储的是32位的时间戳，所以到2038-01-19 03:14:08 UTC 之后，传统的 32 位 Unix 时间戳将溢出，导致时间无法正确表示，所以如果使用 TIMESTAMP 来存储时间需要注意到这个问题。
非原生类型存储日期
字符串类型
除了原生的日期时间类型外，有时候有人可能也会使用字符串类型（如 VARCHAR 或 CHAR）来存储日期和时间信息，比如下面的定义:
CREATE TABLE events (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
event_datetime VARCHAR(19) -- 用于存储日期时间的字符串
);
然后执行下面的插入语句，也可以实现日期时间的存储:
INSERT INTO events (name, event_datetime) VALUES ('test', '2024-03-04 12:00:00');
但是通常不会使用字符串类型来存储日期和时间，原生的时间类型是为了处理日期和时间而优化的，它们在存储效率、性能和功能上通常都要优于字符串类型。
下面可以通过定义两张表，往其中插入一批数据，比较二者的性能性能差距，表的定义如下所示:
-- 使用 TIMESTAMP 类型的表
CREATE TABLE events_timestamp (
id INT AUTO_INCREMENT PRIMARY KEY,
event_datetime TIMESTAMP
);
-- 使用 VARCHAR 类型的表
CREATE TABLE events_varchar (
id INT AUTO_INCREMENT PRIMARY KEY,
event_datetime VARCHAR(19)
);
使用存储过程分别往这两张表插入100w条数据。首先往events_timestamp插入100w条数据:
DELIMITER //
CREATE PROCEDURE InsertEventsTimestamp()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO
INSERT INTO events_timestamp (event_datetime) VALUES (NOW());
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
再往events_varchar插入100w条数据:
DELIMITER //
CREATE PROCEDURE InsertEventsVarchar()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE current_datetime VARCHAR(19);
WHILE i < 1000000 DO
-- 生成当前的日期时间字符串
SET current_datetime = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
INSERT INTO events_varchar (event_datetime) VALUES (current_datetime);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
此时 events_timestamp 和 events_varchar 表中都有100w条数据。
我们下面通过执行查询语句来比较 TIMESTAMP/DATETIME 类型和 CHAR/VARCHAR 类型在存储效率、性能和功能上的差异。
首先通过这个SQL查看 events_timestamp 和 events_varchar 两张表占用的磁盘的大小:
SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = 'test' AND table_name in ('events_varchar', 'events_timestamp');
+------------------+------------+
| Table | Size in MB |
+------------------+------------+
| events_timestamp | 49.09 |
| events_varchar | 86.14 |
+------------------+------------+
2 rows in set (0.01 sec)
可以看到，events_varchar 占用的磁盘空间比events_timestamp 多了一倍。磁盘空间的变大，查询数据时也会增加磁盘 I/O 的操作，占用更多的内存，降低mysql的查询性能。
接着我们来比较二者在查询效率上的差距，为了效果更为明显，这里不加索引从而让查询进行全表扫描，看看两边性能上的差距，这里我会开启profile，方便查看各阶段的耗时:
SET profiling = 1;
首先是对 events_timestamp 进行查询，具体SQL如下，只查询一行数据，同时让其进行排序:
mysql> select * from events_timestamp order by event_datetime desc limit 100000,1;
+--------+---------------------+
| id | event_datetime |
+--------+---------------------+
| 900154 | 2024-03-05 21:07:18 |
+--------+---------------------+
1 row in set (0.36 sec)
查询该SQL各阶段的执行耗时，看看events_timestamp的效果怎么样:
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000074 |
| checking permissions | 0.000011 |
| checking permissions | 0.000004 |
| Opening tables | 0.000018 |
| init | 0.000025 |
| System lock | 0.000006 |
| optimizing | 0.000005 |
| statistics | 0.000025 |
| preparing | 0.000033 |
| Sorting result | 0.000007 |
| executing | 0.000003 |
| Sending data | 0.000007 |
| Creating sort index | 0.277743 |
| end | 0.000023 |
| query end | 0.000008 |
| closing tables | 0.000008 |
| freeing items | 0.000042 |
| cleaning up | 0.000012 |
+----------------------+----------+
18 rows in set, 1 warning (0.01 sec)
这里重点关注下 Creating sort index，这个阶段是MySQL 正在创建一个内部的排序索引来临时存储排序操作的结果，使用 TIMESTAMP 在无索引情况下的耗时为0.277s。
下面对 events_varchar 进行查询，全部条件保持一致，避免其他因素的干扰:
mysql> select * from events_varchar order by event_datetime desc limit 100000,1;
+--------+---------------------+
| id | event_datetime |
+--------+---------------------+
| 899993 | 2024-03-05 22:21:10 |
+--------+---------------------+
1 row in set (0.54 sec)
查询该SQL各阶段的执行耗时，看看varchar 存储时间的效果:
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000070 |
| checking permissions | 0.000010 |
| checking permissions | 0.000004 |
| Opening tables | 0.000016 |
| init | 0.000025 |
| System lock | 0.000007 |
| optimizing | 0.000005 |
| statistics | 0.000023 |
| preparing | 0.000017 |
| Sorting result | 0.000005 |
| executing | 0.000002 |
| Sending data | 0.000006 |
| Creating sort index | 0.526720 |
| end | 0.000023 |
| query end | 0.000008 |
| closing tables | 0.000008 |
| freeing items | 0.000041 |
| cleaning up | 0.000011 |
+----------------------+----------+
可以看到，MySQL 在Creating sort index 这个阶段的耗时为0.52s，相比于TIMESTAMP 类型的耗时多了差不多一倍，说明在使用VARCHAR 存储时间下，比较排序是更加耗费资源的。
由此可以证明 VARCHAR 不管是在存储时间方面，还是在查询性能方面，都比原生时间类型 TIMESTAMP 表现更差一点。 在设计数据库和应用时，应优先考虑使用 DATETIME、TIMESTAMP 或其他适合的时间类型。而不是使用 VARCHAR 来存储时间。
整形
在实际开发过程中，我们也会使用整数类型（通常是32位或64位的整数）来存储时间戳。下面我们比较整数类型和原生时间类型，看看这二者在存储效率，查询性能，功能支持的区别，从而能够做出更好的选择。
在存储效率方面，二者其实没有太大的区别。 整数类型（如 INT 和 BIGINT）通常占用固定的存储空间（INT 为 4 字节，BIGINT 为 8 字节）。而对于原生时间类型（如 DATETIME 和 TIMESTAMP）,在 MySQL 中，DATETIME 占用 8 字节，TIMESTAMP 占用 4 字节。
对于查询性能，整数类型的比较通常很快，因为它们是CPU原生支持的操作。如果查询不涉及到时间日期的特定函数，使用整数可能会提供更好的性能。但是二者的性能差距可以忽略不计，具体验证可以参考上面字符串类型性能比较的过程，这里就不再重复了。
对于功能支持方面，原生时间类型优化了日期和时间的相关计算。在使用日期时间函数进行复杂查询时，如日期间隔计算、日期格式化输出等，原生时间类型通常会提供更好的性能和方便的函数支持。
比如下面这个例子，我们创建一个events表:
CREATE TABLE events (
event_id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255),
start_time DATETIME,
end_time DATETIME,
location VARCHAR(255)
);
events 表中包含两个 DATETIME 类型的列 start_time 和 end_time，我们想要计算每个事件的持续时间。此时因为是使用原生时间类型，我们可以直接使用 TIMEDIFF 函数来获取两个时间点之间的差值：
SELECT
event_id,
start_time,
end_time,
TIMEDIFF(end_time, start_time) AS duration
FROM
events;
相比之下，如果我们使用整数类型存储时间戳，同样的操作通常需要更复杂的转换和计算，这不仅增加了代码的复杂性，而且可能影响性能。
综上所述，整形类型存储时间戳相比于原生时间类型，在存储效率，查询性能并没有突出的优势，相反还少了一些日期时间函数的操作，以及整形时间戳的可读性也比较差，所以还是建议使用原生时间类型而不是用整形来存储。
怎么选择
在MySQL中存储日期和时间，虽然有多种选择，如DATE,DATETIME,TIMESTAMP,但是对于某一个业务场景来说，往往只有某一种类型在满足时间精度要求的前提下，在存储效率，查询性能上表现得更为优秀。
下面我们来展示下几种原生时间类型在存储效率，查询性能，时间精度表现上的差异:
| 类型/比较维度 | DATE | DATETIME | TIMESTAMP |
|---|---|---|---|
| 时间精度 | 包含日期 | 包含日期和时间 | 包含日期和时间，以及时区信息 |
| 存储效率 | 4字节 | 8字节 | 4字节 |
| 时间范围 | 1000-01-01 到 9999-12-31 | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC |
| 查询性能 | 基于相同 | 基于相同 | 基于相同 |
可以看到，如果只需要存储日期，DATE 是最好的选择，其存储效率最高；如果需要日期和时间，则 DATETIME 和 TIMESTAMP 是常用选项，这两者相比TIMESTAMP 是存储效率更高的选择，但是其能表示的时间范围有限。而DATETIME 则没有这个限制。
下面我们再来综合比较下，看看两种非原生类型(字符串和整数)，相比于原生数据类型在存储，执行效率下的表现:
| 类型/比较维度 | 字符串 | 整形 |
|---|---|---|
| 存储效率 | 较差，占用更多的存储空间 | 与原生时间类型基本相同 |
| 查询性能 | 比较和排序效率较差 | 比较和排序效率高 |
| 功能支持 | 无，日期计算等均需自己实现 | 无，日期计算等均需自己实现 |
| 时间范围 | 无限制 | 64位整形无限制 |
从上可以看出，在大部分情况下应该避免使用字符串来保存时间，而是应该基于业务需求，使用最为合适的原生时间类型。
如果不需要MySQL提供的日期和时间函数的话，使用整形来存储时间也是可以的。
回到上面数据库设计的问题上，我会这么考虑时间类型的定义:
- 字符串类型: 从上面对比的结果可以看到，由于其存储效率，性能等问题，基本上不会选择，这里不做考虑
- 整形: 在该场景下，有时候需要使用时间函数来计算航班总时程，需要自己实现，也不做考虑
- DATE: 不满足时间精度的要求，不考虑
- TIMESTAMP: 时间范围有限，不过存储效率高，只需要4个字节
- DATETIME: 不会受到时间范围的限制，不过存储效率相对低一点，需要8个字节
综合考虑之下，我还是选择 DATETIME 类型，从而支持更大的时间范围，最终表结构定义如下:
CREATE TABLE flights (
flight_id INT AUTO_INCREMENT PRIMARY KEY,
flight_number VARCHAR(10),
departure_airport_code VARCHAR(3),
arrival_airport_code VARCHAR(3),
departure_time DATETIME not null,
arrival_time DATETIME not null,
price DECIMAL(10, 2)
);
总结
在时间类型存储上，MySQL提供了多种时间类型，如 DATE, DATETIME, TIMESTAMP，满足各种时间精度的需求；在某些场景下，也可以使用字符串或者整形来存储时间。
本文详细介绍了各种时间类型，同时在时间精度，存储范围，执行效率等维度上对其进行了比较，展示了其长处和相对应的缺点。
从而能够在数据库设计时，在时间字段的类型作出更准确，更高效的选择。
共同学习，写下你的评论
评论加载中...
作者其他优质文章
