物理设计-如何存储日期类型

下面我们来看一下最常用的一种日期类型,datetime类型,datetime类型的值呢,分为两部分,日期和时间,

默认情况下呢,起始以4位的年,2位月,2位日,时分秒,这样格式来存储日期时间值的,在MYSQL5.6之前,能存储的

能存储时间的最小精度呢,是为秒,而在MYSQL5.6之后呢,我们可以利用datetime类型,存储包括微秒的时间值,

但是默认情况下,datetime还是只保存到秒的时间,如果我们想要保存微秒呢,就要为日期时间类型,定义一个宽度,

这个宽度呢,最大值就是列,比如我们想在列中保留6位微秒时间的话,就要使用datetime(6)来定义列的类型,另外呢,

datetime是同时区无关的,也就是说呢,存在datetime中的日期呢,是不会跟着时区的变化而变化的,如果我们在多个

不同的时区中呢,使用相同的数据,就需要特别注意了,这和我们的timestamp类型完全不同,对于这一点在我们使用

timestamp类型之后,会给大家来演示一下,大家就可以发现这两种类型在处理日期上有什么区别了,datetime的存储范围呢,

是非常大的,他可以从公元1000年,到公元9999年12月31号,23点59分59秒,datetime类型是最通用的一种时间类型,一般情况

下呢,都可以使用datetime类型呢来存储我们的时间

另外一种日期类型timestamp类型,timestamp类型呢,是一种非常有特色日期时间类型,名字中我们就可以知道,

这种类型存储的是时间戳,也就是格林威治标准时间,就是从1970年1月1日到当前的时间秒数,这和我们所熟悉的linux

时间戳是一样的,timestamp默认是以四位的年,两位的月,和日,以小时分秒的格式,来存储日期时间顺序的,和datetime

类型一样,也可以给timestamp类型呢,指定一个宽度,用来存储微秒数据,相比datetime类型来说,时间戳类型,只占用四个

字节,实际上是以int来存储的,这个就决定了timestamp类型时间范围要比datetime要小的多,由于存储空间的限制,所以时间戳

类型只能保存1970年1月1号到2038年1月19号之间的数据,这一点一定要注意,如果我们要存储这个时间范围内的数据呢,就推荐大家

来使用timestamp类型,因为显示上和datetime是一样的,但是比datetime要更加节约空间,另外刚才也提到过了,timestamp类型呢,

是和datetime类型是不同的

timestamp类型呢,显示的是和时区相关的,我们存储的时间值呢,不同时区下可能显示不同的值,如果我们是在

多个时区的环境中呢,使用这个数据的话,就要注意,datetime和timestamp在时区这个问题上呢,处理是有区别的,

最后timestamp还有一个最大的特点,就是我们可以在一个表中,使用一个timestamp的列,本行的任何数据被修改时

呢,都可以自动修改timestamp的值,这个功能非常的有用,我们经常使用这个功能来标识,每行数据的最后修改时间,

这样我们数据分析类应用就可以用这个列来增量的抽取我们的数据,在数据仓库环境中,下面呢在我们的演示系统中呢,

来为大家演示一下,datetime和timestamp类型呢,一些特点,以及他们如何使用,特别是关于时区上的特点,一定要注意,

下面就进入到我们的演示系统中,我为大家演示一下,刚才所说的一些内容

首先我们来演示datetime和timestamp时区的不同之处,首先呢我们可以先设置一下我们的时区,很简单,我们通过

set time zone呢,设置我们的时区,比如第10时区,这时候呢我们可以建立一张表,这张表只有两列,第一列是datetime类型,

而第二列呢,是timestampe类型

set time_zone='+10:00';

create table t(d1 datetime,d2 timestamp);

那么我们现在在表中随便插入两个时间值吧,以当前时间为准

insert into t values(now(),now());

大家可以看到,现在这两个表中的时间是一样的

同样是13分6秒,如果我们改变了时区,比如我们把时区改成这个

set time_zone='-10:00';

那么同样查询数据会有什么样的变化呢,大家可以看到了

select * from t;

对于timestamp也就是第二列的值,他变成了19点46分,而第一列datetime是和时区是无关的

还是上面的3月5号的3点46分,这就是说,timestamp列中的数据呢,显示是和我们的时区是有关系的,

同样我们来看看这两种是如何来存储微秒值的,前面说到了如果我们想在这里存储微秒值呢,就要对这两列进行宽度的

设置,下面我们就通过alter命令来修改一下列的定义,比如我们把第一列定义成datetime(6),我们同样把第二列定义

为6

alter table t modify d1 datetime(6),modify d2 timestamp(6);

再像其中插入一条数据

insert into t values(now(),now());

这个时候来查看,我们发现,现在第一列和第二列都保存了微秒的数据

我们把刚才那个表删掉

drop table t;

这里我们再建立一个表,同样也叫t表,表中呢有id列,第一列,datetime类型,第二列呢也是timestamp类型,

第三列也同样是timestamp类型,同样给他not null属性,那我们现在给这个表插入数据,看看会有什么样的结果出来,

