MySQL #1273 – 未知排序规则：'utf8mb4_unicode_520_ci'

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

提问by Shishil Patel

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

我的本地WAMP服务器上有一个 WordPress 网站。但是当我将其数据库上传到实时服务器时，出现错误

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci'

Any help would be appreciated!

任何帮助，将不胜感激！

回答by Sabba Keynejad

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

我相信这个错误是由于本地服务器和实时服务器运行不同版本的 MySQL 引起的。要解决这个问题：

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

1. 在文本编辑器中打开 sql 文件
2. 查找并全部替换utf8mb4_unicode_520_ci为utf8mb4_unicode_ci
3. 保存并上传到一个新的 mySql 数据库

Hope that helps

希望有帮助

回答by savani sandip

You can solve this by finding

您可以通过查找来解决此问题

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

in your .sqlfile, and swapping it with

在您的.sql文件中，并将其与

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

回答by SherylHohman

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 .sqlfile I'd exported from my old server.

就我而言，事实证明我的
新服务器正在运行MySQL 5.5，
旧服务器正在运行MySQL 5.6。
因此，在尝试导入.sql从旧服务器导出的文件时出现此错误。

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

MySQL 5.5 不支持utf8mb4_unicode_520_ci，但
MySQL 5.6支持。

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

更新到MySQL 5.6新服务器解决了整理错误！

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

如果您想保留 MySQL 5.5，您可以：
- 制作导出.sql文件的副本
- 替换utf8mb4unicode520_ci和utf8mb4_unicode_520_ci
...的实例utf8mb4_unicode_ci
- 导入更新的.sql文件。

回答by VUUB

Open the sql file in your text editor;

在文本编辑器中打开 sql 文件；

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

1.搜索：utf8mb4_unicode_ci 替换：utf8_general_ci （全部替换）

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

2.搜索：utf8mb4_unicode_520_ci替换：utf8_general_ci （全部替换）

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

3.搜索：utf8mb4 替换：utf8 （全部替换）

Save and upload!

保存并上传！

回答by Shakil Hossain

just remove "520_"
utf8mb4_unicode_520_ci→ utf8mb4_unicode_ci

只需删除“520_”
utf8mb4_unicode_520_ci→utf8mb4_unicode_ci

回答by Scard

easy replace

容易更换

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

回答by John

find and replace:

查找和替换：

utf8mb4_unicode_520_ci

with

和

utf8_general_ci

in whole sql file

在整个 sql 文件中

回答by Code Spy

I just opened the dump.sql file in Notepad++ and hit CTRL+H to find and replace the string "utf8mb4_0900_ai_ci" and replaced it with "utf8mb4_general_ci". Source link https://www.freakyjolly.com/resolved-when-i-faced-1273-unknown-collation-utf8mb4_0900_ai_ci-error/

我刚刚在 Notepad++ 中打开了 dump.sql 文件，然后按 CTRL+H 找到并替换字符串“ utf8mb4_0900_ai_ci”并将其替换为“ utf8mb4_general_ci”。源链接https://www.freakyjolly.com/resolved-when-i-faced-1273-unknown-collat​​ion-utf8mb4_0900_ai_ci-error/

回答by Obmerk Kronen

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

迟到了，但如果WORDPRESS安装时发生这种情况：

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci

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

在 phpmyadmin 中，在export method> Format-specific options（自定义导出）下

Set to : MYSQL40

设置 ： MYSQL40

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

如果您现在尝试导入，您现在可能会收到另一条错误消息：

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

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

That is because The older TYPEoption that was synonymous with ENGINEwas removed in MySQL 5.5.

那是因为在 MySQL 5.5 中删除了TYPE与 同义的旧选项ENGINE。

Open your .sqlfile , search and replace all instances

打开您的.sql文件，搜索并替换所有实例

from TYPE=to ENGINE=

从TYPE=到ENGINE=

Now the import should go smoothly.

现在导入应该很顺利。

回答by Mushfiqur Rahman

Getting collation error #1273 - Unknown collation: 'utf8mb4_unicode_520_ci' is caused by the difference of the MySQL version from which you export and our MySQL server to which you import. Basically, the Wordpress library for newer version checks to see what version of SQL your site is running on. If it uses MySQL version 5.6 or more, it assumes the use of a new and improved Unicode Collation Algorithm (UCA) called “utf8mb4_unicode_520_ci”. This is great unless you end up moving your WordPress site from a newer 5.6 version of MySQL to an older, pre 5.6 version of MySQL.

获取整理错误 #1273 - 未知整理：'utf8mb4_unicode_520_ci' 是由您导出的 MySQL 版本和您导入的 MySQL 服务器的差异引起的。基本上，较新版本的 Wordpress 库会检查您的站点正在运行的 SQL 版本。如果它使用 MySQL 5.6 或更高版本，则假定使用名为“utf8mb4_unicode_520_ci”的新的和改进的 Unicode 整理算法 (UCA)。这很好，除非您最终将 WordPress 站点从较新的 5.6 版本的 MySQL 移动到较旧的、低于 5.6 版本的 MySQL。

To resolve this you will either have to edit your SQL export file and do a search and replace, changing all instances of ‘utf8mb4_unicode_520_ci' to ‘utf8mb4_unicode_ci'. Or follow the steps below if you have a PHPMyAdmin:

要解决此问题，您必须编辑 SQL 导出文件并进行搜索和替换，将“utf8mb4_unicode_520_ci”的所有实例更改为“utf8mb4_unicode_ci”。或者，如果您有 PHPMyAdmin，请按照以下步骤操作：

1. Click the Export tab for the database
2. Click the Custom radio button.
3. Go the section titled Format-specific options and change the drop-down for Database system or older MySQL server to maximize output compatibility with: from NONE to MYSQL40.
4. Scroll to the bottom and click GO.

1. 单击数据库的导出选项卡
2. 单击自定义单选按钮。
3. 转到标题为特定格式选项的部分，并更改数据库系统或旧 MySQL 服务器的下拉列表，以最大限度地提高输出兼容性：从 NONE 到 MYSQL40。
4. 滚动到底部并单击开始。