Rubin's Blog

  • 首页
  • 关于作者
  • 隐私政策
享受恬静与美好~~~
分享生活的点点滴滴~~~
  1. 首页
  2. MySQL
  3. 正文

MySQL学习之时间类型

2021年 5月 6日 922点热度 0人点赞 0条评论

前言

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。

用户可以将 DATETIME 初始化值设置为当前时间,并设置自动更新当前时间的属性(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)
);

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 是一种选择。

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

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

ALTER TABLE User CHANGE 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)

我们在工作中怎么样选择时间类型

首先说一下INT,有好多小伙伴愿意使用INT来标明时间,也就是存储当前时间和'1970-01-01 00:00:00'之间的毫秒数,原因就是INT的执行效率比较高。其实这种方式是不推荐的。原因就是这种方式和TIMESTAMP 在本质上是没有区别的,而且还会在后期运维上面造成比较大的成本开支。我们当前的CPU每秒可以支持上亿次的计算,所以我们完全没有必要为了一点点的运行效率而捡了芝麻丢了西瓜。

我们这里是建议使用DATETIME类型的。原因如下:

  • 若将时间精确到毫秒,TIMESTAMP 要 7 个字节,和 DATETIME 8 字节差不太多。另一方面,现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近。
  • TIMESTAMP存在性能问题

TIMESTAMP的性能问题

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

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

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

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

[mysqld]

time_zone = "+08:00"

设计表的建议

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

这样设计的好处是: 用户可以知道每个用户最近一次记录更新的时间,以便做后续的处理。比如在电商的订单表中,可以方便对支付超时的订单做处理;在金融业务中,可以根据用户资金最后的修改时间做相应的资金核对等。另外,MySQL 数据库的主从逻辑数据核对的设计实现,也会利用到last_modify_date 字段。

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL
最后更新:2022年 6月 9日

RubinChu

一个快乐的小逗比~~~

打赏 点赞
< 上一篇
下一篇 >

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复
文章目录
  • 前言
  • DATETIME
  • TIMESTAMP
  • 我们在工作中怎么样选择时间类型
    • TIMESTAMP的性能问题
  • 设计表的建议
最新 热点 随机
最新 热点 随机
问题记录之Chrome设置屏蔽Https禁止调用Http行为 问题记录之Mac设置软链接 问题记录之JDK8连接MySQL数据库失败 面试系列之自我介绍 面试总结 算法思维
RabbitMQ之高级特性解析 MyBatis之手写简易MyBatis Netty初探 加速本机对github的访问速度 java面试系列之网络编程 JVM垃圾回收机制及算法

COPYRIGHT © 2021 rubinchu.com. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

京ICP备19039146号-1