Mysql日期/时间数据类型实战-蒲公英云

一创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，’2010’，’2166’

首先创建表tmp3：

mysql> CREATE TABLE tmp3(  y YEAR );
Query OK, 0 rows affected (0.27 sec)

向表中插入数据：

mysql> INSERT INTO tmp3 values(2010),('2010');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

再次向表中插入数据：

mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1

语句执行之后，MySQL给出了一条错误提示，使用SHOW查看错误信息：

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'y' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

可以看到，插入的第3个值2166超过了YEAR类型的取值范围，此时不能正常的执行插入操作，查看结果：

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
+------+
2 rows in set (0.00 sec)

由结果可以看到，当插入值为数值类型的2010或者字符串类型的’2010’时，都正确的储存到了数据库中；而当插入值’2166’时，由于超出了YEAR类型的取值范围，因此，不能插入值。

二向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’

首先删除表中的数据：

mysql> DELETE FROM tmp3;
Query OK, 2 rows affected (0.03 sec)

向表中插入数据：

mysql> INSERT INTO tmp3 values('0'),('00'),('77'),('10');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看结果：

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
4 rows in set (0.00 sec)

由结果可以看到，字符串’0’和’00’的作用相同，分别都转换成了2000年；’77’转换为1977；’10’转换为2010。

三向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、78和11

首先删除表中的数据：

mysql> DELETE FROM tmp3;
Query OK, 4 rows affected (0.03 sec)

向表中插入数据：

mysql> INSERT INTO tmp3 values(0),(78),(11);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看结果：

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 0000 |
| 1978 |
| 2011 |
+------+
3 rows in set (0.00 sec)

由结果可以看到，0被转换为0000；78被转换为1978；11被转换为2011。

四创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’

首先创建表tmp4，

mysql> CREATE TABLE tmp4( t TIME );
Query OK, 0 rows affected (0.20 sec)

向表中插入数据：

mysql> INSERT INTO tmp4 values('10:05:05'), ('23:23'), ('2 10:10'), ('3 02'),('10');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

查看结果：

mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:05:05 |
| 23:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
5 rows in set (0.00 sec)

由结果可以看到，’10:05:05’被转换为10:05:05；’23:23’被转换为23:23:00；’2 10:10’被转换为58:10:00，’3 02’被转换为74:00:00；’10’被转换成00:00:10。

在使用’D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。

五向表tmp4中插入值’101112’，111213,’0’，107010

首先删除表中的数据：

mysql> DELETE FROM tmp4;
Query OK, 5 rows affected (0.08 sec)

向表中插入数据：

mysql> INSERT INTO tmp4 values('101112'),(111213),( '0');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

再向表中插入数据：

mysql> INSERT INTO tmp4 values ( 107010);
ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1

可以看到，在插入数据时，MySQL给出了一个错误提示信息，使用SHOW WARNINGS;查看错误信息，如下所示：

mysql> show warnings;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1292 | Incorrect time value: '107010' for column 't' at row 1 |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，第二次在插入记录的时候，数据超出了范围，原因是107010的分钟部分超过了60，分钟部分是不会超过60的，查看结果：

mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:11:12 |
| 11:12:13 |
| 00:00:00 |
+----------+
3 rows in set (0.00 sec)

由结果可以看到，’101112’被转换为10:11:12；111213被转换为11:12:13；’0’被转换为00:00:00；107010因为是不合法的值，因此不能被插入。

也可以使用系统日期函数向TIME字段列插入值。

六向tmp4表中插入系统当前时间

首先删除表中的数据：

mysql> DELETE FROM tmp4;
Query OK, 3 rows affected (0.05 sec)

向表中插入数据：

mysql> INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看结果：

mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 16:21:45 |
| 16:21:45 |
+----------+
2 rows in set (0.00 sec)

由结果可以看到，获取系统当前的日期时间插入到TIME类型列，因为读者输入语句的时间不确定，因此获取的值与这里的可能是不同的，但都是系统当前的日期时间值。

七创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期

首先创建表tmp5：

mysql> CREATE TABLE tmp5(d DATE);
Query OK, 0 rows affected (0.18 sec)

向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式日期：

mysql> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1998-08-08 |
| 1998-08-08 |
| 2010-10-10 |
+------------+
3 rows in set (0.00 sec)

可以看到，各个不同类型的日期值都正确的插入到了数据表中。

八向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期

首先删除表中的数据：

mysql> DELETE FROM tmp5;
Query OK, 3 rows affected (0.03 sec)

向表中插入“YY-MM-DD”和“YYMMDD”格式日期：

mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1999-09-09 |
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
+------------+
4 rows in set (0.00 sec)

九向tmp5表中插入YYMMDD数字格式日期

首先删除表中的数据：

