跟我一起学习MySQL技术内幕（第五版）：（第三章学习日记11）

3.2.6.6时态类型的自动特性
3.2.6.7处理时态值
3.2.6.8解释模糊年份值
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3.2.6.6时态类型的自动特性

timestamp和datetime列可以有自动初始化属性和自动更新特性.

1."自动初始化"意味着,对于新行,如果在insert语句里省略了这两种类型的列,那么列会被设置为当前时间戳
2."自动更新"即意味着,对于已有的行,当把任何其他列改为不同值时,这两种类型的列都会被更更新为当前时间戳(**如果将列设置成它的当前值不算自动更新,实际上是在防止它自动更新**)

MySQL5.6.5扩展并推广了对自动特性的支持:任何timestamp列都可以有这两种特性中的一种或者全部,和datetime一样

timestamp的特殊特性:如果将列设置为NULL,那么它的值会被设定为当前时间戳,要想允许timestamp列存储NULL值,需要在创建列时定义为NULL属性.

定义timestamp的语句:

col_name timestamp [default defult_value] [on update current_timestamp]

如果同时指定default和on update属性,他们的顺序无关紧要.默认值可以是current_timestamp像0那样的常量值,或者是格式为’CCYY-MM-DD hh:mm:ss’d的值.也可以使用now().(和current_stamp同义)?

如果不想让拥有自动初始化或自动更新特性的timestamp或datetime列,受到这些特性的影响,那么在执行插入或更新操作时,可以显式的把列设置成所期望的值

timestamp的默认属性是NOT NULL.当把列显式的设置成NULL时,MySQL会把它设置为当前时间戳(对于插入和更新操作都是这样)

如果在列的定义里指定NULL,那么把该列设置成NULL时,存储的将是NULL而不是当前时间戳.

datetime默认属性是NULL,在把datetime设置为NULL时,不会有特殊效果

1.创建一列,在新增行时,列会被设置为当前时间戳,并且之后不会自动更新

create table t1
(
 ts_created timestamp default current_timestamp
);

新建一行:将列设置为NULL,或者在insert语句里省略它.对于后续更新操作,列会保持它的价值,一直到显式更改它为止

2.两列分别用于存储创建时间和最后修改时间

create table t2
(
 ts_created  timestamp default 0,
 ts_modified timestamp default current_time default 
                       on update current_timestamp
);

新建一行:把这两个timestamp列都设置为NULL,从而将它们设置为当时的插入时间戳.如果想要更新已有的行,可以不用管这两列

3.2.6.7处理时态值

MySQL可以正确的解释各种格式的日期和时间列的输入值,其中包括字符串形式和数值型式.此外,time timestamp datetime 还可以带有一个可选的留位精度的小数秒部分.

MySQL也可以解释那些没有世纪部分(CC)的日期和时间格式.对于带分隔符的字符串格式,日期里可以不用”-“时间里不用带”:”.任何一种标点符号都可以用作分隔符.对这些值的解释依据的是上下文而不是分隔符.

对于包含分隔符的字符串格式,当月 日 时 分的值小于10时,可以不用指定两位数字.

对于带有前导零的日期和时间值,MySQL有多种不同的解释方式,具体取决于这些值是以字符串形式指定的,还是以数字形式指定的:例如字符串”001231”会被看作是一个有六位数字的值,如果看作date类型,那么会被解释为”2000-12-31”datetime类型会被看作”2000-12-31 00:00:00”如果被解析器解释为数字会被看作”1231”,于是对它的解释就会不确定.

所以此时最好指定为字符串值,对于别的类型.就需要使用完整形式

一般情况下,date datetime timestamp类型之间可以相互赋值,但是也有一些限制.

1.datetime或timestamp转化为date,时间部分会丢失
2.date赋值为datetime或timestamp结果值的时间部分将会被设置为零"00:00:00" 
3.各个类型取值范围有所不同.尤其是timestamp类型

3.2.6.8解释模糊年份值

对于所有那些带有年份值的日期和时间类型MySQL会在处理那些包含连我给数字的年份值时,会把他们转化为4位数字

1.00~69会被转换为2000~2069
2.70~99会被转换为1970~1999

把不同的二位数字值,存储到year(4)列,然后再把他们检索出来,便可以看到这些转换规则的实际效果

create table y_table (y year(4));
insert into y_table values(68),(69),(99),(00),('00');
select * from y_table;

他们的值分别为 2068 2069 1999 0000 2000
注意到00会被转换为0000而不是2000如果想要利用某个没有包含世纪部分的值得到2000的结果,就需要使用字符串’0’或者’00’.也可以使用cast(value as char)函数来插入year值不管value是字符串还是数字,结果一律是字符串

需要注意的是,MySQL只是提供了一种比较合理的猜测.当未指定世纪部分MySQL并不知道你的2位数字年份具体是哪一年.所以尽量提供一个准确的4位数字的年份值.