MySQL数据类型之⽇期时间类型
• ⽇期时间类型包括date,time,datetime,timestamp和year，⽤来指定不同范围的⽇期或时间值
• Date类型⽤来表⽰仅⽇期，MySQL默认的⽇期格式为yyyy-mm-dd，取值范围为1000-01-01到9999-12-31
tabletime• 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-00 00:00:00
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-04-21 |
+----------------+
1 row in set (0.00 sec)
mysql> select current_datetime();
ERROR 1305 (42000): FUNCTION course.current_datetime does not exist
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-04-2121:40:48 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table temp5(tstamp datetime,tstamp2 timestamp);
Query OK, 0 rows affected (0.17 sec)
mysql> insert into temp5 values(now(),now());
Query OK, 1 row affected (0.04 sec)
mysql> select * from temp5;
+---------------------+---------------------+
| tstamp | tstamp2 |
+---------------------+---------------------+
| 2020-04-2121:42:31 | 2020-04-2121:42:31 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> insert into temp5 values('2020-04-22 21:45:10.321123',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from temp5;
+---------------------+---------------------+
| tstamp | tstamp2 |
+---------------------+---------------------+
| 2020-04-2121:42:31 | 2020-04-2121:42:31 |
| 2020-04-2221:45:10 | 2020-04-2121:45:13 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> drop table temp5;
Query OK, 0 rows affected (0.08 sec)
mysql> create table temp5(tstamp datetime(6),tstamp2 timestamp(3));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into temp5 values('2020-04-22 21:45:10.321123','2020-04-22 21:45:10.321123');
Query OK, 1 row affected (0.09 sec)
mysql> select * from temp5;
+----------------------------+-------------------------+
| tstamp | tstamp2 |
+----------------------------+-------------------------+
| 2020-04-2221:45:10.321123 | 2020-04-2221:45:10.321 |
+----------------------------+-------------------------+
1 row in set (0.00 sec)
• Time类型⽤来仅表⽰时间，MySQL默认格式为HH:MM:SS，其取值范围为-838:59:59到838:59:59，⼩时字段可以超过24是因为time类型不光代表⼩时，也可以代表持续时长中的⼩时
• Time类型也可以包含6位的毫秒微秒精度，其取值范围为-838:59:59.000000到838:59:59.000000
• Year类型⽤来仅表⽰年份，MySQL默认格式为YYYY，其取值范围为1901到2155，和0000
• 针对⾮法的year数据，则直接转化为0000
mysql> select date_add(now(),interval 50 hour);
+----------------------------------+
| date_add(now(),interval 50 hour) |
+----------------------------------+
| 2020-04-2323:52:01 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select now(),date_add(now(),interval 50 hour);
+---------------------+----------------------------------+
| now() | date_add(now(),interval 50 hour) |
+---------------------+----------------------------------+
| 2020-04-2121:52:14 | 2020-04-2323:52:14 |
+---------------------+----------------------------------+
1 row in set (0.00 sec)
• Timestamp和datetime⽇期时间类型可以被⾃动初始化和更新为当前的⽇期时间数据，当你默认指定current timestamp为默认值，或者指定此数据列为⾃动更新时
• 指定默认值是指当插⼊新的数据⽽该列没有显视指定数值时，则插⼊当前⽇期时间值
• 指定⾃动更新是指当⾏中的其他列被更新时，则此列被⾃动更新为当前⽇期时间值
mysql> drop table temp5;
Query OK, 0 rows affected (0.10 sec)
mysql> create table temp5(id int,tstamp datetime default current_timestamp on update current_timestamp,tstamp2 timestamp default current_timestamp on update current_timestamp); Query OK, 0 rows affected (0.07 sec)
mysql> alter table temp5 add name varchar(64);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc temp5;
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | YES | | NULL | |
| tstamp | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| tstamp2 | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| name | varchar(64) | YES | | NULL | |
+---------+-------------+------+-----+-------------------+-----------------------------------------------+
4 rows in set (0.01 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2020-04-2122:03:29 |
+---------------------+
1 row in set (0.01 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-04-2122:03:35 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from temp5;
Empty set (0.00 sec)
mysql> insert into temp5(id,name) values(1,'a');
Query OK, 1 row affected (0.04 sec)
mysql> select * from temp5;
+------+---------------------+---------------------+------+
| id | tstamp | tstamp2 | name |
+------+---------------------+---------------------+------+
| 1 | 2020-04-2122:05:11 | 2020-04-2122:05:11 | a |
+------+---------------------+---------------------+------+
1 row in set (0.00 sec)
mysql> insert into temp5(id,name) values(2,'b');
Query OK, 1 row affected (0.05 sec)
mysql> select * from temp5;
+------+---------------------+---------------------+------+
| id | tstamp | tstamp2 | name |
+------+---------------------+---------------------+------+
| 1 | 2020-04-2122:05:11 | 2020-04-2122:05:11 | a |
| 2 | 2020-04-2122:05:43 | 2020-04-2122:05:43 | b |
+------+---------------------+---------------------+------+
2 rows in set (0.00 sec)
mysql> update temp5 set name='abc';
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from temp5;
+------+---------------------+---------------------+------+
| id | tstamp | tstamp2 | name |
+------+---------------------+---------------------+------+
| 1 | 2020-04-2122:06:05 | 2020-04-2122:06:05 | abc |
| 2 | 2020-04-2122:06:05 | 2020-04-2122:06:05 | abc |
+------+---------------------+---------------------+------+
2 rows in set (0.00 sec)
mysql> drop table temp5;
Query OK, 0 rows affected (0.14 sec)
mysql> create table temp5(id int,tstamp datetime default now());
Query OK, 0 rows affected (0.09 sec)
mysql> desc temp5;
+--------+----------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+-------------------+-------------------+
| id | int(11) | YES | | NULL | |
| tstamp | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------+----------+------+-----+-------------------+-------------------+
2 rows in set (0.00 sec)
mysql> drop table temp5;
Query OK, 0 rows affected (0.13 sec)
mysql> create table temp5(id int,tstamp datetime default date_add(now(),interval 1 day)); # 5.7版本及以前
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 'date_add(now(),interval 1 day))' at line 1 mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2020-04-2222:11:52 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> create table temp5(id int,tstamp datetime default (date_add(now(),interval 1 day))); # 8.0版本
Query OK, 0 rows affected (0.16 sec)
mysql> desc temp5;
+--------+----------+------+-----+--------------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+--------------------------+-------------------+
| id | int(11) | YES | | NULL | |
| tstamp | datetime | YES | | (now() + interval 1 day) | DEFAULT_GENERATED |
+--------+----------+------+-----+--------------------------+-------------------+
2 rows in set (0.00 sec)
mysql> insert into temp5(id) values(1);
Query OK, 1 row affected (0.06 sec)
mysql> select * from temp5;
+------+---------------------+
| id | tstamp |
+------+---------------------+
| 1 | 2020-04-2222:13:27 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> drop table temp;
Query OK, 0 rows affected (0.04 sec)
mysql> create table temp(id int,id2 int default (1+1));
Query OK, 0 rows affected (0.13 sec)
mysql> desc temp;
+-------+---------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------------------+
| id | int(11) | YES | | NULL | |
| id2 | int(11) | YES | | (1 + 1) | DEFAULT_GENERATED |
+-------+---------+------+-----+---------+-------------------+
2 rows in set (0.01 sec)
mysql> insert into temp(id) values(10);
Query OK, 1 row affected (0.08 sec)
mysql> select * from temp;
+------+------+
| id | id2 |
+------+------+
| 10 | 2 |
+------+------+
1 row in set (0.00 sec)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论