数据库设计的几个时间问题

数据库字段设计上老是会因为几个时间类型而喋喋不休，有的人会忽视掉，有的人会追根究底，但可能最终还是没彻底解决

database-design-tinted.jpg

timestamp 还是 datetime 还是int ?

前面2个都是数据库原生的时间类型。而且可以进行相互转换，一个是时间戳格式一个是年月日时分秒可读类型。
通常时间类型选择的是出于存储大小的 考虑，为了不浪费空间或能减少索引大小。
那么二者的存储开销是多大呢？
参考官网：https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

Data Type	Storage Required Before MySQL 5.6.4	MySQL 5.6.4
YEAR	1 byte	1 byte
DATE	3bytes	3bytes
TIME	3 bytes	3 bytes + fractional seconds storage
DATETIME	8 bytes	5 bytes + fractional seconds storage
TIMESTAMP	4 bytes	4 bytes + fractional seconds storage

Numeric Type Storage Requirements

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(p)	4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT	4 bytes
DOUBLE[PRECISION], [REAL(https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html)	8 bytes
DECIMAL(M,D), NUMERIC(M,D)	Varies; see following discussion
BIT(M)	approximately (M+7)/8 bytes

可以看到datetime需要8个字节，而timestamp 需要4个字节，int 同样也只需4字节，从存储空间上来说 timestamp和int 要比datetime 要好。

表示范围

• timestamp 采用的是有符号的4字节 也就是最大可表示2的31次方，换算成日期格式 就是2038年
• datetime 可表示到9999年。
• 若采用int 表示时间，并且设置为无符号，那么最大表示值是2的32次方，能表示到2106年。

如上，若从表示范围来考虑，datetime又会好于无符号int ,int 又会好于timestamp

结论呢？

• 如果你确信你的系统能撑到2038年这么久，那么2038危机将是一个大问题 timestamp 不适合你。
• 如果你觉得2106还不够长，这个对你很重要，那好吧，datetime 吧
• int 型能表示到2106，100多年后了，系统挂了也不找不到你了(开个玩笑= =)
• int 型能防止 某些差的 sql 语句 使用 datetime 时间函数 查找 而进行的全表扫描。例如：select date(create_time) as date
• int 不方便数据展示，一般时间字段都会格式化输出
• int 存储空间消耗要少