本篇文章MySQL版本:
+-----------+
| version() |
+-----------+
| 5.5.53 |
+-----------+
目录
日期和时间类型
- 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