MySQL的日期类型

本文主要参考：https://blog.csdn.net/u011794238/article/details/50914444

MySQL的日期类型分为五种：分别是year，date，time，datetime，timestamp

1. 各类型的存储空间，最值和零值

-- 创建表
CREATE TABLE tab_date(
	id INT PRIMARY KEY AUTO_INCREMENT,
	_year YEAR,
	_date DATE,
	_time TIME,
	_datetime DATETIME,
	_timestamp TIMESTAMP
);

-- 插入过大的时间
INSERT INTO tab_date (_year) VALUE('2156');
INSERT INTO tab_date (_date) VALUE('10000-1-1');
INSERT INTO tab_date (_time) VALUE('839:00:00');
INSERT INTO tab_date (_datetime) VALUE('10000-1-1 00:00:00');
INSERT INTO tab_date (_timestamp) VALUE(20380119111408);

执行结果

得出的结论：

1. 除了time类型，其他类型当插入的值过大时，会将零值加入到表中
2. 当time类型插入的值过大，会将其最大值加入到表中
3. timestamp类型在不指定时，默认加入的是系统时间

2. year类型

字段的插入1

-- 以数字方式
INSERT INTO tab_date (_year) VALUE(0);     -- 1
INSERT INTO tab_date (_year) VALUE(00);    -- 2
INSERT INTO tab_date (_year) VALUE(000);   -- 3
INSERT INTO tab_date (_year) VALUE(0000);  -- 4
-- 以字符方式
INSERT INTO tab_date (_year) VALUE('0');   -- 5
INSERT INTO tab_date (_year) VALUE('00');  -- 6
INSERT INTO tab_date (_year) VALUE('000'); -- 7
INSERT INTO tab_date (_year) VALUE('0000');-- 8

可见

1. 如果向year类型的字段插入数字0，存入该字段的年份是0000
2. 如果向year类型的字段插入字符‘0’，存入的年份是2000。
3. 字符‘0’和‘00’和‘000’是一样的效果，但是‘0000’效果不一样。

字段的插入2

• 插入的参数有两种，分别是 YYYY 和 YY
• Y表示年，个数表示位数

INSERT INTO tab_date (_year) VALUE(00);     -- 1
INSERT INTO tab_date (_year) VALUE(69);     -- 2
INSERT INTO tab_date (_year) VALUE(70);     -- 3
INSERT INTO tab_date (_year) VALUE(99);     -- 4
INSERT INTO tab_date (_year) VALUE(2014);   -- 5
INSERT INTO tab_date (_year) VALUE('00');   -- 6
INSERT INTO tab_date (_year) VALUE('69');   -- 7
INSERT INTO tab_date (_year) VALUE('70');   -- 8
INSERT INTO tab_date (_year) VALUE('99');   -- 9
INSERT INTO tab_date (_year) VALUE('2014'); -- 10

可见：
共同点：

1. 01 到 69 之间的数字或字符作为参数相当于2000加上该数
2. 70 到 99 之间的数字或字符作为参数相当于1900加上该数
3. 以四位数字或字符作为参数，直接赋值

不同点：

1. 使用2位字符串以 00 作为参数：得到的是2000
2. 使用2位数字作以 00 作为参数：得到的是0000

如果只想保存年，可以考虑使用year类型

3. date类型

字段的插入1

• 插入的参数有两种，分别是 YYYY-MM-DD 和 YY-MM-DD，其中 “-” 为分隔符
• Y表示年 ，M表示月 ，D表示日，个数表示位数
• MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符。如’YYYY/MM/DD‘，’YYYY@MM@DD‘，’YYYY.MM.DD‘等分隔形式。 但是建议使用 “-”

INSERT INTO tab_date (_date) VALUE('00-11-11');   -- 1
INSERT INTO tab_date (_date) VALUE('69-11-11');   -- 2
INSERT INTO tab_date (_date) VALUE('70-11-11');   -- 3
INSERT INTO tab_date (_date) VALUE('99-11-11');   -- 4
INSERT INTO tab_date (_date) VALUE('2014-11-11'); -- 5

可见：

