mysql5.7 datetime 默认值0000-00-00 00:00:00出错

实验环境：MySQL 5.7.17

使用wordpress的表wp_posts

mysql>CREATETABLE`wp_posts`(->`ID`bigint(20)unsignedNOTNULLAUTO_INCREMENT,->`post_author`bigint(20)unsignedNOTNULLDEFAULT'0',->`post_date`datetimeNOTNULLDEFAULT'0000-00-0000:00:00',->`post_date_gmt`datetimeNOTNULLDEFAULT'0000-00-0000:00:00',->`post_content`longtextNOTNULL,->`post_title`textNOTNULL,->`post_excerpt`textNOTNULL,->`post_status`varchar(20)NOTNULLDEFAULT'publish',->`comment_status`varchar(20)NOTNULLDEFAULT'open',->`ping_status`varchar(20)NOTNULLDEFAULT'open',->`post_password`varchar(20)NOTNULLDEFAULT'',->`post_name`varchar(200)NOTNULLDEFAULT'',->`to_ping`textNOTNULL,->`pinged`textNOTNULL,->`post_modified`datetimeNOTNULLDEFAULT'0000-00-0000:00:00',->`post_modified_gmt`datetimeNOTNULLDEFAULT'0000-00-0000:00:00',->`post_content_filtered`longtextNOTNULL,->`post_parent`bigint(20)unsignedNOTNULLDEFAULT'0',->`guid`varchar(255)NOTNULLDEFAULT'',->`menu_order`int(11)NOTNULLDEFAULT'0',->`post_type`varchar(20)NOTNULLDEFAULT'post',->`post_mime_type`varchar(100)NOTNULLDEFAULT'',->`comment_count`bigint(20)NOTNULLDEFAULT'0',->PRIMARYKEY(`ID`),->KEY`post_name`(`post_name`(191)),->KEY`type_status_date`(`post_type`,`post_status`,`post_date`,`ID`),->KEY`post_parent`(`post_parent`),->KEY`post_author`(`post_author`)->)ENGINE=innodbAUTO_INCREMENT=536DEFAULTCHARSET=utf8;ERROR1067(42000):Invaliddefaultvaluefor'post_date'

出现以上问题：是因为mysql5.7版本后，sql_mode参数被设置了NO_ZERO_IN_DATE,NO_ZERO_DATE

mysql>showvariableslike'sql_mode';+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+|Variable_name|Value|+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+|sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.01sec)

解决方法：

方法一：临时修改方法

mysql>setglobalsql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';mysql>setsessionsql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

方法二：永久修改方法,需要重启mysql服务:修改mysql的配置文件my.cnf,添加以下参数

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION//实际是去除NO_ZERO_IN_DATE,NO_ZERO_DATE

方法三：修改datetime的默认值