MySQL #1273 - 未知排序规则：'utf8mb4_unicode_ci' cPanel

Question

提问by Wairimu Murigi

I have a WordPress database on my local machine that I want to transfer to a hosted phpMyAdmin on cPanel. However, when I try to import the database into the environment, I keep getting this error:

我的本地机器上有一个 WordPress 数据库，我想将其传输到 cPanel 上托管的 phpMyAdmin。但是，当我尝试将数据库导入环境时，我不断收到此错误：

#1273 - Unknown collation: 'utf8mb4_unicode_ci'

I have tried to Google around and the only solution I can find is this one phpmysql error - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci'which as by now isn't much help. I have tried clearing the cookies but it still won't work. Please help!

我试过谷歌搜索，我能找到的唯一解决方案是这个phpmysql 错误 - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci'到目前为止没有太大帮助。我已经尝试清除 cookie，但它仍然不起作用。请帮忙！

Answer 1

采纳答案by Stephen

I had the same issue as all of our servers run older versions of MySQL. This can be solved by running a PHP script. Save this code to a file and run it entering the database name, user and password and it'll change the collation from utf8mb4/utf8mb4_unicode_cito utf8/utf8_general_ci

我遇到了同样的问题，因为我们所有的服务器都运行旧版本的 MySQL。这可以通过运行 PHP 脚本来解决。将此代码保存到文件中并输入数据库名称、用户和密码运行它，它会将排序规则从更改utf8mb4/utf8mb4_unicode_ci为utf8/utf8_general_ci

<!DOCTYPE html>
<html>
<head>
  <title>DB-Convert</title>
  <style>
    body { font-family:"Courier New", Courier, monospace; }
  </style>
</head>
<body>

<h1>Convert your Database to utf8_general_ci!</h1>

<form action="db-convert.php" method="post">
  dbname: <input type="text" name="dbname"><br>
  dbuser: <input type="text" name="dbuser"><br>
  dbpass: <input type="text" name="dbpassword"><br>
  <input type="submit">
</form>

</body>
</html>
<?php
if ($_POST) {
  $dbname = $_POST['dbname'];
  $dbuser = $_POST['dbuser'];
  $dbpassword = $_POST['dbpassword'];

  $con = mysql_connect('localhost',$dbuser,$dbpassword);
  if(!$con) { echo "Cannot connect to the database ";die();}
  mysql_select_db($dbname);
  $result=mysql_query('show tables');
  while($tables = mysql_fetch_array($result)) {
          foreach ($tables as $key => $value) {
           mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
     }}
  echo "<script>alert('The collation of your database has been successfully changed!');</script>";
}

?>

Answer 2

回答by Evster

The technique in this post worked for me

这篇文章中的技术对我有用

1) Click the "Export" tab for the database

1) 单击数据库的“导出”选项卡

2) Click the "Custom" radio button

2) 单击“自定义”单选按钮

3) Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.

3) 转到标题为“特定格式选项”的部分，并将“数据库系统或较旧的 MySQL 服务器以最大限度地提高输出兼容性：”的下拉列表从 NONE 更改为 MYSQL40。

4) Scroll to the bottom and click "GO".

4) 滚动到底部并单击“开始”。

I'm not certain if doing this causes any data loss, however in the one time I've tried it I did not notice any. Neither did anyone who responded in the forums linked to above.

我不确定这样做是否会导致任何数据丢失，但是在我尝试过的一次中，我没有注意到任何数据丢失。在与上述链接的论坛中做出回应的人也没有。

Edit 8/12/16- I believe exporting a database in this way causes me to lose data saved in Black Studio TinyMCE Visual Editorwidgets, though I haven't ran multiple tests to confirm.

2016年 8 月 12 日编辑- 我相信以这种方式导出数据库会导致我丢失保存在Black Studio TinyMCE Visual Editor小部件中的数据，尽管我还没有运行多个测试来确认。

Answer 3

回答by Rangel R. Morais

If you have already exported a .sqlfile, the best thing to do is to Find and Replace the following if you have them in your file:

如果您已经导出了一个.sql文件，最好的办法是查找并替换以下文件（如果您的文件中有它们）：

