数据类型之日期时间类型
• 日期时间类型包括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>