1. 对于参数中年的位数为两位并且该两位数在01 到 69 之间，相当于2000加上该数，其余不变
2. 对于参数中年的位数为两位并且该两位数在70 到 99 之间，相当于1900加上该数，其余不变
3. 以四位的年作为参数，直接赋值

如果只需要记录日期，可以考虑使用date类型

4. time类型

• 插入的参数有多种， D hh:mm:ss ， D hh:mm，D hh，hhmmss，hh:mm:ss，hh:mm，ss等，其中 “:” 为分隔符
• D表示日，H表示小时，m表示分钟，s表示秒，个数表示位数

INSERT INTO tab_date (_time) VALUE('1 01:50:50');   -- 1
INSERT INTO tab_date (_time) VALUE('01:50:50');     -- 2
INSERT INTO tab_date (_time) VALUE('50:05');        -- 3
INSERT INTO tab_date (_time) VALUE('1 05:05');      -- 4
INSERT INTO tab_date (_time) VALUE('59');	    	-- 5
INSERT INTO tab_date (_time) VALUE('123456');	    -- 6
INSERT INTO tab_date (_time) VALUE(123456);	   	    -- 7
INSERT INTO tab_date (_time) VALUE('0'); 	        -- 8
INSERT INTO tab_date (_time) VALUE(0);		        -- 9

可见：

1. 对于带有 D 的参数在保存时，小时的值等于（D*24+HH）。

如果只需要记录日期，可以考虑使用date类型

5. datetime类型

• 插入的参数有多种， YYYY-MM-DD HH:MM:SS，YYYYMMDDHHMMSS，YY-MM-DD HH:MM:SS，YYMMDDHHMMSS等，其中 “:” 为分隔符
• Y表示年 ，M表示月，D表示日，H表示小时，m表示分钟，s表示秒，个数表示位数
• 同样MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符。

INSERT INTO tab_date (_datetime) VALUE('11-11-11 01:50:50');     -- 1
INSERT INTO tab_date (_datetime) VALUE('111111015050');     -- 2
INSERT INTO tab_date (_datetime) VALUE('11-11-11 05:05');      -- 3
INSERT INTO tab_date (_datetime) VALUE('11110505');      -- 4

INSERT INTO tab_date (_datetime) VALUE('70-11-11 01:50:50');     -- 5
INSERT INTO tab_date (_datetime) VALUE('701111015050');     -- 6
INSERT INTO tab_date (_datetime) VALUE('70-11-11 05:05');      -- 7
INSERT INTO tab_date (_datetime) VALUE('7011110505');      -- 8

可见：

1. 年份的规则与前面的一致，只是date和time的相互组合而已

如果只需要记录日期，可以考虑使用date类型
dateTime类型用来记录日期和时间，其作用等价于date类型和Time类型的组合。一个dateTime类型的字段可以用一个date类型的字段和一个time类型的字段代替。但是如果需要同时记录日期和时间，可以考虑datetime

6. timestamp类型

先上代码

INSERT INTO tab_date (_timestamp) VALUE('11-11-11 01:50:50');    -- 1
INSERT INTO tab_date (_timestamp) VALUE('111111015050');    	 -- 2
INSERT INTO tab_date (_timestamp) VALUE('11-11-11 05:05');       -- 3
-- 11110505 被分割为 1111 05 05插入 低于最小值，插入失败
INSERT INTO tab_date (_timestamp) VALUE('11110505');             -- 4

INSERT INTO tab_date (_timestamp) VALUE('70-11-11 01:50:50');    -- 5
INSERT INTO tab_date (_timestamp) VALUE('701111015050');         -- 6
INSERT INTO tab_date (_timestamp) VALUE('70-11-11 05:05');       -- 7
INSERT INTO tab_date (_timestamp) VALUE('7011110505');           -- 8

可见：

1. datetime类型 和 timestamp类型规则是一样的

7. datetime类型 和 timestamp类型

1. timestamp类型有一个很大的特殊点，就是时间是根据时区来显示的，timestamp类型能够根据不同地区的时区来转换时间
2. timestamp类型相对于datetime类型的取值范围过小
3. timestamp不指定时，系统会输入系统当前日期与时间，而datetime类型为null