utf8mb4_0900_ai_cito utf8_unicode_ci
utf8mb4to utf8
utf8_unicode_520_cito utf8_unicode_ci

utf8mb4_0900_ai_ci到 utf8_unicode_ci
utf8mb4到 utf8
utf8_unicode_520_ci到 utf8_unicode_ci

It will replace utf8mb4_unicode_cito utf8_unicode_ci. Now you go to your phpMyAdmin cPanel and set the DB collation to utf8_unicode_cithrough Operations > Collation.

它将替换utf8mb4_unicode_ci为utf8_unicode_ci。现在您转到 phpMyAdmin cPanel 并utf8_unicode_ci通过Operations > Collation将 DB 排序规则设置为。

If you are exporting to a .sql, it's better to change the format on how you're exporting the file. Check out Evster's anwer(it's in the same page as this)

如果要导出到 . .sql，最好更改导出文件的格式。查看Evster 的答案（与此在同一页面中）

Answer 4

回答by halilintar8

i use this in linux :

我在 linux 中使用它：

sed -i 's/utf8mb4/utf8/g' your_file.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' your_file.sql

then restore your_file.sql

然后恢复 your_file.sql

mysql -u yourdBUser -p yourdBPasswd yourdB < your_file.sql

Answer 5

回答by Mark Thomson

Wordpress 4.2 introduced support for "utf8mb4" character encoding for security reasons, but only MySQL 5.5.3 and greater support it. The way the installer (and updater) handles this is that it checks your MySQL version and your database will be upgraded to utfmb4 only if it's supported.

出于安全原因，Wordpress 4.2 引入了对“utf8mb4”字符编码的支持，但只有 MySQL 5.5.3 及更高版本支持它。安装程序（和更新程序）处理此问题的方式是它会检查您的 MySQL 版本，并且只有在受支持时，您的数据库才会升级到 utfmb4 。

