mysql8学习笔记23--MySQL数据库设计--数据类型之日期时间类型

数据类型之日期时间类型

• 日期时间类型包括date,time,datetime,timestamp和year，用来指定不同范围的日期或时间值

• Date类型用来表示仅日期，MySQL默认的日期格式为yyyy-mm-dd，取值范围为1000-01-01到9999-12-31

• Datetime类型用来表示日期和时间，MySQL默认的格式为yyyy-mm-dd hh:mi:ss，取值范围为1000-01-01 00:00:00到9999-12-31 23:59:59

• Timestamp类型也用来表示日期和时间，其取值范围为1970-01-01 00:00:01到2038-01-19 03:14:07

• Datetime和timestamp两个类型都可以保存到微妙级别，即6位毫秒微妙精度，即1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999和1970-01-01

00:00:01.000000到2038-01-19 03:14:07.999999• 非法的date,datetime,timestamp值将被转换成0值，0000-00-00或者0000-00-0000:00:00

mysql> create table temp4(t1 Datetime,t2 Timestamp);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into temp4(t1,t2) values(now(),now());
Query OK, 1 row affected (0.09 sec)

mysql> select t1,t2 from temp4;
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2021-05-06 00:21:19 | 2021-05-06 00:21:19 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into temp4(t1,t2) values('2021-05-06 23:21:19.1234567','2021-05-06 23:21:19.1234567');
Query OK, 1 row affected (0.04 sec)

mysql> select t1,t2 from temp4;#秒后面的位数被截断了。
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2021-05-06 00:21:19 | 2021-05-06 00:21:19 |
| 2021-05-06 23:21:19 | 2021-05-06 23:21:19 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> alter table temp4 modify t1 Datetime(6),t2 Timestamp(3);#改下字段，分别精确到6位和3位
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't2 Timestamp(3)' at line 1
mysql> alter table temp4 modify t1 Datetime(6);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table temp4 modify t2 Timestamp(3);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into temp4(t1,t2) values('2021-05-06 23:21:19.1234567','2021-05-06 23:21:19.1234567');
Query OK, 1 row affected (0.01 sec)

mysql> select t1,t2 from temp4;#超出6位的部分，会进行四舍五入。
+----------------------------+-------------------------+
| t1                         | t2                      |
+----------------------------+-------------------------+
| 2021-05-06 00:21:19.000000 | 2021-05-06 00:21:19.000 |
| 2021-05-06 23:21:19.000000 | 2021-05-06 23:21:19.000 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
+----------------------------+-------------------------+
3 rows in set (0.00 sec)

mysql> select t1,t2 from temp4;
+----------------------------+-------------------------+
| t1                         | t2                      |
+----------------------------+-------------------------+
| 2021-05-06 00:21:19.000000 | 2021-05-06 00:21:19.000 |
| 2021-05-06 23:21:19.000000 | 2021-05-06 23:21:19.000 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
+----------------------------+-------------------------+
3 rows in set (0.00 sec)

mysql> insert into temp4(t1,t2) values('2021-05-06 23:21:19.1234567','2021-05-06 23:21:19.1234567');
Query OK, 1 row affected (0.04 sec)

mysql> select t1,t2 from temp4;
+----------------------------+-------------------------+
| t1                         | t2                      |
+----------------------------+-------------------------+
| 2021-05-06 00:21:19.000000 | 2021-05-06 00:21:19.000 |
| 2021-05-06 23:21:19.000000 | 2021-05-06 23:21:19.000 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
+----------------------------+-------------------------+
4 rows in set (0.00 sec)

mysql> insert into temp4(t1,t2) values('2021-05-06 23:21:19.123456','2021-05-06 23:21:19.123456');
Query OK, 1 row affected (0.09 sec)

mysql> select t1,t2 from temp4;
+----------------------------+-------------------------+
| t1                         | t2                      |
+----------------------------+-------------------------+
| 2021-05-06 00:21:19.000000 | 2021-05-06 00:21:19.000 |
| 2021-05-06 23:21:19.000000 | 2021-05-06 23:21:19.000 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
| 2021-05-06 23:21:19.123457 | 2021-05-06 23:21:19.123 |
| 2021-05-06 23:21:19.123456 | 2021-05-06 23:21:19.123 |
+----------------------------+-------------------------+
5 rows in set (0.00 sec)

