干干的笔记

# 1. 数据类型简介

MySQL中关于日期和时间有DATE、TIME、DATETIME、TIMESTAMP这几种数据类型：

  • DATE：储存日期，格式为YYYY-MM-DD，范围1000-01-01到9999-12-31；
  • TIME：储存时间，格式为hhh:mm:ss，范围-838:59:59 到838:59:59；
  • DATETIME：储存日期和时间，格式为YYYY-MM-DD hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59；
  • TIMESTAMP：储存时间戳，格式与DATETIME相同，范围1970-01-01 00:00:01到2038-01-19 03:14:07；

赋值时通过上述格式的字符串赋值即可，还有一些如同NOW()之类的函数也可赋值，但本质可以理解为返回了格式化字符串后赋值的。

# 2. 方便的处理

# 2.1 默认值与自动更新

对TIMESTAMP和DATETIME类型字段，都可以将当前时间戳指定为默认值和/或自动更新值，只需要在创建表格时添加DEFAULT描述和ON UPDATE描述：

// ts和dt将在创建和修改时，被赋值为当前时间
CREATE TABLE t1 (
 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
1
2
3
4
5

官方文档提到CURRENT_TIMESTAMP有很多同义词，他们效果相同：

  • NOW()
  • CURRENT_TIMESTAMP()
  • LOCALTIME/LOCALTIME()
  • LOCALTIMESTAMP/LOCALTIMESTAMP()

# 2.2 毫秒及更高精度

可以在声明字段时添加小数点精度，只需要在TIMESTAMP后添加(n)即可，其中0 ≤ n ≤ 6。

CREATE TABLE t1 (
  ts TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),--正确
  dt DATETIME(4) DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(6) -- 错误
);
1
2
3
4

# 3. 业务表结构设计实战

# 3.1 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的可运维性太差。

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

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

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

# 3.2 不要忽视 TIMESTAMP 的性能问题

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

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

性能不如 DATETIME：DATETIME 不存在时区转化问题。

性能抖动：海量并发时，存在性能抖动问题。

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

[mysqld]
time_zone = "+08:00"
1
2

最后，通过命令 mysqlslap 来测试 TIMESTAMP、DATETIME 的性能，命令如下：

比较time_zone为System和Asia/Shanghai的性能对比
mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'
1
2

最后的性能对比如下：

从表中可以发现，显式指定时区的性能要远远好于直接使用操作系统时区。所以，日期字段推荐使用 DATETIME，没有时区转化。即便使用 TIMESTAMP，也需要在数据库中显式地配置时区，而不是用系统时区。

时区 耗时[秒] 性能提升
System 6.674 /
Asia/Shanghai 4.584 45.59%

# 4. 总结

日期类型通常就是使用 DATETIME 和 TIMESTAMP 两种类型，然而由于类型 TIMESTAMP 存在性能问题，建议你还是尽可能使用类型 DATETIME。我总结一下今天的重点内容：

MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；

DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节，DATETIME(6) 依然占用 8 个字节，TIMESTAMP(6) 占用 7 个字节；

TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险；

使用 TIMESTAMP 必须显式地设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数 time_zone = ‘+08:00’；

推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型；

表结构设计时，每个核心业务表，推荐设计一个 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

# 参考资料

← Canal实现 MySQL binlog解析 →