MySQL 数据库的时间日期数据类型说明

[TOC]

## 时间日期类型
### 1. 时间日期类型介绍

|类型|说明|字节|格式|取值范围|
|--|--|--|--|--|
|datetime |时间日期 |8|YYYY-MM-dd HH:ii:ss |1000年-9999年，有0值：`0000-00-00 00:00:00` |
|date |日期 |3|YYYY-MM-dd |1000年-9999年，有0值：`0000-00-00`|
|time |时间（段）：这里可以表示为时间段，不一定是24小时以内的时间，可以超出24小时，使用负数表示过去的时间 |3|HH:ii:ss |有0值：`00:00:00`|
|timestamp |时间戳：并不是真正的时间戳，它的格式和datetime完全一致 |4|YYYY-MM-dd HH:ii:ss |1970开始，有0值：`0000-00-00 00:00:00`|
|year |年份：它两种形式，year(2)和year(4)|1|YYYY|year(2)：1970-2069<br/>year(4)：1901-2155|

> __附加说明：__MySQL中`explicit_defaults_for_timestamp`变量决定了`timestamp`类型的列，是否默认为必填项和有默认值，如果`explicit_defaults_for_timestamp = OFF`则默认必填，有默认值。`explicit_defaults_for_timestamp = OFF`时创建timestamp类型的列时，自动追加了`DEFAULT CURRENT_TIMESTAMP`和`ON UPDATE CURRENT_TIMESTAMP`属性。有默认值的情况下插入和更新记录时如果没有指定数据，会默认将timestamp类型的列数据更新为当前时间。在MySQL8.0之前版本`explicit_defaults_for_timestamp`默认时OFF，MySQL8.0开始默认为ON了，随意要使用timestamp这个特性时先检查`explicit_defaults_for_timestamp`是否设置为OFF了
```SQL
-- 查询数据库timestamp类型是否是默认必填设置
show global variables like 'explicit_defaults_for_timestamp' ;
-- 修改explicit_defaults_for_timestamp变量的值，注意修改完之后要重启MySQL服务才会生效
set persist explicit_defaults_for_timestamp=OFF;
```

### 2. 日期时间类型应用

```SQL
-- 创建一张全部是时间类型的表
create table test_date(dt datetime,d date,t time,tt timestamp,y year) charset utf8mb4;
```

下表是在MySQL8.0以上版本修改了`explicit_defaults_for_timestamp=OFF`后，创建出来的表结构，<mark>修改数据库变量需要重启数据库服务</mark>

| Field | Type      | Null | Key | Default           | Extra                                         |
|-------|-----------|------|-----|-------------------|-----------------------------------------------|
| dt    | datetime  | YES  |     | NULL              |                                               |
| d     | date      | YES  |     | NULL              |                                               |
| t     | time      | YES  |     | NULL              |                                               |
| ~~tt~~| ~~timestamp~~ | ~~YES~~   |    | ~~NULL~~  | 设置`explicit_defaults_for_timestamp=OFF`后此列无效 |
| tt    | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| y     | year      | YES  |     | NULL              |              &nbsp;                           |

__插入数据：__时间time类型可以是负数，而且可以是很大的负数，year类型可以使用2位数插入，也可以是4位数，下面示例中的注释说明了一些时间日期类型的应用特性

```SQL
-- 正常数据插入
insert into test_date values('2023-03-18 12:22:36','2023-03-18','12:22:36','2023-03-18 12:22:36',2023);
-- time类型字段插入负值，效果见下面表数据第二行，插入负值代表过去的时间
insert into test_date values('2023-03-18 12:22:36','2023-03-18','-12:22:36','2023-03-18 12:22:36',2023);
-- time类型字段插入超过24小时的负值，效果见下面表数据第三行，插入成功说明time是可以代表时间段，不只是24小时以内的时间
insert into test_date values('2023-03-18 12:22:36','2023-03-18','-122:22:36','2023-03-18 12:22:36',2023);
-- time类型的特殊插入方式，例如'-2 22:22:36'表示过去2天加上过去的22:22:36，插入成功后全部换算成小时了
insert into test_date values('2023-03-18 12:22:36','2023-03-18','-2 22:22:36','2023-03-18 12:22:36',2023);
-- year类型除了可以插入4位也可以插入2位，插入2位时需要注意边界值，例如下面year(2)的最大值是2069，插入70时就默认成1970了
insert into test_date values('2023-03-18 12:22:36','2023-03-18','-2 22:22:36','2023-03-18 12:22:36',70);
-- year类型除插入2位，插入2位时需要注意边界值，例如下面year(2)的最大值是2069，69刚好是边界值，所以插入结果是2069
insert into test_date values('2023-03-18 12:22:36','2023-03-18','-2 22:22:36','2023-03-18 12:22:36',69);
```

__表数据：__

| dt                  | d          | t          | tt                  | y    |
|---------------------|------------|------------|---------------------|------|
| 2023-03-18 12:22:36 | 2023-03-18 | 12:22:36   | 2023-03-18 12:22:36 | 2023 |
| 2023-03-18 12:22:36 | 2023-03-18 | -12:22:36  | 2023-03-18 12:22:36 | 2023 |
| 2023-03-18 12:22:36 | 2023-03-18 | -122:22:36 | 2023-03-18 12:22:36 | 2023 |
| 2023-03-18 12:22:36 | 2023-03-18 | -70:22:36  | 2023-03-18 12:22:36 | 2023 |
| 2023-03-18 12:22:36 | 2023-03-18 | -70:22:36  | 2023-03-18 12:22:36 | 1970 |
| 2023-03-18 12:22:36 | 2023-03-18 | -70:22:36  | 2023-03-18 12:22:36 | 2069 |