将UTF-8编码转储加载到MySQL [英] Loading UTF-8 encoded dump into MySQL

问题描述

我昨天已经把这个问题拉过了这个问题了：

I've been pulling my hear out over this problem for a few hours yesterday:

我在MySQL 4.1.22服务器上有一个数据库，编码设置为UTF-8 Unicode（utf8）（由phpMyAdmin报告）。此数据库中的表具有设置为 latin2 的默认字符集。但是，使用它的Web应用程序（CMS Made Simple用PHP编写）显示在 utf8 ...

I've a database on MySQL 4.1.22 server with encoding set to "UTF-8 Unicode (utf8)" (as reported by phpMyAdmin). Tables in this database have default charset set to latin2. But, the web application (CMS Made Simple written in PHP) using it displays pages in utf8...

中的页面但是，这可能是，它实际上是有效的。 Web应用程序正确显示字符（主要使用捷克语和波兰语）。

However screwed up this may be, it actually works. The web app displays characters correctly (mostly Czech and Polish are used).

我运行：mysqldump -u xxx -p -h yyy dbname> dump.sql。这给了我一个SQL脚本，它在 UTF-8

I run: "mysqldump -u xxx -p -h yyy dbname > dump.sql". This gives me an SQL script which:

• 在任何编辑器（如记事本+）中看起来完美， b> - 所有字符正确显示


• 脚本中的所有表都将默认字符集设置为 latin2


• 它具有 / *！40101 SET NAMES latin2 * /;开始的行（其他设置）

现在，我要将此数据库导出到另一台运行在MySQL 5.0.67上的服务器，服务器编码也设置为UTF-8 Unicode（utf8）。我复制了整个CMS Made Simple安装，复制了dump.sql脚本并运行mysql -h ddd -u zzz -p dbname< dump.sql。之后，显示CMSMS网页时，所有字符都会被加密。

Now, I want to export this database to another server running on MySQL 5.0.67, also with server encoding set to "UTF-8 Unicode (utf8)". I copied the whole CMS Made Simple installation over, copied the dump.sql script and ran "mysql -h ddd -u zzz -p dbname < dump.sql". After that, all the characters are scrambled when displaying CMSMS web pages.

我尝试设置：

SET character_set_client = utf8;

SET character_set_connection = latin2;

I tried setting:
SET character_set_client = utf8;
SET character_set_connection = latin2;

所有组合（只是为了安全，即使对我没有任何意义）：latin2 / utf8，latin2 / latin2，utf8 / utf8等 - 没有帮助。所有字符仍然乱码，但有时以不同的方式：）。

And all combinations (just to be safe, even if it doesn't make any sense to me): latin2/utf8, latin2/latin2, utf8/utf8, etc. - doesn't help. All characters still scrambled, however sometimes in a different way :).

我还尝试用脚本中的utf8替换所有latin2设置（表的设置名称和默认字符集） 。没有什么。

I also tried replacing all latin2 settings with utf8 in the script (set names and default charsets for tables). Nothing.

这里有任何MySQL专家可以用几句话来解释（我确定这很简单）这个整个编码的东西真的有效吗？我阅读了 9.1.4。连接字符集和排序，但发现没有任何帮助。

Are there any MySQL experts here who could explain in just a few words (I'm sure it's simple after all) how this whole encoding stuff really works? I read 9.1.4. Connection Character Sets and Collations but found nothing helpful there.

谢谢，
Matt

Thanks, Matt

推荐答案

Ugh ...好的，似乎我找到了一个解决方案。

Ugh... ok, seems I found a solution.

MySQL不是这里的罪魁祸首。我做了一个简单的转储，现在加载，没有更改dump.sql脚本 - 意思是我留下设置名称latin2和表字符串，因为它们是。然后我将我的原始CMSMS安装切换到新数据库，...它正常工作。所以实际上在数据库中的编码是确定的，或至少它适用于CMSMS安装我在我的旧主机提供商（CMSMS显然使用字符编码做有趣的事情）。

MySQL isn't the culprit here. I did a simple dump and load now, with no changes to the dump.sql script - meaning I left "set names latin2" and tables charsets as they were. Then I switched my original CMSMS installation over to the new database and... it worked correctly. So actually encoding in the database is ok, or at least it works fine with CMSMS installation I had at my old hosting provider (CMSMS apparently does funny things with characters encoding).

为了使其在我的新托管服务提供商工作，我实际上已经在CMSMS安装中将此行添加到lib / adodb / drivers / adodb-mysql.inc.php：

To make it work on my new hosting provider, I actually had to add this line to lib/adodb/drivers/adodb-mysql.inc.php in CMSMS installation:

mysql_query('set names latin2',$this->_connectionID);

这是从这篇文章。您也可以在那里找到确切的线。所以看起来像mysql客户端配置问题。

This is a slightly modified solution from this post. You can find the exact line there as well. So it looks like mysql client configuration issue.

这篇关于将UTF-8编码转储加载到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！