Mysql日期/时间数据类型实战

超、凢脫俗 2021-07-24 13:03 552阅读 0赞

**一 创建数据表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查看错误信息：

可以看到，插入的第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;查看错误信息，如下所示：

可以看到，第二次在插入记录的时候，数据超出了范围，原因是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 HH:MM:SS"和"YYYYMMDDHHMMSS"字符串格式日期和时间值**

首先创建表tmp6：

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

向表中插入“YYYY-MM-DD HH:MM:SS”和“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 HH:MM:SS"和"YYMMDDHHMMSS"字符串格式日期和时间值**

首先删除表中的数据：

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

向表中插入“YY-MM-DD HH:MM:SS”和“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)

**三三 日期类型**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/4289865ab00b47618a2dad3caec78113.png