MySQL 5.7中explicit_defaults_for_timestamp參數(shù)

在MySQL 5.7版本之前，且在MySQL 5.6.6版本之后（explicit_defaults_for_timestamp參數(shù)在MySQL 5.6.6開始加入）的版本中，如果沒有設置explicit_defaults_for_timestamp=1的情況下：

1）在默認情況下，如果TIMESTAMP列沒有顯示的指明null屬性，那么該列會被自動加上not null屬性(而其他類型的列如果沒有被顯示的指定not null，那么是允許null值的)，如果往這個列中插入null值，會自動的設置該列的值為current timestamp值。

2）表中的第一個TIMESTAMP列，如果沒有指定null屬性或者沒有指定默認值，也沒有指定ON UPDATE語句。那么該列會自動被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性。

3）第一個TIMESTAMP列之后的其他的TIMESTAMP類型的列，如果沒有指定null屬性，也沒有指定默認值，那么該列會被自動加上DEFAULT ‘0000-00-00 00:00:00’屬性。如果insert語句中沒有為該列指定值，那么該列中插入’0000-00-00 00:00:00’，并且沒有warning。

在MySQL 5.6.6及以后的版本和MySQL 5.7之前的版本中，如果在配置文件中沒有指定explicit_defaults_for_timestamp參數(shù)，啟動時error日志中會報如下警告：

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use --explicit_defaults_for_timestamp server option (see

documentation for more details).

如果我們在啟動的時候在配置文件中指定了explicit_defaults_for_timestamp=1，MySQL會按照如下的方式處理TIMESTAMP列：

1）此時如果TIMESTAMP列沒有顯示的指定not null屬性，那么默認的該列可以為null，此時向該列中插入null值時，會直接記錄null，而不是current timestamp。

2）不會自動的為表中的第一個TIMESTAMP列加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性，除非你在建表的時候顯示的指明。

3）如果TIMESTAMP列被加上了not null屬性，并且沒有指定默認值。這時如果向表中插入記錄，但是沒有給該TIMESTAMP列指定值的時候，如果strict ?sql_mode被指定了，那么會直接報錯。如果strict sql_mode沒有被指定，那么會向該列中插入’0000-00-00 00:00:00’并且產生一個warning。

這里為什么一直強調版本呢？主要還是因為這個參數(shù)explicit_defaults_for_timestamp在MySQL 5.6.6開始加入，并且MySQL 5.6跟MySQL 5.7的默認SQL模式不同了。MySQL 5.7的SQL模式更加嚴格了，限制了不合法的日期輸入，比如”0000-00-00 00:00:00″。詳情可以看：MySQL 5.7默認SQL模式帶來的問題總結。

一、啟動mysql時未設置explicit_defaults_for_timestamp=1

1）創(chuàng)建測試表test_time

mysql> set session sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table test_time(time1 timestamp,time2 timestamp,id int);

Query OK, 0 rows affected (0.01 sec)

這里關閉了SQL模式，是因為MySQL 5.7默認SQL模式加入了NO_ZERO_DATE和NO_ZERO_IN_DATE模式，這兩個模式的意思如下：

NO_ZERO_DATE

在嚴格模式，不要將’0000-00-00’做為合法日期。你仍然可以用IGNORE選項插入零日期。在非嚴格模式，可以接受該日期，但會生成警告。

NO_ZERO_IN_DATE

在嚴格模式，不接受月或日部分為0的日期（也就是說比NO_ZERO_DATE），對年不限制。如果使用IGNORE選項，我們?yōu)轭愃频娜掌诓迦搿?000-00-00’。在非嚴格模式，可以接受該日期，但會生成警告。

所以如果不去掉這兩個SQL模式，那么根據我們上面所說的表中的第一個TIMESTAMP列，如果沒有指定null屬性或者沒有指定默認值，也沒有指定ON UPDATE語句。那么該列會自動被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性。第一個TIMESTAMP列之后的其他的TIMESTAMP類型的列，如果沒有指定null屬性，也沒有指定默認值，那么該列會被自動加上DEFAULT ‘0000-00-00 00:00:00’屬性。所以第二個timestamp添加默認值時就會報錯的，錯誤如下：

mysql> create table test_time1(time1 timestamp,time2 timestamp,id int);

ERROR 1067 (42000): Invalid default value for 'time2'

表創(chuàng)建好了之后，下面來查看表結構信息，如下：

mysql> show create table test_time\G

*************************** 1. row ***************************

?????? Table: test_time

