MySQL · 参数选择 · datetime vs timestamp

发表于 | 更新于 | 分类于 | 阅读次数：

MySQL 中常用的两种时间储存类型分别是 datetime 和 timestamp，建表时该如何选择呢？

占用空间

类型 占据字节 表示形式
datetime 8 字节 yyyy-mm-dd hh:mm:ss
timestamp 4 字节 yyyy-mm-dd hh:mm:ss

表示范围

类型 表示范围
datetime ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’
timestamp ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’

时区

  • timestamp 占 4 个字节，而且是以 utc 格式储存，它会自动检索当前时区并进行转换，存和取可能不一样。

  • datetime 占 8 个字节储存，不会进行时区的检索，存和取一样。

  • 区别：如果存进去的是 NULL，timestamp 会自动储存当前时间，而 datetime 会储存 NULL。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 创建测试表
create table timedemo(st timestamp,dt datetime);

# 插入数据
insert into timedemo values (null,null), (now(),now()), ('20161001193450','20161001193450');

# timestamp 会自动储存当前时间，而 datetime 会储存 NULL
mysql> select * from timedemo;
+---------------------+---------------------+
| st                  | dt                  |
+---------------------+---------------------+
| 2019-09-24 14:00:15 | NULL                |
| 2019-09-24 14:00:15 | 2019-09-24 14:00:15 |
| 2016-10-01 19:34:50 | 2016-10-01 19:34:50 |
+---------------------+---------------------+

# 将时区修改为东9区，会发现 timestamp 比 datetime 多一小时
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "time_zone";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +09:00 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> select * from timedemo;
+---------------------+---------------------+
| st                  | dt                  |
+---------------------+---------------------+
| 2019-09-24 15:00:15 | NULL                |
| 2019-09-24 15:00:15 | 2019-09-24 14:00:15 |
| 2016-10-01 20:34:50 | 2016-10-01 19:34:50 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

选择

  • 如果在时间上要超过 Linux 时间的，或者服务器时区不一样的就建议选择 datetime。
  • 如果是想要使用自动插入时间或者自动更新时间功能，可以使用 timestamp。
  • 存储年使用 year，存储日期使用 date。