MySQL中的多种时间和格式数据类型

关于上图的解释：

1）每种日期和时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将0值插入到数据库中。

2）不同的日期与时间类型有不同的0值，上表中已经详细的列出。

1、year类型

year类型使用1个字节来表示年份，MySQL中以YYYY的形式来显示year类型的值。

给year类型的字段赋值的表示方法如下：

1）使用4位字符串和数字表示。其范围从1901~~2155。输入格式为'YYYY'或YYYY。举个例子，输入‘2008’或者2008，可直接保存2008。如果超过了范围，就会插入0000。

2）使用2位字符串表示。‘00’~~‘69’转换为2000~~2069，‘70’~~‘99’转换为1970~1999。举个例子，输入‘35’,year值会转换成2035，输入‘90’，year值会转换成1990。‘0’和‘00’效果一样。

3）使用2位数字表示。1~~69转换为2001~~2069，70~~99转换为1970~~1999。不举例子了，注意，2位数字和2位字符串是不一样的。如果插入0，转换后的year值不是2000，而是0000。

OK，针对上面的例子，我们来实际操作下数据库。

CREATE TABLE  `linkinframe`.`test` (
 `id` INT NOT NULL,
`a` year NULL,
PRIMARY KEY (`id`));

现在往该表中插入几条数据：

