读MySQL 5.7文档11.2 Date and Time Data Types总结

柔光的暖阳◎ 2023-07-13 05:57 18阅读 0赞

> 作者：魏新平，知数堂第5期MySQL实战班学员，第10期MySQL优化班学员，现任职助教。

# 读MySQL5.7文档11.2 Date and Time Data Types #

MySQL的时间类型分为DATE, DATETIME, TIMESTAMP, TIME, YEAR五个类型。接下来为大家一一介绍下。

## DATE类型 ##

存储YYYY-MM-DD类型的时间，取值范围是'1000-01-01' to '9999-12-31'。

## DATETIME和TIMESTAMP类型 ##

两个类型都是用来存储YYYY-MM-DD hh:mm:ss\[.fraction\]格式的时间。不过在取值范围和存储方式上有些不同。

### 取值范围 ###

DATETIME的取值范围是'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59', TIMESTAMP的取值范围是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC。可以看出来DATETIME的取值范围大很多。

### 存储方式 ###

DATETIME是直接存储插入的时间。而TIMESTAMP是将插入的值转换为utc时间，然后存入数据库，取出来的时候再根据当前会话的时区进行转换显示。

admin@localhost [weixinpingtest] 10:05:51>create table testtime(a timestamp,b datetime);
    Query OK, 0 rows affected (0.05 sec)
    admin@localhost [weixinpingtest] 10:07:20>insert into testtime() values(now(),now());
    Query OK, 1 row affected (0.01 sec)
    admin@localhost [weixinpingtest] 10:07:37>select * from testtime;
    +---------------------+---------------------+
    | a | b |
    +---------------------+---------------------+
    | 2020-03-04 10:07:37 | 2020-03-04 10:07:37 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    admin@localhost [weixinpingtest] 10:07:43>set timezone = '+9:00';#修改时区以后，下面显示的时间就不一样了
    Query OK, 0 rows affected (0.00 sec)
    admin@localhost [weixinpingtest] 10:07:50>select * from testtime;
    +---------------------+---------------------+
    | a | b |
    +---------------------+---------------------+
    | 2020-03-04 11:07:37 | 2020-03-04 10:07:37 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)

可以看到只要修改时区，第二次查询的时候，timestamp类型的字段时间多了一个小时，而datetime类型的字段时间没有变化。

### 自动初始化和更新 ###

虽然有点废话，但是还是要介绍一下这两个功能到底是什么作用。

自动初始化的关键字是DEFAULT CURRENT\_TIMESTAMP。

CREATE TABLE t1 (
    a int,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    )

当往表t1中插入数据时，如果没有指定ts的值，那么ts会被设置为当前时间。

自动更新的关键字是ON UPDATE CURRENT\_TIMESTAMP

