mysql导入sql1067报错_导入数据库时报错1067 – Invalid default value for ‘字段名’

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

Invalid default value for 'comment_date'

原因出在类似这样的语句

DROP TABLE IF EXISTS`wp_comments`;CREATE TABLE`wp_comments` (

`comment_ID`bigint(20) UNSIGNED NOT NULLAUTO_INCREMENT,

`comment_post_ID`bigint(20) UNSIGNED NOT NULL DEFAULT 0,

`comment_author` tinytextCHARACTER 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

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.00sec)

mysql>

临时修改：

mysql> setsession-> 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.00sec)

mysql>

永久修改：

可以直接修改my.cnf文件

例如：     vim /etc/my.cnf

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

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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