MySQL 5.6 中的 TIMESTAMP 和 explicit_defaults_for_timestamp 参数
安装MySQL时，有warning:
[root@localhostmysql]#scripts/mysql_install_db--user=mysql InstallingMySQLsystemtables...2015-08-1314:20:090[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2015-08-1314:20:090[Note]./bin/mysqld(mysqld5.6.26)startingasprocess1934... 2015-08-1314:20:091934[Note]InnoDB:Usingmutexestorefcountbufferpoolpages 2015-08-1314:20:091934[Note]InnoDB:TheInnoDBmemoryheapisdisabled 2015-08-1314:20:091934[Note]InnoDB:Mutexesandrw_locksuseInnoDB'sownimplementation 2015-08-1314:20:091934[Note]InnoDB:Memorybarrierisnotused 2015-08-1314:20:091934[Note]InnoDB:Compressedtablesusezlib1.2.3 2015-08-1314:20:091934[Note]InnoDB:UsingLinuxnativeAIO 2015-08-1314:20:091934[Note]InnoDB:NotusingCPUcrc32instructions 2015-08-1314:20:091934[Note]InnoDB:Initializingbufferpool,size=128.0M 2015-08-1314:20:091934[Note]InnoDB:Completedinitializationofbufferpool 2015-08-1314:20:101934[Note]InnoDB:Thefirstspecifieddatafile./ibdata1didnotexist:anewdatabasetobecreated! 2015-08-1314:20:101934[Note]InnoDB:Settingfile./ibdata1sizeto12MB 2015-08-1314:20:101934[Note]InnoDB:Databasephysicallywritesthefilefull:wait... 2015-08-1314:20:101934[Note]InnoDB:Settinglogfile./ib_logfile101sizeto48MB 2015-08-1314:20:111934[Note]InnoDB:Settinglogfile./ib_logfile1sizeto48MB 2015-08-1314:20:121934[Note]InnoDB:Renaminglogfile./ib_logfile101to./ib_logfile0 2015-08-1314:20:121934[Warning]InnoDB:Newlogfilescreated,LSN=45781 2015-08-1314:20:121934[Note]InnoDB:Doublewritebuffernotfound:creatingnew 2015-08-1314:20:121934[Note]InnoDB:Doublewritebuffercreated 2015-08-1314:20:121934[Note]InnoDB:128rollbacksegment(s)areactive. 2015-08-1314:20:121934[Warning]InnoDB:Creatingforeignkeyconstraintsystemtables. 2015-08-1314:20:121934[Note]InnoDB:Foreignkeyconstraintsystemtablescreated 2015-08-1314:20:121934[Note]InnoDB:Creatingtablespaceanddatafilesystemtables. 2015-08-1314:20:121934[Note]InnoDB:Tablespaceanddatafilesystemtablescreated. 2015-08-1314:20:121934[Note]InnoDB:Waitingforpurgetostart 2015-08-1314:20:121934[Note]InnoDB:5.6.26started;logsequencenumber0 2015-08-1314:20:141934[Note]Binlogend 2015-08-1314:20:141934[Note]InnoDB:FTSoptimizethreadexiting. 2015-08-1314:20:141934[Note]InnoDB:Startingshutdown... 2015-08-1314:20:161934[Note]InnoDB:Shutdowncompleted;logsequencenumber1625977
OK
Fillinghelptables...2015-08-1314:20:160[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated. Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2015-08-1314:20:160[Note]./bin/mysqld(mysqld5.6.26)startingasprocess1957... 2015-08-1314:20:161957[Note]InnoDB:Usingmutexestorefcountbufferpoolpages 2015-08-1314:20:161957[Note]InnoDB:TheInnoDBmemoryheapisdisabled 2015-08-1314:20:161957[Note]InnoDB:Mutexesandrw_locksuseInnoDB'sownimplementation 2015-08-1314:20:161957[Note]InnoDB:Memorybarrierisnotused 2015-08-1314:20:161957[Note]InnoDB:Compressedtablesusezlib1.2.3 2015-08-1314:20:161957[Note]InnoDB:UsingLinuxnativeAIO 2015-08-1314:20:161957[Note]InnoDB:NotusingCPUcrc32instructions 2015-08-1314:20:161957[Note]InnoDB:Initializingbufferpool,size=128.0M 2015-08-1314:20:161957[Note]InnoDB:Completedinitializationofbufferpool 2015-08-1314:20:161957[Note]InnoDB:HighestsupportedfileformatisBarracuda. 2015-08-1314:20:161957[Note]InnoDB:128rollbacksegment(s)areactive. 2015-08-1314:20:161957[Note]InnoDB:Waitingforpurgetostart 2015-08-1314:20:161957[Note]InnoDB:5.6.26started;logsequencenumber1625977 2015-08-1314:20:171957[Note]Binlogend 2015-08-1314:20:171957[Note]InnoDB:FTSoptimizethreadexiting. 2015-08-1314:20:171957[Note]InnoDB:Startingshutdown... 2015-08-1314:20:181957[Note]InnoDB:Shutdowncompleted;logsequencenumber1625987
OK
Tostartmysqldatboottimeyouhavetocopy support-files/mysql.servertotherightplaceforyoursystem PLEASEREMEMBERTOSETAPASSWORDFORTHEMySQLrootUSER! Todoso,starttheserver,thenissuethefollowingcommands: ./bin/mysqladmin-urootpassword'new-password' ./bin/mysqladmin-uroot-hlocalhost.localdomainpassword'new-password' Alternativelyyoucanrun: ./bin/mysql_secure_installation whichwillalsogiveyoutheoptionofremovingthetest databasesandanonymoususercreatedbydefault.Thisis stronglyrecommendedforproductionservers. Seethemanualformoreinstructions. YoucanstarttheMySQLdaemonwith: cd.;./bin/mysqld_safe& YoucantesttheMySQLdaemonwithmysql-test-run.pl cdmysql-test;perlmysql-test-run.pl Pleasereportanyproblemsathttp://bugs.mysql.com/ ThelatestinformationaboutMySQLisavailableonthewebat http://www.mysql.com SupportMySQLbybuyingsupport/licensesathttp://shop.mysql.com Newdefaultconfigfilewascreatedas./my.cnfand willbeusedbydefaultbytheserverwhenyoustartit. Youmayeditthisfiletochangeserversettings WARNING:Defaultconfigfile/etc/my.cnfexistsonthesystem ThisfilewillbereadbydefaultbytheMySQLserver Ifyoudonotwanttousethis,eitherremoveit,orusethe --defaults-fileargumenttomysqld_safewhenstartingtheserver [root@localhostmysql]#
[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).
其原因是从5.6开始，timestamp的默认行为已经是deprecated了。
在MySQL5.6.6之前，TIMESTAMP的默认行为：
TIMESTAMP列如果没有明确声明NULL属性，默认为NOTNULL。（而其他数据类型，如果没有显示声明为NOTNULL，则允许NULL值。）设置TIMESTAMP的列值为NULL，会自动存储为当前timestamp。
表中的第一个TIMESTAMP列，如果没有声明NULL属性、DEFAULT或者ONUPDATE，会自动分配DEFAULTCURRENT_TIMESTAMP和ONUPDATECURRENT_TIMESTAMP属性。
表中第二个TIMESTAMP列，如果没有声明为NULL或者DEFAULT子句，默认自动分配'0000-00-0000:00:00′。插入行时没有指明改列的值，该列默认分配'0000-00-0000:00:00′，且没有警告。
要关闭警告，需要加入下面的参数：
explicit_defaults_for_timestamp=true
重启MySQL后错误消失，这时TIMESTAMP的行为如下：
TIMESTAMP如果没有显示声明NOTNULL，是允许NULL值的，可以直接设置改列为NULL，而没有默认填充行为。
TIMESTAMP不会默认分配DEFAULTCURRENT_TIMESTAMP和ONUPDATECURRENT_TIMESTAMP属性。
声明为NOTNULL且没有默认子句的TIMESTAMP列是没有默认值的。往数据表中插入列，又没有给TIMESTAMP列赋值时，如果是严格SQL模式，会抛出一个错误，如果严格SQL模式没有启用，该列会赋值为'0000-00-0000:00:00′，同时出现一个警告。（这和MySQL处理其他时间类型数据一样，如DATETIME）
（参见：https://www.nhooo.com/article/71107.htm）
也就是explicit_defaults_for_timestamp关闭了timestamp类型字段锁拥有的一些会让人感到奇怪的默认行为，加入了该参数之后，如果还需要为timestamp类型的字段指定默认行为，那么就需要显示的在创建表时显示的指定。explicit_defaults_for_timestamp也就是这个意思：显示指定默认值为timestamp类型的字段。