mysql导入wordpress_WordPress搬家，导入mysql出错的解决方法 - 老牛博客

昨晚迁移几个网站的数据库，出现错误，本以为都是mysql5.5，应该不会有什么问题，导入的时候都在报错

使用命令导入报错如下(导入命令参考https://xiaohost.com/1202.html)：

ERROR 1064 (42000) at line 28: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM AUTO_INCREMENT=1' at line 9

使用命令强行导入：

mysql -u **** -p ***** < ./****.sql -f

还是提示错误如下

ERROR 1273 (HY000) at line 29: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1273 (HY000) at line 45: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1273 (HY000) at line 75: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1273 (HY000) at line 99: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 112: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 245: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 247: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 272: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 274: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 295: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 297: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 323: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 394: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 402: Table '**********.wp_options' doesn't exist

ERROR 1146 (42S02) at line 415: Table '**********.wp_options' doesn't exist

ERROR 1273 (HY000) at line 429: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 443: Table '**********.wp_postmeta' doesn't exist

ERROR 1146 (42S02) at line 628: Table '**********.wp_postmeta' doesn't exist

ERROR 1273 (HY000) at line 695: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 730: Table '**********.wp_posts' doesn't exist

ERROR 1146 (42S02) at line 819: Table '**********.wp_posts' doesn't exist

ERROR 1273 (HY000) at line 850: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1273 (HY000) at line 866: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 881: Table '**********.wp_terms' doesn't exist

ERROR 1273 (HY000) at line 1007: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 1023: Table '**********.wp_term_taxonomy' doesn't exist

ERROR 1273 (HY000) at line 1119: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 1133: Table '**********.wp_usermeta' doesn't exist

ERROR 1273 (HY000) at line 1189: Unknown collation: 'utf8mb4_unicode_520_ci'

ERROR 1146 (42S02) at line 1210: Table '**********.wp_users' doesn't exist

解决办法:使用notepad++ 打开导出的****.sql文件(类似工具也可以，注意保持utf-8编码)

将

utf8mb4_unicode_520_ci

替换为

utf8_general_ci

将

utf8mb4_unicode_ci

替换为

utf8_general_ci

将

utf8mb4

替换为

utf-8

注意替换顺序不能错，从大到小，替换后导入就没问题了，产生这个错误的原因主要为mysql版本问题，使用的编码排序设置不同导致的