mysql的時(shí)間類型分為DATE, DATETIME, TIMESTAMP, TIME, YEAR五個(gè)類型。接下來為大家一一介紹下。
DATE類型
存儲(chǔ)YYYY-MM-DD類型的時(shí)間，取值范圍是'1000-01-01' to '9999-12-31'。
DATETIME和TIMESTAMP類型
兩個(gè)類型都是用來存儲(chǔ)YYYY-MM-DD hh:mm:ss[.fraction]格式的時(shí)間。不過在取值范圍和存儲(chǔ)方式上有些不同。
取值范圍
DATETIME的取值范圍是'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59', TIMESTAMP的取值范圍是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC?？梢钥闯鰜鞤ATETIME的取值范圍大很多。
存儲(chǔ)方式
DATETIME是直接存儲(chǔ)插入的時(shí)間。而TIMESTAMP是將插入的值轉(zhuǎn)換為utc時(shí)間，然后存入數(shù)據(jù)庫，取出來的時(shí)候再根據(jù)當(dāng)前會(huì)話的時(shí)區(qū)進(jìn)行轉(zhuǎn)換顯示。
admin@localhost [weixinping_test] 10:05:51>create table test_time(a timestamp,b datetime);
Query OK, 0 rows affected (0.05 sec)
admin@localhost [weixinping_test] 10:07:20>insert into test_time() values(now(),now());
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinping_test] 10:07:37>select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2020-03-04 10:07:37 | 2020-03-04 10:07:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinping_test] 10:07:43>set time_zone = '+9:00';#修改時(shí)區(qū)以后，下面顯示的時(shí)間就不一樣了
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinping_test] 10:07:50>select * from test_time;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2020-03-04 11:07:37 | 2020-03-04 10:07:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
可以看到只要修改時(shí)區(qū)，第二次查詢的時(shí)候，timestamp類型的字段時(shí)間多了一個(gè)小時(shí)，而datetime類型的字段時(shí)間沒有變化。
自動(dòng)初始化和更新
雖然有點(diǎn)廢話，但是還是要介紹一下這兩個(gè)功能到底是什么作用。
自動(dòng)初始化的關(guān)鍵字是DEFAULT CURRENT_TIMESTAMP。
CREATE TABLE t1 (
a int,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
)
當(dāng)往表t1中插入數(shù)據(jù)時(shí)，如果沒有指定ts的值，那么ts會(huì)被設(shè)置為當(dāng)前時(shí)間。
自動(dòng)更新的關(guān)鍵字是ON UPDATE CURRENT_TIMESTAMP
CREATE TABLE t1 (
a int,
ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
當(dāng)表t1的a字段更新時(shí)，ts會(huì)自動(dòng)更新到修改的時(shí)間。但是如果a修改的時(shí)候值沒有變化，那么ts也不會(huì)變化。
DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP可以同時(shí)指定，或者只指定其中一個(gè)。兩者的先后順序不會(huì)有什么影響。
explicit_defaults_for_timestamp變量
針對(duì)timestamp還有一個(gè)數(shù)據(jù)庫變量。explicit_defaults_for_timestamp。
這個(gè)變量默認(rèn)是off的。那么off的時(shí)候會(huì)有什么現(xiàn)象呢。
在創(chuàng)建表的時(shí)候，如果自動(dòng)初始化和自動(dòng)更新都沒有添加。那么第一個(gè)timestamp字段會(huì)自動(dòng)加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP。
admin@localhost [weixinping_test] 10:07:52>show global variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
admin@localhost [weixinping_test] 10:15:14>create table test_t3(a timestamp,b timestamp);
Query OK, 0 rows affected (0.02 sec)
admin@localhost [weixinping_test] 10:15:44>show create table test_t3;
+---------+------------------------------------------------------------------------------+
| Table | Create Table
+---------+------------------------------------------------------------------------------+
| test_t3 | CREATE TABLE `test_t3` (
`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
1 row in set (0.00 sec)
有兩種方法可以阻止這種現(xiàn)象的發(fā)生，第一個(gè)是直接設(shè)定explicit_defaults_for_timestamp參數(shù)為on,第二個(gè)是給字段設(shè)置默認(rèn)值，或者直接設(shè)置可以為可以為null。
timestamp沒有指定允許null的情況下是不能插入null的，插入null的時(shí)候會(huì)默認(rèn)轉(zhuǎn)換成當(dāng)前時(shí)間
admin@localhost [weixinping_test] 10:21:47>show create table test_t4;
+---------+--------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------+
| test_t4 | CREATE TABLE `test_t4` (
`a` timestamp NULL DEFAULT NULL,
`b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------+
1 row in set (0.00 sec)
admin@localhost [weixinping_test] 10:21:54>insert into test_t4() values(null,null);
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinping_test] 10:22:03>select * from test_t4;
+------+---------------------+
| a | b |
+------+---------------------+
| NULL | 2020-03-05 10:22:03 |
+------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinping_test] 10:22:12>set explicit_defaults_for_timestamp=on;
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinping_test] 10:25:11>insert into test_t4() values(null,null);
ERROR 1048 (23000): Column 'b' cannot be null
test_t4表當(dāng)中a字段可以為null，b字段默認(rèn)，不能為null，但是插入null值都沒有報(bào)錯(cuò)，只不過a字段變成了null，b字段變成了當(dāng)前時(shí)間。后面我開啟了explicit_defaults_for_timestamp,再次插入null，就報(bào)錯(cuò)，說b不能為null了。
至于要不要開啟這個(gè)變量就看你自己了，反正是可以在線關(guān)閉和開啟的。
秒后面的小數(shù)
DATETIME和TIMESTAMP都是可以在定義字段類型時(shí)加上(n),比如DATETIME(3)，這里的3表示秒后面的小數(shù)可以精確到幾位。n的取值范圍是0-6。如果不設(shè)定，默認(rèn)是0。需要注意的是,當(dāng)指定了初始化和自動(dòng)更新時(shí)，后面的也要加上這個(gè)數(shù)字。而且必須保持一致。如下
admin@localhost [weixinping_test] 10:34:53>CREATE TABLE test_t5( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) );
Query OK, 0 rows affected (0.01 sec)
admin@localhost [weixinping_test] 10:35:29>CREATE TABLE test_t5( ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(3) );
ERROR 1294 (HY000): Invalid ON UPDATE clause for 'ts' column
如果不一致就會(huì)報(bào)錯(cuò)。
TIME類型
存儲(chǔ)hh:mm:ss類型的數(shù)據(jù)，取值范圍是'-838:59:59.000000' to '838:59:59.000000'。范圍可以取這么大的原因是time還可以表示時(shí)間差，所以范圍會(huì)大于24個(gè)小時(shí)，甚至是負(fù)數(shù)。TIME也是支持秒的小數(shù)的。
YEAR類型
存儲(chǔ)年份的。取值范圍是1901 - 2155 和 0000。
tips
針對(duì)DATE和DATETIME，表示的范圍非常大，但是mysql有一句提示語。For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.表示雖然支持那么大的時(shí)間范圍，但是我不保證這個(gè)能行。
