mysql timestamp 插入null报错_读MySQL 5.7文档11.2 Date and Time Data Types总结
作者：魏新平，知數(shù)堂第5期MySQL實(shí)戰(zhàn)班學(xué)員，第10期MySQL優(yōu)化班學(xué)員，現(xiàn)任職助教。
讀MySQL5.7文檔11.2 Date and Time Data Types
MySQL的時(shí)間類型分為DATE, DATETIME, TIMESTAMP, TIME, YEAR五個(gè)類型。接下來(lái)為大家一一介紹下。
DATE類型
存儲(chǔ)YYYY-MM-DD類型的時(shí)間，取值范圍是'1000-01-01' to '9999-12-31'。
DATETIME和TIMESTAMP類型
兩個(gè)類型都是用來(lái)存儲(chǔ)YYYY-MM-DD hh:mm:ss[.fraction]格式的時(shí)間。不過(guò)在取值范圍和存儲(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。可以看出來(lái)DATETIME的取值范圍大很多。
存儲(chǔ)方式
DATETIME是直接存儲(chǔ)插入的時(shí)間。而TIMESTAMP是將插入的值轉(zhuǎn)換為utc時(shí)間，然后存入數(shù)據(jù)庫(kù)，取出來(lái)的時(shí)候再根據(jù)當(dāng)前會(huì)話的時(shí)區(qū)進(jìn)行轉(zhuǎn)換顯示。
admin@localhost [weixinpingtest] 10:05:51>create table testtime(a timestamp,b datetime);
Query OK, 0 rows affected (0.05 sec)
admin@localhost [weixinpingtest] 10:07:20>insert into testtime() values(now(),now());
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinpingtest] 10:07:37>select * from testtime;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2020-03-04 10:07:37 | 2020-03-04 10:07:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:07:43>set timezone = '+9:00';#修改時(shí)區(qū)以后，下面顯示的時(shí)間就不一樣了
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinpingtest] 10:07:50>select * from testtime;
+---------------------+---------------------+
| 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í)間沒(méi)有變化。
自動(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í)，如果沒(méi)有指定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í)候值沒(méi)有變化，那么ts也不會(huì)變化。
DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP可以同時(shí)指定，或者只指定其中一個(gè)。兩者的先后順序不會(huì)有什么影響。
explicit_defaults_for_timestamp變量
針對(duì)timestamp還有一個(gè)數(shù)據(jù)庫(kù)變量。explicit_defaults_for_timestamp。 這個(gè)變量默認(rèn)是off的。那么off的時(shí)候會(huì)有什么現(xiàn)象呢。在創(chuàng)建表的時(shí)候，如果自動(dòng)初始化和自動(dòng)更新都沒(méi)有添加。那么第一個(gè)timestamp字段會(huì)自動(dòng)加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP。
admin@localhost [weixinpingtest] 10:07:52>show global variables like '%explicitdefaultsfortimestamp%';
+---------------------------------+-------+
| Variablename | Value |
+---------------------------------+-------+
| explicitdefaultsfortimestamp | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:15:14>create table testt3(a timestamp,b timestamp);
Query OK, 0 rows affected (0.02 sec)
admin@localhost [weixinpingtest] 10:15:44>show create table testt3;
+---------+------------------------------------------------------------------------------+
| Table | Create Table
+---------+------------------------------------------------------------------------------+
| testt3 | CREATE TABLE test_t3 (a timestamp NOT NULL DEFAULT CURRENTTIMESTAMP 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沒(méi)有指定允許null的情況下是不能插入null的，插入null的時(shí)候會(huì)默認(rèn)轉(zhuǎn)換成當(dāng)前時(shí)間
admin@localhost [weixinpingtest] 10:21:47>show create table testt4;
+---------+--------------------------------------------+
| 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 [weixinpingtest] 10:21:54>insert into testt4() values(null,null);
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinpingtest] 10:22:03>select * from testt4;
+------+---------------------+
| a | b |
+------+---------------------+
| NULL | 2020-03-05 10:22:03 |
+------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:22:12>set explicitdefaultsfortimestamp=on;
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinpingtest] 10:25:11>insert into testt4() values(null,null);
ERROR 1048 (23000): Column 'b' cannot be null
test_t4表當(dāng)中a字段可以為null，b字段默認(rèn)，不能為null，但是插入null值都沒(méi)有報(bào)錯(cuò)，只不過(guò)a字段變成了null，b字段變成了當(dāng)前時(shí)間。后面我開(kāi)啟了explicit_defaults_for_timestamp,再次插入null，就報(bào)錯(cuò)，說(shuō)b不能為null了。
至于要不要開(kāi)啟這個(gè)變量就看你自己了，反正是可以在線關(guān)閉和開(kāi)啟的。
秒后面的小數(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 [weixinpingtest] 10:34:53>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(6) );
Query OK, 0 rows affected (0.01 sec)
admin@localhost [weixinpingtest] 10:35:29>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(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有一句提示語(yǔ)。For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.表示雖然支持那么大的時(shí)間范圍，但是我不保證這個(gè)能行。
喜歡文章的話就點(diǎn)個(gè)關(guān)注吧！
有問(wèn)題聯(lián)系我，微信：lvqingshan_
與50位技術(shù)專家面對(duì)面20年技術(shù)見(jiàn)證，附贈(zèng)技術(shù)全景圖總結(jié)
以上是生活随笔為你收集整理的mysql timestamp 插入null报错_读MySQL 5.7文档11.2 Date and Time Data Types总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： mysql php apache源码_配
- 下一篇： mysql1215_这15条MySQL改