mysql数据库----时间和日期类型

为了方便在数据库中存储日期和时间，MySQL提供了表示日期和时间的数据类型，分别是YEAR. DATE、TIME、DATETIME和TIMESTAMP。表3-4列举了这些MySQL中日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值。
表3-4 MySQL日期和时间类型

数据类型	取值范围	日期格式	零值
YEAR	1901~2155	YYYY	00
DATE	1000-01-01~9999-12-3	YYYY-MM-DD	0000-00-00
TIME	-838:59:59~838:59:59	HH:MM:SS	00:00:00
DATETIME	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

在表3-4中，日期格式YYYY表示年，MM表示月，DD表示日。每种日期和时间类型的取值范围都是不同的。需要注意的是，如果插入的数值不合法，系统会自动将对应的零值插入数据库中。
为了让读者更好地学习日期和时间类型，接下来分别进行详细讲解。

1．YEAR类型

YEAR类型用于表示年份，使用示例如下。

CREATE TABLE my_year (y YEAR);          # 设置y字段的数据类型为YEAR
INSERT INTO my_year VALUES(2020);       # 插入年份数据，2020年

在MySQL中，可以使用以下3种格式指定YEAR类型的值。
(1)使用4位字符串或数字表示，为1901’_{2155’或1901}2155。例如，输入’2020’或2020，插入到数据库中的值均为2020。
(2)使用两位字符串表示为00’_{99’，其中，‘00’}‘69’的值会被转换为2000_{2069的YEAR值，70’}99"的值会被转换为1970~1999的YEAR值。例如，输入20’，插入到数据库中的值为2020。
(3) 使用两位数字表示为1_{99，其中，1}69的值会被转换为2001~ 2069的YEAR值，70_{99的值会被转换为1970}1999的YEAR值。例如，输入20，插入到数据库中的值为2020。
需要注意的是，当使用YEAR类型时，一定要区分0和0。因为字符串格式的0表示的YEAR值是2000，而数字格式的0表示的YEAR值是0000。

2．DATE类型

DATE类型用于表示日期值，不包含时间部分，使用示例如下。
#设置d字段的数据类型为DATE

CREATE TABLE my_date (d DATE);

#插入日期数据

INSERT INTO my_date VALUES('2020-01-21');

#插入当前系统日期

INSERT INTO my_date VALUES(CURRENT_DATE);

插入当前系统日期

INSERT INTO my_date VALUES(NOW());

在MySQL中，可以使用以下4种格式指定DATE类型的值。
(1)以YYYY-MM-DD或者YYYYMMDD字符串格式表示。
例如，输入*2020-01-21’或20200121"，插入数据库中的日期都为2020-01-21。
(2)以Y Y-MM-DD或者’Y YMMDD字符串格式表示。YY表示的是年，为00’~ "99’，其中00’_{'69"的值会被转换为2000}2069的值，70人’99’的值会被转换为1970~1999的值。
例如，输入20-01-21’或200121’，插入数据库中的日期都为2020-01-21。
(3)以YY-MM-DD或者Y YMMDD数字格式表示。
例如，输入20-01-21或200121，插入数据库中的日期都为2020-01-21。
(4)使用CURRENT_ DATE或者NOW()输入当前系统日期。

3．TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时，MM表示分，SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
(1)以’HHMMSS’字符串或者’HHMMSS’数字格式表示。
例如，输入’345454’或’345454’，插入数据库中的时间为34:54:54(34小时54分54秒)。
(2)以’D HH:MM:SS’字符串格式表示。其中，D表示日，可以取0~34之间的值，插入数据时，小时的值等于(D×24+HH)。
例如，输入’2 11:30:50’，插入数据库中的时间为59:30:50；输入’11:30:50’，插入数据库中的时间为11:30:50；输入’34 22 :59:59’，插入数据库中的时间为838 :59:59。
(3)使用CURRENT_TIME或NOW()输入当前系统时间。

4．DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS表示秒。
在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
(1)以’YYY Y-MM-DD HH: MM:SS’或者’YYY YMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00 :00 :00’~ ‘9999-12-31 23:59:59’。
例如，输入’2014-01-22 09 :01 :23’或20140122090123，插入数据库中的DATETIME值都为2014-01-22 09:01:23。
(2)以’YY-MM-DD HH : MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’~‘99’。与DATE类型中的YY相同，‘00’_{'69’范围的值会被转换为2000}2069范围的值，‘70’_{'99’范围的值会被转换为1970}1999范围的值。
(3)以YYYYMMDDHHMMSS或者Y YMMDDHHMMSS数字格式表示的日期和时间。
例如，插入20140122090123或者140122090123，插入数据库中的DATETIME值都为2014-01-22 09 :01:23。
(4)使用NOW()来输入当前系统的日期和时间。

5．TIMESTAMP类型

TIMESTAMP(时间戳)类型用于表示日期和时间，它的显示形式与DATETIME相同，但取值范围比DATETIME小。下面介绍几种TIMESTAMP类型与DATATIME类型不同的形式，具体如下。
(1)使用CURRENT_TIMESTAMP来输入系统当前日期和时间。
(2)无任何输入，或输入NULL时，实际保存的是系统当前日期和时间。

在MySQL中，TIMESTAMP字段默认情况下会自动设置NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 属性，具体解释如下。
(1) NOT NULL表示非空约束，该字段将不允许保存NULL值。
(2) DEFAULT表示默认约束，当字段无任何输入时，自动设置某个值作为默认值。此处设为CURRENT TIMESTAMP表示使用系统当前日期和时间作为默认值。
(3)ONUPDATE用于当一条记录中的其他字段被UPDATE语句修改时，自动更改该字段为某个值。此处设为CURRENT TIMESTAMP表示每次修改时保存修改时的系统日期和时间。
若为TIMESTAMP字段手动设置DEFAULT属性时，该字段将不会自动设置ON UPDATE属性，通过如下SQL语句可进行测试。

CREATE TABLE my_timestamp (
  t1 TIMESTAMP,
  t2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

上述SQL语句通过t1、t2、t3字段演示了TIMESTAMP 字段的3种使用方式。其中，t1和t2的设置结果是相同的，t3没有设置ON UPDATE属性。
通过DESC查看表结构，会发现t1和t2字段具有DEFAULT和ON UPDATE属性，t3字段没有ON UPDATE属性，如下所示。

mysql> DESC my_timestamp;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| t2    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| t3    | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

值得一提的是，若使用MySQL 5.6之前的版本，my_timestamp 表会创建失败。这是因为MySQL 5.6之前的版本在一张表中只允许一个字段使用CURRENT_TIMESTAMP作为DEFAULT和ON UPDATE的值。此时可以分成多张表进行测试。

秒客网