mysql> DELETE FROM tmp5;
Query OK, 4 rows affected (0.02 sec)

向表中插入YY-MM-DD和YYMMDD数字格式日期：

mysql> INSERT INTO tmp5 values (990909), (000101) ,(111111);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1999-09-09 |
| 2000-01-01 |
| 2011-11-11 |
+------------+
3 rows in set (0.00 sec)

十向tmp5表中插入系统当前日期

首先删除表中的数据：

mysql> DELETE FROM tmp5;
Query OK, 3 rows affected (0.03 sec)

向表中插入系统当前日期：

mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );
Query OK, 2 rows affected, 1 warning (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 1

查看插入结果：

mysql> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 2020-01-02 |
| 2020-01-02 |
+------------+
2 rows in set (0.00 sec)

CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

MySQL允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，’98-11-31’、’98.11.31’、’98/11/31’和’98@11@31’是等价的，这些值也可以正确的插入到数据库。

十一创建数据表tmp6，定义数据类型为DATETIME的字段dt，向表中插入”YYYY-MM-DD HHSS”和”YYYYMMDDHHMMSS”字符串格式日期和时间值

首先创建表tmp6：

mysql> CREATE TABLE tmp6(  dt DATETIME );
Query OK, 0 rows affected (0.17 sec)

向表中插入“YYYY-MM-DD HHSS”和“YYYYMMDDHHMMSS”格式日期：

mysql> INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1998-08-08 08:08:08 |
| 1998-08-08 08:08:08 |
| 2010-10-10 10:10:10 |
+---------------------+
3 rows in set (0.00 sec)

可以看到，各个不同类型的日期值都正确的插入到了数据表中。

十二向tmp6表中插入”YY-MM-DD HHSS”和”YYMMDDHHMMSS”字符串格式日期和时间值

首先删除表中的数据：

mysql> DELETE FROM tmp6;
Query OK, 3 rows affected (0.03 sec)

向表中插入“YY-MM-DD HHSS”和“YYMMDDHHMMSS”格式日期：

mysql> INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1999-09-09 09:09:09 |
| 1999-09-09 09:09:09 |
| 2010-10-10 10:10:10 |
+---------------------+
3 rows in set (0.00 sec)

十三向tmp6表中插入YYYYMMDDHHMMSS 和YYMMDDHHMMSS数字格式日期和时间值

首先删除表中的数据：

mysql> DELETE FROM tmp6;
Query OK, 3 rows affected (0.02 sec)

向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式日期和时间：

mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 1999-09-09 09:09:09 |
| 2010-10-10 10:10:10 |
+---------------------+
2 rows in set (0.00 sec)

十四向tmp6表中插入系统当前日期和时间值

首先删除表中的数据：

mysql> DELETE FROM tmp6;
Query OK, 2 rows affected (0.03 sec)

向表中插入系统当前日期：

mysql> INSERT INTO tmp6 values( NOW() );
Query OK, 1 row affected (0.03 sec)

查看插入结果：

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 2020-01-02 16:40:09 |
+---------------------+
1 row in set (0.00 sec)

十五创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值’19950101010101’，’950505050505’，’1996-02-02 02:02:02’，’97@03@03 03@03@03’，121212121212，NOW()

CREATE TABLE tmp7( ts TIMESTAMP);

向表中插入数据：

INSERT INTO tmp7 values ('19950101010101'),
('950505050505'),
('1996-02-02 02:02:02'),
('97@03@03 03@03@03'),
(121212121212),
( NOW() );

查看插入结果：

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2020-01-02 16:41:59 |
+---------------------+
6 rows in set (0.00 sec)

由结果可以看到，’19950101010101’被转换为1995-01-01 01:01:01；’950505050505’被转换为1995-05-05 05:05:05；’1996-02-02 02:02:02’被转换为1996-02-02 02:02:02；’97@03@03 03@03@03’被转换为1997-03-03 03:03:03；121212121212被转换为2012-12-12 12:12:12；NOW()被转换为系统当前日期时间2013-03-24 09:17:49。

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

十六向tmp7表中插入当前日期，查看插入值，更改时区为东10区，再次查看插入值

首先删除表中的数据：

mysql> DELETE FROM tmp7;
Query OK, 6 rows affected (0.03 sec)

向表中插入系统当前日期：

mysql> INSERT INTO tmp7 values( NOW() );
Query OK, 1 row affected (0.02 sec)

查看当前时区下日期值：

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 2020-01-02 16:44:28 |
+---------------------+
1 row in set (0.00 sec)

查询结果为插入时的日期值，读者所在时区一般为东8区，下面修改当前时区为东10区，SQL语句如下：

mysql> set time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)

再次查看插入时的日期值：

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 2020-01-02 18:44:28 |
+---------------------+
1 row in set (0.00 sec)