MySQL 5.6 中TIMESTAMP with implicit DEFAULT value is deprecated错误
安装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′,且没有警告。
要关闭警告,需要加入下面的参数:
[mysqld] 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/71054.htm)
也就是explicit_defaults_for_timestamp关闭了timestamp类型字段锁拥有的一些会让人感到奇怪的默认行为,加入了该参数之后,如果还需要为timestamp类型的字段指定默认行为,那么就需要显示的在创建表时显示的指定。explicit_defaults_for_timestamp也就是这个意思:显示指定默认值为timestamp类型的字段。