INSERT INTO `linkinframe`.`test` (`id`, `a`)  VALUES('1',1989);
INSERT INTO `linkinframe`.`test` (`id`, `a`)  VALUES('2,1990);
INSERT INTO `linkinframe`.`test` (`id`, `a`)  VALUES('3',1900);
INSERT INTO `linkinframe`.`test` (`id`, `a`)  VALUES('4',2156);

year类型是使用总结：

1）顾名思义，我们一般用year类型来表示年份，所以在对该字段进行相关操作的时候，最好使用4位字符串或者数字表示，不要使用2位的字符串和数字。

2）有的时候可能会插入0或者‘0’。这里要严格区分0和‘0’，如果向year类型的字段插入0，存入该字段的年份是0000；如果向year类型的字段插入‘0’，存入的年份是2000。

当然字符串‘0’和‘00’和‘000’是一样的效果，但是‘0000’效果不一样。因为如果是4个0的话就使用了year类型赋值的第一种方式，也就是超过了year类型的范围，插入了0。

关于这点这里来举一个实际的例子吧。

2、time类型

time类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示Time类型的值。其中，HH表示时；MM表示分，取值范围为0~~59；SS表示秒，取值范围是0~~59。
Time类型的范围可以从‘-838：59：59’~~‘838：59：59’。虽然，小时的范围是0~~23，但是为了表示某种特殊需要的时间间隔，将Time类型的范围扩大了。而且还支持了负值。

Time类型的字段赋值的表示方法如下：

1）‘D HH:MM:SS’格式的字符串表示。其中，D表示天数，取值范围是0~~34。保存时，小时的值等于（D*24+HH）。举个例子，输入‘2 11：30：50’，Time类型会转换为59：30：50。
当然。输入时可以不严格按照这个格式，也可以是‘HH:MM:SS’,'HH:MM','D HH:MM','D HH','SS'等形式。举个例子，输入‘30’，Time类型会自动转换为00：00：30。

2）‘HHMMSS’格式的字符串或HHMMSS格式的数值表示，例如，输入‘123456’，Time类型会转换成12：34：56；输入123456，Time类型会转换成12：34：56。如果输入0或者‘0’，那么TIME类型会转换为0000：00：00。

3）使用current_time或者current_time()或者now()输入当前系统时间。

3、Date类型

date类型使用4个字节来表示日期。MySQL中是以YYYY-MM-DD的形式显示date类型的值。
其中，YYYY表示年，MM表示月，DD表示日。date类型的范围可以从‘1000-01-01’~~‘9999-12-31’。

给DATE类型的字段赋值的表示方法如下：

1）‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表示，这种方式可以表达的范围是‘1000-01-01’~‘9999-12-31’。
举个例子，比如现在我输入‘4008-2-8‘，date类型将转换为4008-02-08；输入’40080308‘，date类型将转换为4008-03-08。

2）当然，MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符。如’YYYY/MM/DD‘，’YYYY@MM@DD‘，’YYYY.MM.DD‘等分隔形式。
举个例子，输入’2011.3.8‘，date类型将转换为2011-03-08。

3）’YY-MM-DD‘或者’YYMMDD‘格式的字符串表示，其中’YY‘的取值，’00‘~~’69‘转换为2000~~2069，’70‘~~’99‘转换为1970~~1999。与year类型类似。
举个例子，比如现在我输入’35-01-02‘，date类型将转换为2035-01-02；输入’800102‘，date类型将转换为1980-01-02。

4）同样的，MySQL中也支持一些不严格的语法格式，比如’YY/MM/DD‘，’YY@MM@DD‘，’YY.MM.DD’等分隔形式。例如，输入‘89@3@8’，date类型将转换为1989-03-08。

5）YYYYMMDD或YYMMDD格式的数字表示。其中，‘YY’的取值，‘00’~~‘69’转换为2000~~2069，‘70’~~‘99’转换为1970~~1999。与year类型类似。
举个例子，比如现在我输入20080808，date类型将转换为2008-08-08；输入790808，date类型将转换为1979-08-08；输入0，那么date类型会转化为0000-00-00。

6）使用current_date或now()来输入当前系统时间。

关于date类型的总结：

1）date类型值战友4个字节，如果只需要记录日期，选择date类型是最合适的。
2）虽然MySQL支持的DATA类型的一些不严格的语法格式，但是，在实际应用中，最好还是选择标准形式。日期中使用“-”做分隔符，时间用“:”做分隔符，然后中间用空格隔开。

比如下面格式：2016-03-17 16:27:55。当然如果有特殊需要，可以使用“@”，“*”等特殊字符做分隔符。

4、Datetime类型

datetime类型使用8个字节来表示日期和时间。MySQL中以‘YYYY-MM-DD HH:MM:SS’的形式来显示dateTime类型的值。
从其形式上可以看出，dateTime类型可以直接用date类型和Time类型组合而成。

给dateTime类型的字段赋值的表示方法如下：

1）‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表示。这种方式可以表达的范围是‘1000-01-01 00:00:00’~~‘9999-12-31 23:59:59’。
举个例子，比如我现在输入‘2008-08-08 08:08:08’，dateTime类型转换为2008-08-08 08:08:08，输入‘20080808080808’，同样转换为2008-08-08 08:08:08。

2）MySQL中还支持一些不严格的语法格式，任何的标点都可以用来做间隔符。情况与date类型相同，而且时间部分也可以使用任意的分隔符隔开，这与Time类型不同，Time类型只能用‘:’隔开呢。
举个例子，比如我现在输入‘2008@08@08 08*08*08’，数据库中dateTime类型统一转换为2008-08-08 08:08:08。

3）‘YY-MM-DD HH:MM:SS’或‘YYMMDDHHMMSS’格式的字符串表示。其中‘YY’的取值，‘00’~~‘69’转换为2000~~2069，‘70’~~‘99’转换为1970~~1999。与year型和date型相同。
举个例子，比如我现在输入‘69-01-01 11:11:11’，数据库中插入2069-01-01 11:11:11；比如我现在输入‘70-01-01 11:11:11’，数据库中插入1970-01-01 11:11:11。

4）当然这种格式化的省略YY的简写也是支持一些不严格的语法格式的，比如用‘@’，‘*’来做间隔符。

5）使用now()来输入当前系统日期和时间。

datetime类型总结：

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

5、timestamp类型

timestamp类型使用4个字节来表示日期和时间。timestamp类型的范围是从1970-01-01 08:00:01~~2038-01-19 11:14:07。

MySQL中也是以‘YYYY-MM-DD HH:MM:SS’的形式显示timestamp类型的值。从其形式可以看出，timestamp类型与dateTime类型显示的格式是一样的。

给timestamp类型的字段复制的表示方法基本与dateTime类型相同。值得注意的是，timestamp类型范围比较小，没有dateTime类型的范围那么大。所以输入值时要保证在timestamp类型的有效范围内。