MySQL 时间类型用 datetime, timestamp 或者 integer 更好

问题

今天我们来探讨一个成心思的问题，先说场景：

• 这是一个做在线文档产品的业务，需要给用户展现文档的编辑记录，现在我们叫它【智能文档】。
• 智能文档会不定期给文档数据打一个快照，保存起来。用户可以在历史记录中查阅快照。
• 快照之间会展现具体的变更记录，比如“用户A 复制了一段文字”，“用户B删除一个图片”。
• 快照本身是动态生成和回收的，即距离现在越远的快照，留下来的越少（更稀疏的快照意味着相邻快照之间的变更记录会更多，本来是一天一个快照，展现这一天内的变更记录便可，后来变成了一周一个快照，因而需要展现这一周内的变更记录）

那怎么实现查找两个快照之间的【变更记录】有哪几种呢？

快照 和 变更记录 预期是两张表。首先我们不能将【变更记录】通过 id 挂在某个【快照】上，由于我们的快照是不断被回收的，这样的话当你回收快照时，也需要连带着更新大量的【变更记录】，出现写分散。

另外一个想法是，能否通过时间戳进行比较？比如快照 A 的创建时间戳是 12345，快照 B 的创建时间戳是 23456。那末我只要【变更记录】这张表也有一个时间戳字段，写一个 SQL 查到两个快照时间戳之间的变更记录是不是是就能够了？

写出来 SQL 类似这样：

那末，问题来了，这个 create_time，虽然这里我们直接拿时间戳比较，但真的是性能最好的么？建表的时候，我应当用 datetime, timestamp 或者 int ?

今天我们就来看看到底有甚么区分。

MySQL 支持的数据类型

任何一篇博客，教程都比不上官方文档，大家选型有疑虑时或者建议先来看看 MySQL Data Types 。

Integer

我们先来看 integer 有甚么类型。

SQL 标准中对整数，提出了两种类型：INTEGER(INT) 和 SMALLINT。在此以外，MySQL 还额外提供了 TINYINT, MEDIUMINT, BIGINT 三种类型。

所以一共是五种:

可以看到，INT 其实和我们通经常使用的 int32 是一样的，本质是 2 的 31 次方 – 1，大概21亿4千7百万。（正整数以二进制存储。负整数以补码存储。一个Int类型数据占据空间4字节。每一个字节8位，共32位。因此最大存储2的31次方（从2的0次方开始）。但32位的第一名是符号位。所以2的31次方减1.简单说Int类型占据4字节，所以是这个取值范围。）

这里 BIGINT 就等价于 int64。

Datetime

datetime 实际上是一个统称，MySQL 提供了 DATE, DATETIME, TIMESTAMP 三种类型。

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999⑴2⑶1'.

DATE 类型没有具体的时间点，只能精确到【日期】，即 YYYY-MM-DD，比如 1994-06-09。

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999⑴2⑶1 23:59:59'.

DATETIME 则同时支持【日期】和【时间】，格式为 YYYY-MM-DD hh:mm:ss。如 1995-04⑵9 17:11:12。

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01⑴9 03:14:07' UTC.

TIMESTAMP 一样也支持【日期】和【时间】，但由于带上了时间戳的语义，就不如 DATETIME 支持的范围那末宽了。UTC 时间，从'1970-01-01 00:00:01' UTC 到 '2038-01⑴9 03:14:07'

由于此前的系统设计都是基于 32 位实现的，我们上面提到过，最多不过是 2 的 31次方 – 1，每一个数代表一秒的话，最多表示 68 年。所以 Unix 选取了 1970年1月1日作为UNIX TIME的纪元时间(开始时间)。

这里我们主要或者关心 DATETIME 和 TIMESTAMP，两者除整秒以外，还可以支持小数点后的部份，最多到 microseconds （6位）精度。格式为 'YYYY-MM-DD hh:mm:ss[.fraction]'，比如 '2038-01⑴9 03:14:07.999999' （事实上这也是 TIMESTAMP 能支持的最大值）。

除此以外，两者也都支持 自动初始化（Automatic Initialization)。这里要用到的两个大杀器：

• DEFAULT CURRENT_TIMESTAMP
• ON UPDATE CURRENT_TIMESTAMP

两者可以同时出现，也能够单独出现，分几种情况：

• 同时出现

此时 ts 和 dt 的默许值就是当前时间，当这一行其他值产生变化时，也会自动把这两个属性更新为当前时间。

• 只有 DEFAULT

此时只有初始化的时候才会写入当前时间，随后更新时不会变动。（固然，我们也能够把 CURRENT_TIMESTAMP 换成一个常数，比如 0，语法上是支持的，只不过那样就不是当前时间了）

• 只有 ON UPDATE

此时没有指定默许值，但产生更新时会改成当前时间，这时候的默许值就是 type dependent，依赖类型了。 TIMESTAMP 的默许值为 0，如果定义了 NULL 则默许值为 NULL。

