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 存储空间消耗要少
网友评论