tulip notes
EffectTang
2024-10-21
目录

表设计-时间类型选择

# 表设计-时间类型选择

# 日期类型

MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常见使用的日期类型为DATETIME 和 TIMESTAMP。接下来，我就带你深入了解这两种类型，以及它们在设计中的应用实战。

下列是一个关于Datetime的简单例子：

register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
1
2

# DATETIME

类型 DATETIME 最终展现的形式为：YYYY-MM-DD HH：MM：SS，固定占用 8 个字节。

从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。同时，一些日期函数也支持精确到毫秒，例如常见的函数 NOW、SYSDATE：

mysql> SELECT NOW(6);

+----------------------------+

| NOW(6)                     |

+----------------------------+

| 2020-09-14 17:50:28.707971 |

+----------------------------+

1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

用户可以将 DATETIME 初始化值设置为当前时间，并设置自动更新当前时间的属性。例如之前已设计的用户表 User，我在其基础上，修改了register_date、last_modify_date的定义：

CREATE TABLE User (

    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money INT NOT NULL DEFAULT 0,

    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在上面的表 User 中，列 register_date 表示注册时间，DEFAULT CURRENT_TIMESTAMP 表示记录插入时，若没有指定时间，默认就是当前时间。

列 last_modify_date 表示当前记录最后的修改时间，DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都会修改为当前时间。

# TimeStamp

除了 DATETIME，日期类型中还有一种 TIMESTAMP 的时间戳类型，其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

同类型 DATETIME 一样，从 MySQL 5.6 版本开始，类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时，类型 TIMESTAMP 占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。

类型 TIMESTAMP 最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种选择。

另外，有些国家会执行夏令时。根据不同的季节，人为地调快或调慢 1 个小时，带有时区属性的 TIMESTAMP 类型本身就能解决这个问题。

参数 time_zone 指定了当前使用的时区，默认为 SYSTEM 使用操作系统时区，用户可以通过该参数指定所需要的时区。

如果想使用 TIMESTAMP 的时区功能，你可以通过下面的语句将之前的用户表 User 的注册时间字段类型从 DATETIME(6) 修改为 TIMESTAMP(6)：

ALTER TABLE User 

 CHANGE register_date 

 register_date TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP

(6);
1
2
3
4
5
6
7

# 不同时区

通过设定不同的 time_zone，可以观察到不同时区下的注册时间：

mysql> SET time_zone = '-08:00';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name,register_date FROM User WHERE name = 'David';

+-------+----------------------------+

| name  | register_date              |

+-------+----------------------------+

| David | 2018-09-14 02:28:33.898593 |

+-------+----------------------------+

1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

从上述例子中，你可以看到，中国的时区是 +08:00，美国的时区是 -08:00，因此改为美国时区后，可以看到用户注册时间比之前延迟了 16 个小时。当然了，直接加减时区并不直观，需要非常熟悉各国的时区表。在 MySQL 中可以直接设置时区的名字，如：

mysql> SET time_zone = 'America/Los_Angeles';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2020-09-14 20:12:49 |

+---------------------+

1 row in set (0.00 sec)

mysql> SET time_zone = 'Asia/Shanghai';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2020-09-15 11:12:55 |

+---------------------+

1 row in set (0.00 sec)
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

# 日期类型选择

# 建议选 Datetime

对日期字段的存储，开发人员通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。

当然，有些同学会认为 INT 比 TIMESTAMP 性能更好。但是，由于当前每个 CPU 每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，在后期运维和数据分析时，使用 INT 存储日期，是会让 DBA 和数据分析人员发疯的，INT的可运维性太差。

也有的同学会热衷用类型 TIMESTEMP 存储日期，因为类型 TIMESTAMP 占用 4 个字节，比 DATETIME 小一半的存储空间。

但若要将时间精确到毫秒，TIMESTAMP 要 7 个字节，和 DATETIME 8 字节差不太多。另一方面，现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近，这是需要开发同学好好思考的问题。

总的来说，我建议你使用类型 DATETIME。 对于时区问题，可以由前端或者服务这里做一次转化，不一定非要在数据库中解决。

# 不要忽视 TIMESTAMP 的性能问题

前面已经提及，TIMESTAMP 的上限值 2038 年很快就会到来，那时业务又将面临一次类似千年虫的问题。另外，TIMESTAMP 还存在潜在的性能问题。

虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。

  • 性能不如 DATETIME： DATETIME 不存在时区转化问题。
  • 性能抖动： 海量并发时，存在性能抖动问题。

为了优化 TIMESTAMP 的使用，强烈建议你使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：

[mysqld]

time_zone = "+08:00"
1
2
3

# 表结构设计规范：每条记录都要有一个时间字段

在做表结构设计规范时，强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段，并设置修改自动更新机制， 即便标识每条记录最后修改的时间。

例如，在前面的表 User 中的字段 last_modify_date，就是用于表示最后一次的修改时间：

CREATE TABLE User (

    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money INT NOT NULL DEFAULT 0,

    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DATETIME(6)：

  • DATETIME 类型用于存储日期和时间信息，格式为 YYYY-MM-DD HH:MM:SS。
  • (6) 表示该类型支持微秒级别的精度，最多可以存储 6 位小数的微秒部分。

比秒更小的时间单位有‌毫秒 (opens new window)、‌微秒 (opens new window)、‌纳秒 (opens new window)和‌皮秒 (opens new window)。‌

  1. ‌**毫秒（millisecond, ms）**‌：毫秒是一秒的千分之一，即1秒=1000毫秒。
  2. ‌**微秒（microsecond, μs）**‌：微秒是一秒的百万分之一，即1秒=1,000,000微秒。

# 数据对应

  • DATETIME 类型：通常对应 java.sql.Timestamp 或 java.time.LocalDateTime。
  • TIMESTAMP 类型：通常对应 java.sql.Timestamp。或 java.time.Instant：

时间戳：通常用于表示 Unix 时间戳（从 1970 年 1 月 1 日 00:00:00 UTC 起的秒数）。

上次更新: 2025/04/23, 16:23:16
表设计实战-自增、资金与字符
非结构存储：用好 JSON 这张牌

← 非结构存储：用好 JSON 这张牌→

最近更新
01
面向切面跟自定义注解的结合
05-22
02
时间跟其他数据的序列化
05-19
03
数据加密与安全
05-17
更多文章>
Theme by Vdoing | Copyright © 2023-2025 EffectTang