数据库设计的几个时间问题
2017-06-27 本文已影响121人
简书说我的昵称违规
数据库字段设计上老是会因为几个时间类型而喋喋不休，有的人会忽视掉，有的人会追根究底，但可能最终还是没彻底解决
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 存储空间消耗要少
