开发者社区> 北在南方> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【MySQL】时间类型存储格式选择

简介:
+关注继续查看
一  前言
  昨天在给开发同学做数据库设计规范分享的时候,讲到时间字段常用的有三个选择datetime、timestamp、int,应该使用什么类型的合适?本文通过三种类型的各个维度来分析,声明:本文没有具体的结论,但是会给一个推荐使用方式,需要使用者结合自己的业务场景来具体选择。

二 分析
int型:
存储长度: 4字节
表示范围: date('Y-m-d H:i:s', 4294967295) 最大到 2106-02-07 14:28:15 ,如果一个企业活过这么久,就需要数据库考虑 bigint 或者datetime类型了。
是否为空: 可以为空,但是业务逻辑设计建议设置非空
存储格式: 数值类型存储,节省空间
时区相关: 与时区无关
默认值 :  可以根据业务逻辑设置默认值为某个时间。
优点
  1 类型简单,cpu处理该字段的运算会比较快,占用字节小,节省空间。
  2 查询速度快。

datetime:
存储长度: 8字节
表示范围:'1000-01-01 00:00:00'-'9999-12-31 23:59:59'
是否为空: 允许为空值,可以自定义值,且insert和update操作不会自动修改其值。
储存格式: 以实际格式存储(Just stores what you have stored and retrieves the same thing which you have stored.)
时区相关: 与时区无关
默认值  : 不指定默认值的时候 MySQL会初始化为'0000-00-00 00:00:00'
mysql> CREATE TABLE `tm` (
    -> `d1` int(10) unsigned NOT NULL default '0',
    -> `d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
    -> `d3` datetime NOT NULL,
    -> `d4` timestamp NOT NULL default CURRENT_TIMESTAMP on update current_timestamp
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tm(d1,d4) values(1458612980,now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from tm;
+------------+---------------------+---------------------+---------------------+
| d1         | d2                  | d3                  | d4                  |
+------------+---------------------+---------------------+---------------------+
| 1458612980 | 2016-03-22 10:16:20 | 2016-03-22 15:21:21 | 2016-03-22 10:16:20 |
| 1458612980 | 2016-03-22 15:22:17 | 0000-00-00 00:00:00 | 2016-03-22 15:22:17 |
+------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
优点 显示直观,不需使用函数做转换
 
timestamp:
存储长度: 4字节
是否为空: 允许为空值,但是不可以自定义值,所以为空值时没有任何意义。
表示范围:'1970-01-01 00:00:01'-'2038-01-19 03:14:07
是否为空: 允许为空值,可以自定义值,且insert和update操作不会自动修改其值。
存储格式: 值以UTC格式保存,即以毫秒为单位的数字存储 ( it stores the number of milliseconds)
时区相关: 和时间相关,时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区。
默认值 :  可以设置为CURRENT_TIMESTAMP(),当前的系统时间。
gmt_modified timestamp not null default '0000-00-00 00:00:00' on update current_timestamp
字段属性加上 "on update current_timestamp",
1 在更新记录时不指定update timestamp字段的值,数据库会自动修改gmt_modified的值为当前系统的时间,
2 在插入记录时不指定timestamp字段和timestamp字段的值,插入后该字段的值会自动变为当前系统时间。
相比于 init 类型的 可以自动更新为系统当前时间,其他并无优势。
三 总结
 对于如何选型 ,有如下三种层面 性能,存储空间,时间范围 的考虑。这里我从时间范围和存储空间层面推荐使用 int 或者bigint ,datetime 类型。bigint和datetime 占用的空间一样,唯一的差异是在性能上可能存在差异。当然如果你服务的企业有存在  102年的梦想,那我建议直接使用 datetime类型。2038年的时候,虽然我们(80后)估计已经在家养老或者身居高层,为了避免给后来的运维人员留坑,建议不要使用 timestamp 字段。
四 推荐阅读

1 datatime和timstmap初始化 
datetime官方文档 
3 说说time_zone 带来的性能问题  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL 在 linux 下的安装|学习笔记
快速学习MySQL 在 linux 下的安装
41 0
MySQL 在 Mac 下的安装|学习笔记
快速学习MySQL 在 Mac 下的安装
81 0
MySQL的安装手册
指导如何在centos环境下安装MySQL
65 0
ubuntu安装mysql
ubuntu安装mysql
238 0
【实施工程师之家】linux安装mysql(yum安装mysql)
【实施工程师之家】linux安装mysql(yum安装mysql)
110 0
linux下通过Docker安装mysql
个人学习使用,安装mysql的话,使用上面的简单参数就ok,如果要考虑到以后的升级备份之类的 ,需要加入其他的参数,比如把数据映射到本地某个目录,容器删除重建之后,数据不会丢失。
64 0
Linux安装 MySQL + Navicat连接数据库 + 数据库迁移
Linux安装 MySQL + Navicat连接数据库 + 数据库迁移
63 0
Ubuntu 通过 Deb 安装 MySQL| 学习笔记
快速学习 Ubuntu 通过 Deb 安装 MySQL
80 0
记录一次Linux下安装MySql过程
记录一次Linux下安装MySql过程
37 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
好的 MySQL 兼容可以做到什么程度
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
MySQL表和索引优化实战
立即下载