MySQL最佳入门 - MySQL数据类型之日期和时间类型

本篇文章MySQL版本：

+-----------+
| version() |
+-----------+
| 5.5.53    |
+-----------+

目录

 

日期和时间类型

YEAR(M)

创建表

正常操作

插入0操作

插入‘0’操作

TIME

创建表

正常操作

不带:操作

空格操作

DATE

创建表

正常操作

DATETIME

创建表

正常操作

完整格式

简写格式

TIMESTAMP（时间戳）

创建表

正常操作

脏数据

---

日期和时间类型

• MySQL中提供了多种用于存储日期和时间的类型；
• 一般根据需要进行选择，并注意格式。

YEAR(M)

M 可以指定2，或者4

4位数年份存储1901-2155；

2位数年份存储1970-2069。

创建表

创建一个 4位年份的字段，以及一个 2位年份的字段：

mysql> create table t_year(
    -> col1 year(4),
    -> col2 year(2)
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

正常操作

分别插入 2019 19数据：

mysql> insert into t_year values(2019,19);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_year;
+------+------+
| col1 | col2 |
+------+------+
| 2019 |   19 |
+------+------+
1 row in set (0.00 sec)

插入0操作

mysql> insert into t_year values(0,0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_year;
+------+------+
| col1 | col2 |
+------+------+
| 2019 |   19 |
| 0000 |   00 |
+------+------+
2 rows in set (0.00 sec)

无效数据，直接补0处理。

插入‘0’操作

mysql> insert into t_year values('0','0');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_year;
+------+------+
| col1 | col2 |
+------+------+
| 2019 |   19 |
| 0000 |   00 |
| 2000 |   00 |
+------+------+
4 rows in set (0.00 sec)

单引号，把数字括进去，当前年份最右面位数，4位默认从2000开始，补0，找最多0的年份。

TIME

小时：分钟：秒钟

范围：过去的时间开始计算。

创建表

mysql> create table t_time(
    -> col1 time
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t_time;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

正常操作

插入一条正常数据，用“”包括：

mysql> insert into t_time values ('10:10:10');
Query OK, 1 row affected (0.01 sec)

不带:操作

mysql> insert into t_time values (1122);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_time;
+----------+
| col1     |
+----------+
| 10:10:10 |
| 00:11:22 |
+----------+
2 rows in set (0.00 sec)

没有：的时候，MySQL默认为最右面的数字为秒。当然不带：的情况下，用不用“”都是一样的效果。

空格操作

mysql> insert into t_time values ('2 10:10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_time;
+----------+
| col1     |
+----------+
| 10:10:10 |
| 00:11:22 |
| 58:10:00 |
+----------+
3 rows in set (0.00 sec)

2空格，代表 在原有的 10:10:00 加上2天，也就是48个小时的意思。

因为 加了：所以按着10左边来计算，第一个是就是小时计算。

mysql> insert into t_time values ('2 10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_time;
+----------+
| col1     |
+----------+
| 10:10:10 |
| 00:11:22 |
| 58:10:00 |
| 58:00:00 |
+----------+
4 rows in set (0.00 sec)

10前后都没有：被认为小时，2天加10 为58小时。

mysql> insert into t_time values ('10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_time;
+----------+
| col1     |
+----------+
| 10:10:10 |
| 00:11:22 |
| 58:10:00 |
| 58:00:00 |
| 00:00:10 |
+----------+
5 rows in set (0.00 sec)

没有：引号包括，最右计算，为秒。

DATE

创建表

mysql> create table t_date (
    -> col1 date
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t_date;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

正常操作

mysql> insert into t_date values ('2019-7-16');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_date;
+------------+
| col1       |
+------------+
| 2019-07-16 |
+------------+
1 row in set (0.00 sec)

DATETIME

一般情况不要使用datetime因为会占用8 bytes，而time + date 才占用6 bytes。

创建表

mysql> create table t_dt ( col1 datetime );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t_dt;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col1  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

正常操作

mysql> insert into t_dt values ('1999-10-1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_dt;
+---------------------+
| col1                |
+---------------------+
| 1999-10-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

datetime完整类型是 年月日 时分秒，如果省略了 时分秒，默认会补全。

完整格式

mysql> insert into t_dt values ('2012-10-1 10:10:10');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_dt;
+---------------------+
| col1                |
+---------------------+
| 1999-10-01 00:00:00 |
| 2012-10-01 10:10:10 |
+---------------------+
2 rows in set (0.00 sec)

简写格式

mysql> insert into t_dt values ('20-10-1 10:10:10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_dt;
+---------------------+
| col1                |
+---------------------+
| 1999-10-01 00:00:00 |
| 2012-10-01 10:10:10 |
| 2020-10-01 10:10:10 |
+---------------------+
3 rows in set (0.00 sec)

TIMESTAMP（时间戳）

创建表

mysql> create table t_tt ( col1 timestamp );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t_tt;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| col1  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

正常操作

mysql> insert into t_tt values (now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_tt;
+---------------------+
| col1                |
+---------------------+
| 2019-07-16 23:28:18 |
+---------------------+

now()受当前系统的影响。

可以修改时区看一下效果：

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

mysql> set time_zone='+10:00';

脏数据

mysql> insert into t_tt values ('88)');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t_tt;
+---------------------+
| col1                |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

插入一条非时间格式的数据，会显示 默认值 0000-00-00 00:00:00