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-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-21 21: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-21 21:42:31 | 2020-04-21 21: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-21 21:42:31 | 2020-04-21 21:42:31 | | 2020-04-22 21:45:10 | 2020-04-21 21: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-22 21:45:10.321123 | 2020-04-22 21: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-23 23: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-21 21:52:14 | 2020-04-23 23: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-21 22:03:29 | +---------------------+ 1 row in set (0.01 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-04-21 22: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-21 22:05:11 | 2020-04-21 22: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-21 22:05:11 | 2020-04-21 22:05:11 | a | | 2 | 2020-04-21 22:05:43 | 2020-04-21 22: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-21 22:06:05 | 2020-04-21 22:06:05 | abc | | 2 | 2020-04-21 22:06:05 | 2020-04-21 22: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-22 22: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-22 22: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)