这次我们换成了 DATETIME，两者正好相反，不指定 DEFAULT 的话，默许值为 NULL，但如果我们声明了 NOT NULL，则默许值变成 0。

• 我们可使用 show variables like '%explicit_defaults_for_timestamp%'; 来查看会不会禁用了自动初始化和更新。

• 虽然在MySQL中可以对时间戳字段赋值或更新，但建议仅在必要的情况下对时间戳列进行显式插入和更新。

TIMESTAMP

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

TIMESTAMP 底层采取 4 个字节存储（2的31次方⑴，还记得么），能支持的时间范围比 DATETIME 要小一倍，但它的特点在于，当我们写入时，MySQL会根据当前 server 所在的时区进行转换，将值变成 UTC 时区的时间，再存储。一样的，在查询的时候，MySQL 也会帮助我们转成当前时区再展现。这是 DATETIME 不具有的。

这样的跨时区支持，在一些业务场景下是很有用的。毕竟存储时间这件事情本身是很敏感的。国外用户一开始要求到了新加坡机房，落了一个时间。随后跑到欧洲顽耍，在法国重新访问，发现跟本地时间完全对不上，这就有问题了。

所以 TIMESTAMP 的思路就是，大家都以 UTC 时间为准，这是个基线，不管你是哪一个时区的，我都要转成统一的时间，查询的时候给你转回去就是了。

我们可以用 show variables like '%time_zone%'; 来查看当前库的时区：

需要注意，当MySQL参数time_zone=system时，查询timestamp字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会致使线程上下文频繁切换，CPU使用率暴涨，系统响应变慢设置假死。

The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

使用 SET TIME_ZONE = 'america/new_york"; 来设置时区。每一个连接可使用区别的时区

可以实验一下，在一个时区写入 TIMESTAMP 数据，切换时区后读出来，显示的时间是不一样的，而 DATETIME 则是完全一致的。demo

DATETIME

DATETIME 底层采取 8 个字节存储，没有跨时区的支持，结果直接展现。你存进去的是甚么时间，读到的就是甚么时间。不过我们如果需要跨时区，也不是没有办法，可以在读出来 DATETIME 后转为时间戳，从业务代码层面来处理，想转成甚么时区都 OK。

这里不用担心 2038 年的限制，虽然空间大了一倍，但通常情况下不会造成多大性能影响。

Integer

这里在讨论完 DATETIME, TIMESTAMP 以后，我们回过头来看看 Integer。

为何我们能用一个整数来代表时间呢？这里本质是我们给它赋予了【时间戳】的语义。

虽然整数的上下限更大（比如我们用 BIGINT，可以支持 2 的 63 次方 – 1 的数据），但是，但是，用法是关键。

如果你打算还用时间戳函数进行生成和转换，那就需要关注 2038 年这个限制，本质上和 TIMESTAMP 是没有区分的。

所以，通常我们认为，用整型时间戳的情势，取值范围也是 1970 年 1 月 1日起，到 2038 年截止，这个区间。用 BIGINT 的意义不大，只要它的语义或者时间戳，就需要遵守这个规范。

BETWEEN 查询

回到我们一开始提到的案例，我们需要挑选出两个时间点之间，有哪几种【变更记录】。

如果是整型，我们其实常常使用 BETWEEN 来进行查询：

它和下面直接用运算符的情势是等价的，注意 BETWEEN 是个闭区间：

一样的，查询 datetime 仍然可以用 BETWEEN:

下面两个查询也是等价的：

固然，我们也能够用 now() 等函数作为辅助，注意 between 里面一定要先写小的时间，and 后面写更大的时间点。

性能差异

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999⑴2⑶1 23:59:59'.

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

其实 DATETIME 和 TIMESTAMP 底层也是整型存储（否则就不会依照 2 的31 次方，63 次方来支持了），算是一层封装，提供了一系列时间函数使用。

DATETIME 底层存储实现是 BigInt，索引存储上和 BigInt 的处理是几近如出一辙的，所以 BigInt 支持的索引查询，datetime也支持。

加上索引后的速度如何，推荐大家浏览这一篇 benchmark MYSQL 数据库时间字段 INT,TIMESTAMP,DATETIME 性能效力的比较介绍

这里援用一下结论：

• 对 MyISAM 引擎，不建立索引的情况下（推荐），效力从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较）> timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime) 。
• 对 MyISAM 引擎，建立索引的情况下，效力从高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIXTIMESTAMP(datetime) 。
• 对 InnoDB 引擎，没有索引的情况下(不建议)，效力从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime)。
• 对 InnoDB 引擎，建立索引的情况下，效力从高到低：int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。
• 一句话，对 MyISAM 引擎，采取 UNIX_TIMESTAMP(timestamp) 比较；对InnoDB 引擎，建立索引，采取 int 或 datetime直接时间比较。

大家可以尝试一下，结合你的业务场景，跑一下 explain 看看。

到此这篇关于MySQL 时间类型用 datetime, timestamp 或者 integer 更好的文章就介绍到这了,更多相关MySQL 时间类型使用内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！