欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-24 22:20:41
最近把mysql升级到5.7了,wordpress导数据报错invalid default value for 'comment_date'原因出在类似这样的语句drop table if exist...

最近把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 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

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>

临时修改:

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]下面添加如下列:

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 的资料请关注其它相关文章!