CREATE TABLE t1 (
    a int,
    ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

当表t1的a字段更新时，ts会自动更新到修改的时间。但是如果a修改的时候值没有变化，那么ts也不会变化。

DEFAULT CURRENT\_TIMESTAMP和ON UPDATE CURRENT\_TIMESTAMP可以同时指定，或者只指定其中一个。两者的先后顺序不会有什么影响。

### explicit\_defaults\_for\_timestamp变量 ###

针对timestamp还有一个数据库变量。explicit\_defaults\_for\_timestamp。这个变量默认是off的。那么off的时候会有什么现象呢。

*  在创建表的时候，如果自动初始化和自动更新都没有添加。那么第一个timestamp字段会自动加上DEFAULT CURRENT\_TIMESTAMP 和ON UPDATE CURRENT\_TIMESTAMP。

admin@localhost [weixinpingtest] 10:07:52>show global variables like '%explicitdefaultsfortimestamp%';
    +---------------------------------+-------+
    | Variablename | Value |
    +---------------------------------+-------+
    | explicitdefaultsfortimestamp | OFF |
    +---------------------------------+-------+
    1 row in set (0.00 sec)
    admin@localhost [weixinpingtest] 10:15:14>create table testt3(a timestamp,b timestamp);
    Query OK, 0 rows affected (0.02 sec)
    admin@localhost [weixinpingtest] 10:15:44>show create table testt3;
    +---------+------------------------------------------------------------------------------+
    | Table | Create Table
    +---------+------------------------------------------------------------------------------+
    | testt3 | CREATE TABLE test_t3 (a timestamp NOT NULL DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENT_TIMESTAMP,b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
    1 row in set (0.00 sec)

有两种方法可以阻止这种现象的发生，第一个是直接设定explicit\_defaults\_for\_timestamp参数为on,第二个是给字段设置默认值，或者直接设置可以为可以为null。

*  timestamp没有指定允许null的情况下是不能插入null的，插入null的时候会默认转换成当前时间

admin@localhost [weixinpingtest] 10:21:47>show create table testt4;
    +---------+--------------------------------------------+
    | Table | Create Table |
    +---------+-------------------------------------------+
    | test_t4 | CREATE TABLE test_t4 (a timestamp NULL DEFAULT NULL,b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
    +---------+-------------------------------------------+
    1 row in set (0.00 sec)
    admin@localhost [weixinpingtest] 10:21:54>insert into testt4() values(null,null);
    Query OK, 1 row affected (0.01 sec)
    admin@localhost [weixinpingtest] 10:22:03>select * from testt4;
    +------+---------------------+
    | a | b |
    +------+---------------------+
    | NULL | 2020-03-05 10:22:03 |
    +------+---------------------+
    1 row in set (0.00 sec)
    admin@localhost [weixinpingtest] 10:22:12>set explicitdefaultsfortimestamp=on;
    Query OK, 0 rows affected (0.00 sec)
    admin@localhost [weixinpingtest] 10:25:11>insert into testt4() values(null,null);
    ERROR 1048 (23000): Column 'b' cannot be null

test\_t4表当中a字段可以为null，b字段默认，不能为null，但是插入null值都没有报错，只不过a字段变成了null，b字段变成了当前时间。后面我开启了explicit\_defaults\_for\_timestamp,再次插入null，就报错，说b不能为null了。

至于要不要开启这个变量就看你自己了，反正是可以在线关闭和开启的。

### 秒后面的小数 ###

DATETIME和TIMESTAMP都是可以在定义字段类型时加上(n),比如DATETIME(3)，这里的3表示秒后面的小数可以精确到几位。n的取值范围是0-6。如果不设定，默认是0。需要注意的是,当指定了初始化和自动更新时，后面的也要加上这个数字。而且必须保持一致。如下

admin@localhost [weixinpingtest] 10:34:53>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(6) );
    Query OK, 0 rows affected (0.01 sec)
    admin@localhost [weixinpingtest] 10:35:29>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(3) );
    ERROR 1294 (HY000): Invalid ON UPDATE clause for 'ts' column

如果不一致就会报错。

## TIME类型 ##

存储hh:mm:ss类型的数据，取值范围是'-838:59:59.000000' to '838:59:59.000000'。范围可以取这么大的原因是time还可以表示时间差，所以范围会大于24个小时，甚至是负数。TIME也是支持秒的小数的。

## YEAR类型 ##

存储年份的。取值范围是1901 - 2155 和 0000。

## tips ##

针对DATE和DATETIME，表示的范围非常大，但是mysql有一句提示语。For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.表示虽然支持那么大的时间范围，但是我不保证这个能行。

--------------------

由叶老师主讲的知数堂「MySQL优化课」第17期已发车，我们的课程从第15期就升级成MySQL 8.0版本了，现在上车刚刚好，一起开启MySQL 8.0的修行之旅吧。

扫码入群了解「MySQL优化」课吧，有助教妹子在等着哟

![format_png][]

--------------------

另外，叶老师在腾讯课堂的短课程《**MySQL性能优化**》已开课，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。

下面是报名小程序码

![format_png 1][]

点“在看”给我一朵小黄花![format_png 2][]

![format_png 3][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeUE0TWRFaWF1UDBlR1lMdVlXNXhORU9tdzNHeHlkWGxiRGcyS0Rrd2liU2lhS0lZS1Y2aFQ5MDNvcnhERmliOVZSYzFTWkRQVXUxTmJvWUEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X2dpZi83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FKZHlIUFE0VWxBa1Y4aWF6b1BsRDJITEFOaWFZMWNMN2pVOUtuekhiNFVPNGszOThLazBUdDViQS82NDA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FqSGtXWHBPcGttZG5HMlFXSWxPbjJVeE9KUkNFdDNTVWdGY1FiVTlvNFdHc1p2OTVvRGhZMVEvNjQw?x-oss-process=image/format,png