转换“错误”来自旧服务器的MySQL编码，在新服务器上正确UTF-8 [英] Converting "wrong" MySQL encoding from old server to correct UTF-8 on new server

问题描述

我在Web服务器上设置了一个Web项目，显然编码错误。但是，不知何故（我真的不知道为什么），我们知道我们如何处理它，编码对我们有用。
但现在我们将数据移动到具有正确设置的UTF8数据库的新服务器并且令人惊讶，编码错误。
我们如何纠正数据，有没有最佳实践？

示例
旧服务器： a href =http://www.orat.io/stmt/200 =nofollow> http://www.orat.io/stmt/200
新服务器： http://www.firefile.net/stmt/200

非常感谢！

解决方案

这实际上是在实践中发生的 - 我看过它有时候:-)什么通常会发生：

• 数据库位于 utf-8


• 输入/输出在编码1 - 说它是 iso-8859-2 （即HTML中的元字符集）

但是，因为以前的管理员不知道如何正确设置数据库连接编码（这是使用 set names SQL命令指定的字符集），所以数据库连接被配置为输入/输出在不同的encoding2中 - 例如 iso-8859-1 - 默认为许多linux系统。

要从数据库中获取正确的数据，您必须以与以前相同的错误方式访问它 - 例如对于上面的例子，设置 iso-8859-1 编码，但实际上得到了 iso-8859-2 。一切都可以正常工作，直到有人开始正确地访问数据。

你没有提供足够的细节，关于你的情况下的encoding1 / encoding2是什么，你可能不知道它。所以，要么看看旧的设置，要么尝试通过试错来找出。

重新编码数据库的最简单方法是可能要在编码2中执行 mysqldump ，然后声明它是编码1（只是在sql文件的开头放置设置名称encoding1 ），并通过源SQL数据库将数据库导入空。所有数据库字段当然都保留在 utf-8 中。

请小心使用PHPMyAdmin作为独立工具看到数据库的真实状态。 :-)祝你有这个回扣。

I set up a web project on a web server with apparently wrong encoding. But somehow (I really don't know why), we figured how we had to deal with it and the encoding worked for us. But now we moved the data to a new server with a correctly set up UTF8 database and surprise, the encoding is wrong. How can we "correct" the data, is there any best practice?

Example old server: http://www.orat.io/stmt/200 new server: http://www.firefile.net/stmt/200

Thanks a lot!

解决方案

This actually happens in practice - I've seen it sometimes :-) What usually happens:

• database is in utf-8
• input/output is in encoding1 - say it is iso-8859-2 (i.e. the meta-charset in HTML)
• but, because the previous admin didn't know how to properly set the database connection encoding (this is the charset which is specified using set names SQL command), the database connection is configured as if the input/output is in different encoding2 - say iso-8859-1 - default on many linux systems.

To get the correct data out of the database, you must access it in the same erroneous way as you did before - e.g. for the example above, set iso-8859-1 encoding but in fact get the iso-8859-2. Everything "works" until someone starts to access the database correctly.

You have not provided enough detail about what is encoding1/encoding2 in your case and you probably don't know it. So, either look at the old setup or try to figure out by trial and error.

The easiest way to re-code your database is probably to do mysqldump in encoding2, then claim it's encoding1 (just put the set names encoding1 at the beginning of sql file) and import the database into an empty one by source-ing the sql file. All database fields of course stay in utf-8 all the time.

Be careful and use PHPMyAdmin as an independent tool to see the "real" state of the database. :-) Good luck with this rebus.

这篇关于转换“错误”来自旧服务器的MySQL编码，在新服务器上正确UTF-8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！