最近把mysql升級(jí)到5.7了，wordpress導(dǎo)數(shù)據(jù)報(bào)錯(cuò)
Invalid default value for 'comment_date'
原因出在類(lèi)似這樣的語(yǔ)句
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;
這種報(bào)錯(cuò)多是你mysql升級(jí)到5.7而引起的默認(rèn)值不兼容的問(wèn)題。看看你的字段名是什么，我的是時(shí)間字段，類(lèi)型是datetime。想到可能是類(lèi)型的默認(rèn)值被限制了，查看sql_mode。果然：NO_ZERO_IN_DATE,NO_ZERO_DATE這兩個(gè)參數(shù)限制時(shí)間不能為0
查看sql_mode
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>
臨時(shí)修改：
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環(huán)境下修改mysql.ini配置文件就解決了~
在[mysqld]下面添加如下列：
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ok問(wèn)題解決，現(xiàn)在你導(dǎo)入或者創(chuàng)建表時(shí)看看！
以上就是解決mysql升級(jí)到5.7時(shí)wordpress導(dǎo)數(shù)據(jù)報(bào)錯(cuò)1067問(wèn)題的詳細(xì)內(nèi)容，更多關(guān)于mysql導(dǎo)入數(shù)據(jù)庫(kù)報(bào)錯(cuò)1067 的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- mysql如何利用Navicat導(dǎo)出和導(dǎo)入數(shù)據(jù)庫(kù)的方法
- MySQL命令行導(dǎo)出導(dǎo)入數(shù)據(jù)庫(kù)實(shí)例詳解
- wampserver下mysql導(dǎo)入數(shù)據(jù)庫(kù)的步驟
- MySQL命令行導(dǎo)出與導(dǎo)入數(shù)據(jù)庫(kù)
- 修改php.ini實(shí)現(xiàn)Mysql導(dǎo)入數(shù)據(jù)庫(kù)文件最大限制的修改方法
- windows無(wú)法啟動(dòng)MySQL服務(wù)報(bào)錯(cuò)1067的解決方法
- mysql服務(wù)無(wú)法啟動(dòng)報(bào)錯(cuò)誤1067解決方法(mysql啟動(dòng)錯(cuò)誤1067 )