mysql>

• Time类型用来仅表示时间，MySQL默认格式为HH:MM:SS，其取值范围为-838:59:59到838:59:59，小时字段可以超过24是因为time类型不光代表小时，也可以代表持续时长中的小时

• Time类型也可以包含6位的毫秒微秒精度，其取值范围为-838:59:59.000000到838:59:59.000000

mysql> select now(),date_add(now(),interval 50 hour);
+---------------------+----------------------------------+
| now()               | date_add(now(),interval 50 hour) |
+---------------------+----------------------------------+
| 2021-05-06 01:01:11 | 2021-05-08 03:01:11              |
+---------------------+----------------------------------+
1 row in set (0.00 sec)

• Year类型用来仅表示年份，MySQL默认格式为YYYY，其取值范围为1901到2155，和0000

• 针对非法的year数据，则直接转化为0000

• Timestamp和datetime日期时间类型可以被自动初始化和更新为当前的日期时间数据，当你默认指定current timestamp为默认值，或者指定此数据列为自动更新时

• 指定默认值是指当插入新的数据而该列没有显视指定数值时，则插入当前日期时间值

• 指定自动更新是指当行中的其他列被更新时，则此列被自动更新为当前日期时间值

• CREATE TABLE t1 (

• ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

• dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• );

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

dt DATETIME DEFAULT CURRENT_TIMESTAMP

);

 

mysql> desc t1;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| dt    | datetime  | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| id    | int(11)   | YES  |     | NULL              |                                               |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.00 sec)


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

mysql> select * from t1;#因为Default 设置了CURRENT_TIMESTAMP，所以插入新记录时，虽然ts和dt两个字段没设置值，系统会插入当前时间。
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2021-05-06 23:43:48 | 2021-05-06 23:43:48 |    1 |
+---------------------+---------------------+------+
1 row in set (0.00 sec)

mysql> insert into t1(id) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2021-05-06 23:43:48 | 2021-05-06 23:43:48 |    1 |
| 2021-05-06 23:44:09 | 2021-05-06 23:44:09 |    2 |
+---------------------+---------------------+------+
2 rows in set (0.00 sec)

mysql> update t1 set id = 3 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;#当设置了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，则更新记录时，对应字段也会自动更新。
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2021-05-06 23:43:48 | 2021-05-06 23:43:48 |    1 |
| 2021-05-06 23:44:32 | 2021-05-06 23:44:32 |    3 |
+---------------------+---------------------+------+
2 rows in set (0.00 sec)

mysql>

 

 

 

mysql> select * from temp5;
Empty set (0.00 sec)

mysql> desc temp5;
+-------+----------+------+-----+--------------------------+-------------------+
| Field | Type     | Null | Key | Default                  | Extra             |
+-------+----------+------+-----+--------------------------+-------------------+
| t1    | datetime | YES  |     | (now() + interval 1 day) | DEFAULT_GENERATED |
+-------+----------+------+-----+--------------------------+-------------------+
1 row in set (0.00 sec)

mysql> show create table temp5;#可以指定默认值加时间间隔。
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| temp5 | CREATE TABLE `temp5` (
  `t1` datetime DEFAULT ((now() + interval 1 day))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into temp5(t1) values(now());
Query OK, 1 row affected (0.37 sec)

mysql> select * from temp5;
+---------------------+
| t1                  |
+---------------------+
| 2021-05-07 13:23:38 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-07 13:23:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> alter table temp5 add t2 int;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into temp5(t2) values(3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from temp5;
+---------------------+------+
| t1                  | t2   |
+---------------------+------+
| 2021-05-07 13:23:38 | NULL |
| 2021-05-08 13:24:06 |    3 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql> 

 两种创建方法：

mysql> create table temp6(t1 datetime default(now()+interval 1 day),t2 datetime default(date_add(now(),interval 1 day)));
Query OK, 0 rows affected (0.30 sec)

mysql> show create table temp6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| temp6 | CREATE TABLE `temp6` (
  `t1` datetime DEFAULT ((now() + interval 1 day)),
  `t2` datetime DEFAULT ((now() + interval 1 day))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>