当前时间,还有一个id列

create table t(id int,d1 datetime,d2 timestampe not null,d3 timestamp not null);

insert into t values(1,now(),now(),now());

我们看看这个表中的数据

select * from t;

大家看到

由于之前我们都插入时间,所以三列的数据是一致的,都是有当前时间的一个值,如果我们只插入id列,会产生什么样的

效果呢,只插入第一列id

insert into t(id) values(1);

再来看看,看看这个结果,大家可以看到

select * from t;

这时候呢,就只有第二列有整体的日期数据了,因为第二列定义的是timestamp类型,所以它具有自动更新的特性

大家也可以看到,虽然说在这张表中呢,定义了两个timestamp类型,第一个timestamp类型是可以自动更新的,

而第二个却不同,这个也是MYSQL的一个特性,就是使用timestamp另一个限制,在一个表中,只有第一个timestamp列,

默认是可以自动更新的,当然我们也可以通过建表语句,指定哪一个timestamp列,自动更新,如果在没有指定的情况下呢,

默认是使用第一个列的,那这里就演示完了timestamp列,还有datetime列的一些特点,那除了datetime列,timestamp列,

在MYSQL5.6中呢,还增加了另外两种时间类型,也就是date类型和time类型,在表存储时间数据时呢,我们经常有这样的

需求,只需要保存日期时间中的某一部分,比如说第七部分,而不用保存时间部分的数据,比如我们在存储用户生日时,

实际上只要存储日期部分就可以了,而在以前要达到这个效果,我们可以分别采用三种方法来实现,第一种就是日期部分存储

为字符串,第二个就是使用int类型来存储,第三种就是使用datetime来存储,而在MYSQL5.7之后呢,又多了一种选择,

使用date类型来存储

而使用date类型的优点呢,在于占用的字节数呢比字符串使用datetime类型呢,可能要更少,甚至比int类型

还要更少,使用字符串存储日期类型呢,至少需要8个字符,8个字节,而使用datetime类型呢,也需要8个字节,

如果使用int类型存储的话,至少需要4个字节,而如果我们使用date类型存储,则使用3个字节就可以了,另外和使用

字符串和int类型相比呢,引用date类型还可以,利用日期时间的函数,来进行日期的计算,这样也就使得int类型,

和字符串类型所使用不到的,那么同时呢,由于这个date类型呢,只是用3个字节来保存

所以他的时间范围是公元1000年1月1号,到公元9999年的12月31号,之间的日期都是可以存储到date类型中的,

另外一种类型呢,称之为time类型,time类型和date类型不同,time存储的是日期时间部分的时间部分,存储格式

是小时分秒,同时也可以通过定义时间类型的宽度呢,来指定保存微秒的数据,前面介绍了常用的时间日期类型,

包括date类型,timestamp类型,date类型和time类型

下面我们来看一看日期时间数据的注意事项,都有哪些,首先第一点要注意的是,千万不要使用字符串类型来

存储日期时间数据,虽然MYSQL提供了多种日期时间类型,而且绝对可以满足我们存储这类数据的需求,但是还是

有很多开发人员喜欢用字符串类型来存储日期时间的数据,我在工作中经常可以遇到这种情况,开发人员还有各种各样

的理由拒绝使用日期时间类型来存储,日期时间数据,比如他们会说,我们需要的只是要保存日期,所以不能使用datetime类型,

那么我们就建议他使用date类型,他又会说了,我们显示的格式和datetime的格式不一样,所以我还是要告诉他们,在MYSQL中

是存在日期格式化函数的,我们完全可以让date类型呢显示成我们想要的格式,说了这么多呢,其实还是很多开发人员,没有

意识到使用日期时间类型来存储日期数据的好处,首先来说,日期时间类型,通常比字符串占用的空间要小,其次呢日期时间类型呢,

在进行查找过滤时呢,可以利用日期来进行对比,而如果我们使用字符串来保存呢,只能按字符集顺序来进行过滤了,这显然是不符合

我们对时间过滤的要求的,最后呢日期时间类型还有着丰富的处理函数,可以方便的对日期类型进行日期的计算,以上都是使用

字符串存储时间类型无法做到的,另外还有一点需要注意到的就是

以前我见过很多人喜欢用int类型来存储日期时间数据,因为MYSQL提供了两个非常方便的函数,在linux时间戳,

进行转换,所以他们认为把日期时间数据呢,转换成linux时间戳进行存储呢,可能效率会更高,但是通过上面介绍呢,

就知道了,如果要存储linux时间戳的话,我们不如直接使用timestamp类型,因为这个类型本身就是以int类型来存储的,

显示的格式呢就是年月日时分秒这样的格式,所以子啊使用时呢更加的方便,不用每次使用都是用函数来转换,可以很方便的

看出来,我们列中存储的日期时间是什么样子的,所以如果timestamp的存储范围可以满足我们日期时间存储的需求的话,我就

推荐大家使用这种类型来进行日期时间的存储