您现在的位置: 首页 > 网站导航收录 > 百科知识百科知识

毫秒换算(警告！别再使用 TIMESTAMP 作为日期字段)

时区,类型,时间毫秒换算(警告！别再使用 TIMESTAMP 作为日期字段)

发布时间：2020-12-06加入收藏来源：互联网点击：

原文链接：https://mp.weixin.qq.com/s/TNLJyhxibAB1bC3nHCELXQ

来源：JAVA日知录

在日常数据库设计中，几乎每张业务表都带有一个日期列，用于记录每条记录产生和变更的时间。比如用户表会有一个日期列表记录用户注册的时间、用户最后登录的时间。又比如，电商行业中的订单表（核心业务表）会有一个订单产生的时间列，当支付时间超过订单产生的时间，这个订单可能会被系统自动取消。

日期类型虽然常见，但在表结构设计中也容易犯错，比如很多开发同学都倾向使用整型存储日期类型，同时也会忽略不同日期类型对于性能可能存在的潜在影响。所以你有必要认真看看这篇文章，举一反三，在自己的业务中做好日期类型的设计。

日期类型

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

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)

用户可以将 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));

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

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

这样的设计保证了用户的金钱（money 字段）发生了变更，则是 last_modify_date 能记录最后一次用户金钱发生变更时的时间。来看下面的例子：

mysql> SELECT name,money,last_modify_date FROM User WHERE name = 'David'; ------- ------- ---------------------------- | name | money | last_modify_date | ------- ------- ---------------------------- | David | 100 | 2020-09-13 08:08:33.898593 | ------- ------- ---------------------------- 1 row in set (0.00 sec)mysql> UPDATE User SET money = money - 1 WHERE name = 'David';Query OK, 1 row affected (0.06 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT name,money,last_modify_date FROM User WHERE name = 'David'; ------- ------- ---------------------------- | name | money | last_modify_date | ------- ------- ---------------------------- | David | 99 | 2020-09-14 18:29:17.056327 | ------- ------- ---------------------------- 1 row in set (0.00 sec)

可以看到，当用户金额发生修改时，所对应的字段 last_modify_date 也修改成发生变更的时间。

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);

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

mysql> SELECT name,regist er_date FROM User WHERE name = 'David'; ------- ---------------------------- | name | register_date | ------- ---------------------------- | David | 2018-09-14 18:28:33.898593 | ------- ---------------------------- 1 row in set (0.00 sec)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)

从上述例子中，你可以看到，中国的时区是 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)

讲到这儿，想必你已经了解了时间字段类型，接下来我将分享在真实业务设计中如何使用好时间类型。

业务表结构设计实战DATETIME vs TIMESTAMP vs INT，怎么选？

在做表结构设计时，对日期字段的存储，开发人员通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

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

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

 1/2    1 2 下一页 尾页