mysql升级到5.7时,wordpress导数据报错1067的问题

最近把mysql升级到5.7了，wordpress导数据报错

Invalid default value for 'comment_date'

原因出在类似这样的语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

DROP TABLE IF EXISTS `wp_comments`; CREATE TABLE `wp_comments`  (    `comment_ID` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT,    `comment_post_ID` bigint (20) UNSIGNED NOT NULL DEFAULT 0,    `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL ,    `comment_author_email` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '' ,    `comment_author_url` varchar (200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '' ,    `comment_author_IP` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '' ,    `comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00' ,    `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00' ,    `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL ,    `comment_karma` int (11) NOT NULL DEFAULT 0,    `comment_approved` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1' ,    `comment_agent` varchar (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '' ,    `comment_type` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '' ,    `comment_parent` bigint (20) UNSIGNED NOT NULL DEFAULT 0,    `user_id` bigint (20) UNSIGNED NOT NULL DEFAULT 0,    PRIMARY KEY (`comment_ID`) USING BTREE,    INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,    INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,    INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,    INDEX `comment_parent`(`comment_parent`) USING BTREE,    INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic ;

这种报错多是你mysql升级到5.7而引起的默认值不兼容的问题。看看你的字段名是什么，我的是时间字段，类型是datetime。想到可能是类型的默认值被限制了，查看sql_mode。果然：NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制时间不能为0

查看sql_mode

?

1 2 3 4 5 6 7 8 9

mysql> show variables like '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 | + ---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)   mysql>

临时修改：

?

1 2 3 4 5

mysql> set session   -> sql_mode= 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ; Query OK, 0 rows affected, 1 warning (0.00 sec)   mysql>

永久修改：

可以直接修改my.cnf文件

例如： vim /etc/my.cnf

Windows环境下修改mysql.ini配置文件就解决了~

在[mysqld]下面添加如下列：

?

1	sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ok问题解决，现在你导入或者创建表时看看！

以上就是解决mysql升级到5.7时wordpress导数据报错1067问题的详细内容，更多关于mysql导入数据库报错1067 的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/wpjamer/p/7057577.html