昨晚迁移几个网站的数据库，出现错误，本以为都是mysql5.5，应该不会有什么问题，导入的时候都在报错
使用命令导入报错如下（导入命令参考https://xiaohost.com/1202.html）：
1 | 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 |
使用命令强行导入：
1 | mysql -u **** -p ***** < ./****.sql -f |
还是提示错误如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | ERROR 1273 (HY000) at line 29: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1273 (HY000) at line 45: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1273 (HY000) at line 75: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1273 (HY000) at line 99: Unknown collation: 'utf8mb4_unicode_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_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_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_ci' ERROR 1273 (HY000) at line 866: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1146 (42S02) at line 881: Table '**********.wp_terms' doesn't exist ERROR 1273 (HY000) at line 1007: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1146 (42S02) at line 1023: Table '**********.wp_term_taxonomy' doesn't exist ERROR 1273 (HY000) at line 1119: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1146 (42S02) at line 1133: Table '**********.wp_usermeta' doesn't exist ERROR 1273 (HY000) at line 1189: Unknown collation: 'utf8mb4_unicode_ci' ERROR 1146 (42S02) at line 1210: Table '**********.wp_users' doesn't exist |
解决办法:使用notepad++ 打开导出的****.sql文件（类似工具也可以，注意保持utf-8编码）
将
1 | utf8mb4_unicode_ci |
替换为
1 | utf8_general_ci |
将
1 | utf8mb4_unicode_ci |
替换为
1 | utf8_general_ci |
将
1 | utf8mb4 |
替换为
1 | utf-8 |
注意替换顺序不能错，从大到小，替换后导入就没问题了，产生这个错误的原因主要为mysql版本问题，使用的编码排序设置不同导致的
原文链接：https://xiaohost.com/1725.html，转载请注明出处。
评论0