This sounds great in theory but the problem (as you've discovered) is when you are migrating databases from a MySQL server that supports utf8mb4 to one that doesn't. While the other way around should work, it's basically a one-way operation.

这在理论上听起来不错，但问题（正如您所发现的）是当您将数据库从支持 utf8mb4 的 MySQL 服务器迁移到不支持的 MySQL 服务器时。虽然另一种方式应该可行，但它基本上是一种单向操作。

As pointed out by Evster you mighthave success using PHPMYAdmin's "Export" feature. Use "Export Method: Custom" and for the "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".

正如 Evster 指出的那样，您可能会成功使用 PHPMYAdmin 的“导出”功能。使用“导出方法：自定义”，对于“数据库系统或更旧的 MySQL 服务器，以最大限度地提高输出兼容性：”下拉选择“ MYSQL 40”。

For a command line export using mysqldump. Have a look at the flag:

对于使用 mysqldump 的命令行导出。看看国旗：

$ mysqldump --compatible=mysql4

Note: If there are any 4-byte characters in the database they will be corrupted.

注意：如果数据库中有任何 4 字节字符，它们将被损坏。

Lastly, for anyone using the popular WP Migrate DB PRO plugin, a user in this Wordpress.org threadreports that the migration is always handled properly but I wasn't able to find anything official.

最后，对于使用流行的 WP Migrate DB PRO 插件的任何人，此 Wordpress.org 线程中的用户报告说迁移始终得到正确处理，但我找不到任何官方信息。

The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL

当使用 5.5.3 之前或之后的 MySQL 在主机之间移动 4.2 站点时，WP Migrate DB 插件将数据库从一种排序规则转换为另一种排序规则

At this time, there doesn't appear to be a way to opt out of the database update. So if you are using a workflow where you are migrating a site from a server or localhost with MySQL > 5.5.3 to one that uses an older MySQL version you might be out of luck.

目前，似乎没有办法选择退出数据库更新。因此，如果您使用的工作流程是将站点从 MySQL > 5.5.3 的服务器或本地主机迁移到使用较旧 MySQL 版本的站点，那么您可能不走运。

Answer 6

回答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文件。

Answer 7

回答by Isk1n

There is a line in wp-config.php:

wp-config.php 中有一行：

define('DB_CHARSET', 'utf8mb4');

If you follow Markouver's/ Evster'sinstructions, don't forget to change this line on production server to

如果您按照Markouver/ Evster 的说明进行操作，请不要忘记将生产服务器上的这一行更改为

define('DB_CHARSET', 'utf8');

in order to fix broken 4-byte characters

为了修复损坏的 4 字节字符

Answer 8

回答by Dead Boyz

After the long time research i have found the solution for above:

经过长时间的研究，我找到了上述解决方案：

Firstly you change the wp-config.php> Database DB_CHARSET default to "utf8"
Click the "Export" tab for the database
Click the "Custom" radio button
Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
Scroll to the bottom and click go

首先，您将 wp-config.php> Database DB_CHARSET 默认更改为“utf8”
单击数据库的“导出”选项卡
单击“自定义”单选按钮
转到标题为“特定格式选项”的部分，并将“数据库系统或较旧的 MySQL 服务器以最大化输出兼容性：”的下拉列表从 NONE 更改为 MYSQL40。
滚动到底部并点击 go

Then you are on.

那你就上。

Answer 9

回答by flomei

Seems like your host does not provide a MySQL-version which is capable to run tables with utf8mb4 collation.

似乎您的主机没有提供能够运行具有 utf8mb4 排序规则的表的 MySQL 版本。

The WordPress tables were changed to utf8mb4 with Version 4.2 (released on April, 23rd 2015) to support Emojis, but you need MySQL 5.5.3 to use it.5.5.3. is from March 2010, so it should normally be widely available. Cna you check if your hoster provides that version?

WordPress 表已更改为 utf8mb4 版本 4.2（2015 年 4 月 23 日发布）以支持表情符号，但您需要 MySQL 5.5.3 才能使用它。5.5.3. 是从 2010 年 3 月开始的，所以它通常应该可以广泛使用。Cna 你检查你的主机是否提供那个版本？

If not, and an upgrade is not possible, you might have to look out for another hoster to run the latest WordPress versions (and you should always do that for security reasons).

如果没有，并且无法升级，您可能需要寻找另一个托管商来运行最新的 WordPress 版本（出于安全原因，您应该始终这样做）。

Answer 10

回答by Andrea

So I solved in this way, from MySQL 5.6 to MySQL 5.5:

于是我就这样解决了，从MySQL 5.6到MySQL 5.5：

$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql
$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql

(Optional) Create a .sql.gzfile:

（可选）创建.sql.gz文件：

$ gzip database_name.sql

Explanation

解释

$ mysqldump -u username -p --compatible=mysql4 database_name > database_name.sql

As explained in this answer, this is just the equivalent of this options from phpMyAdmin: "Database system or older MySQL server to maximize output compatibility with:" dropdown select "MYSQL 40".

正如在这个答案中所解释的，这只是 phpMyAdmin 中的这个选项的等价物：“数据库系统或较旧的 MySQL 服务器以最大限度地提高输出兼容性：”下拉选择“MYSQL 40”。

$ sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' database_name.sql

We needs this, to solve this issue:

我们需要这个，来解决这个问题：

ERROR 1064 (42000) at line 18: 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=InnoDB' at line 9

第 18 行的 ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 9 行的“TYPE=InnoDB”附近使用的正确语法

MySQL #1273 - 未知排序规则：'utf8mb4_unicode_ci' cPanel

提问by Wairimu Murigi

采纳答案by Stephen

回答by Evster

回答by Rangel R. Morais

回答by halilintar8

回答by Mark Thomson

回答by SherylHohman

回答by Isk1n

回答by Dead Boyz

回答by flomei

回答by Andrea

Explanation

解释

相关推荐

最近更新

标签

MySQL #1273 - 未知排序规则：'utf8mb4_unicode_ci' cPanel

提问by Wairimu Murigi

采纳答案by Stephen

回答by Evster

回答by Rangel R. Morais

回答by halilintar8

回答by Mark Thomson

回答by SherylHohman

回答by Isk1n

回答by Dead Boyz

回答by flomei

回答by Andrea

Explanation

解释

相关推荐

MySQL 如何从mysql db中删除重复条目？

SQLite 比 MySQL 快吗？

MySQL - SELECT * INTO OUTFILE LOCAL？

如何删除分区而不删除 MySQL 中的数据？

相关推荐

最近更新

标签