#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci

问题:

I have a WordPress website on my local WAMP server. But when I upload its database to live server, I get error

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Any help would be appreciated!

回答1:

You can solve this by finding

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

in your .sql file, and swapping it with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

回答2:

I believe this error is caused because the local server and live server are running different versions of MySQL. To solve this:

1. Open the sql file in your text editor
2. Find and replace all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci
3. Save and upload to a fresh mySql db

Hope that helps

回答3:

In my case it turns out my
new server was running MySQL 5.5,
old server was running MySQL 5.6.
So I got this error when trying to import the .sql file I'd exported from my old server.

MySQL 5.5 does not support utf8mb4_unicode_520_ci, but
MySQL 5.6 does.

Updating to MySQL 5.6 on the new server solved collation the error !

If you want to retain MySQL 5.5, you can:
- make a copy of your exported .sql file
- replace instances of utf8mb4unicode520_ci and utf8mb4_unicode_520_ci
...with utf8mb4_unicode_ci
- import your updated .sql file.

回答4:

Open the sql file in your text editor;

1. Search: utf8mb4_unicode_ci Replace: utf8_general_ci (Replace All)

2. Search: utf8mb4_unicode_520_ci Replace: utf8_general_ci (Replace All)

3. Search: utf8mb4 Replace: utf8 (Replace All)

Save and upload!

回答5:

just remove "520_"
utf8mb4_unicode_520_ci → utf8mb4_unicode_ci

回答6:

find and replace:

utf8mb4_unicode_520_ci

with

utf8_general_ci

in whole sql file

回答7:

easy replace

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql

回答8:

Late to the party, but in case this happens with a WORDPRESS installation :

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci

In phpmyadmin, under export method > Format-specific options( custom export )

Set to : MYSQL40

If you will try to import now, you now might get another error message :

1064 - You have an error in your SQL syntax; .....

That is because The older TYPE option that was synonymous with ENGINE was removed in MySQL 5.5.

Open your .sql file , search and replace all instances

from TYPE= to ENGINE=

Now the import should go smoothly.