Create Table: CREATE TABLE `test_time` (

??`time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

??`time2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

??`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

從表結構中可以看到表中timestamp列被自動設置為not null，并且表中第一個timestamp列被設置了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP默認值，第二個字段默認值為”0000-00-00 00:00:00″。

2）插入測試

mysql> insert into test_time select null,null,1;

Query OK, 1 row affected (0.00 sec)

Records: 1??Duplicates: 0??Warnings: 0


mysql> select * from test_time;

+---------------------+---------------------+------+

| time1?????????????? | time2?????????????? | id?? |

+---------------------+---------------------+------+

| 2017-02-22 15:42:11 | 2017-02-22 15:42:11 |????1 |

+---------------------+---------------------+------+

1 row in set (0.00 sec)

往timestamp列插入null值時，會自動為該列設置為current?time。

插入時未指定值的timestamp列中被插入了0000-00-00?00:00:00（非表中第一個timestamp列）。

mysql> select * from test_time;

+---------------------+---------------------+------+

| time1?????????????? | time2?????????????? | id?? |

+---------------------+---------------------+------+

| 2017-02-22 15:42:11 | 2017-02-22 15:42:11 |????1 |

| 2017-02-22 15:45:09 | 0000-00-00 00:00:00 |????1 |

+---------------------+---------------------+------+

2 rows in set (0.00 sec)

二、啟動mysql時設置explicit_defaults_for_timestamp=1

1）創(chuàng)建表（不需要改變sql mode）

mysql> create table test_time(time1 timestamp,time2 timestamp,id int);

Query OK, 0 rows affected (0.00 sec)


mysql> show create table test_time\G

*************************** 1. row ***************************

?????? Table: test_time

Create Table: CREATE TABLE `test_time1` (

??`time1` timestamp NULL DEFAULT NULL,

??`time2` timestamp NULL DEFAULT NULL,

??`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

通過表結構我們看到，2個timestamp列都被設置為null，并且設置了默認值為null。

2）插入測試

mysql> insert into test_time select null,1;

Query OK, 1 row affected (0.00 sec)

Records: 1??Duplicates: 0??Warnings: 0


mysql> select * from test_time;

+-------+-------+------+

| time1 | time2 | id?? |

+-------+-------+------+

| NULL??| NULL??|????1 |

+-------+-------+------+

1 row in set (0.00 sec)

為timestamp列指定了not?null屬性，在strict SQL mode時，如果插入時該列沒有指定值，會直接報錯。

mysql> create table test_time(time1 timestamp,time2 timestamp not null,id int);

Query OK, 0 rows affected (0.01 sec)


mysql> insert into test_time select null,null,1;

ERROR 1048 (23000): Column 'time2' cannot be null

如果為timestamp列指定not?null屬性，在非stric?sql_mode模式下，如果插入的時候該列沒有指定值，那么會向該列中插入0000-00-00?00:00:00，并且產生告警。

mysql> set session sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> insert into test_time select null,null,1;

Query OK, 1 row affected, 1 warning (0.00 sec)

Records: 1??Duplicates: 0??Warnings: 1


mysql> show warnings;

+---------+------+-------------------------------+

| Level?? | Code | Message?????????????????????? |

+---------+------+-------------------------------+

| Warning | 1048 | Column 'time2' cannot be null |

+---------+------+-------------------------------+

1 row in set (0.00 sec)


mysql> select * from test_time;

+-------+---------------------+------+

| time1 | time2?????????????? | id?? |

+-------+---------------------+------+

| NULL??| 0000-00-00 00:00:00 |????1 |

+-------+---------------------+------+

1 row in set (0.00 sec)

上面說的都是關于timestamp類型的，如果是datetime呢？

mysql> create table time1(id int,time1 datetime,time2 datetime);

Query OK, 0 rows affected (0.03 sec)


mysql> show create table time1\G

*************************** 1. row ***************************

?????? Table: time1

Create Table: CREATE TABLE `time1` (

??`id` int(11) DEFAULT NULL,

??`time1` datetime DEFAULT NULL,

??`time2` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)


mysql> insert into time1(id) values(1);

Query OK, 1 row affected (0.00 sec)


mysql> select * from time1;

+------+-------+-------+

| id?? | time1 | time2 |

+------+-------+-------+

|????1 | NULL??| NULL??|

+------+-------+-------+

1 row in set (0.00 sec)

可以看到跟普通類型一樣。


轉自：http://www.ywnds.com/?p=8309

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

  • 什么是數(shù)據庫？ 數(shù)據庫是存儲數(shù)據的集合的單獨的應用程序。每個數(shù)據庫具有一個或多個不同的API，用于創(chuàng)建，訪問，管理...
    chen_000閱讀 4,124評論 0贊 19
  • 【MySQL】Linux下MySQL 5.5、5.6和5.7的RPM、二進制和源碼安裝 1.1BLOG文檔結構圖 ...
    小麥苗DB寶閱讀 10,862評論 0贊 31
  • MySQL5.6從零開始學 第一章 初始mysql 1.1數(shù)據庫基礎 數(shù)據庫是由一批數(shù)據構成的有序的集合，這些數(shù)據...
    星期四晚八點閱讀 1,228評論 0贊 4
  • 用了mysql好多年，很少關注mysql自帶庫，自然也不知道這個庫里存放些什么，在看《MySQL排錯指南》時看到它...
    灼灼2015閱讀 1,980評論 1贊 3
  • 充滿了負能量 對大學的輕視，導致期中考的慘敗，導致這段時間的低迷 但是啊，這樣負能量能帶來什么呢？還不是內心的堵，...
    米蘭Milan閱讀 122評論 0贊 1

友情鏈接更多精彩內容