在传输数据库/服务器设置期间使用波兰字符集编码错误 [英] Encoding error with polish charset during transfer of database / server seting up

问题描述

我试图将一个数据库从一个主机（home.pl）传输到另一个（我新设置的服务器）。我试图转移的脚本是wordpress。不幸的是，不管使用的方法我正在努力与编码问题。

$ b

新的主机配置

在我的新服务器中，我使用以下指令in my.cnf：

  [mysql] 
 default-character-set = utf8 
 $ b b [mysqld] 
 collat​​ion-server = utf8_general_ci 
 character-set-server = utf8 
 init_connect ='set collat​​ion_connection = utf8_general_ci'
 init_connect ='SET NAMES utf8'
 
 [client] 
 default-character-set = utf8 
  

vars：

  character_set_client utf8 
 character_set_connection utf8 
 character_set_database utf8 
 character_set_filesystem binary 
 character_set_results utf8 
 character_set_server utf8 
 character_set_system utf8 
 collat​​ion_connection utf8_general_ci 
 collat​​ion_database utf8_general_ci 
 collat​​ion_server utf8_general_ci 
  

在新服务器上的Php.ini：

  PHP的默认字符集设置为UTF-8。 
; http://php.net/default-charset 
 default_charset =UTF-8
  

旧主机配置

我在我尝试传输数据库的旧主机上运行了SHOW VARIABLES，我得到以下结果： / p>

  character_set_client utf8 
 character_set_connection utf8mb4 
 character_set_database utf8 
 character_set_results utf8 
 character_set_server latin2 
 character_set_system utf8 
 / usr / local / pssql55 / share / charsets / 
 collat​​ion_connection utf8mb4_general_ci 
 collat​​ion_database utf8_polish_ci 
 collat​​ion_server latin2_general_ci 
  

已尝试的传输方法

1）通过phpmyadmin传输

我已经尝试使用PHPMYADMIN导出/导入。特别是我已指出UTF-8作为文件字符集，在导出和导入过程中通过phpmyadmin。

在源服务器上的phpmyadmin和新主机上我都看不到波兰字符（输出是相同的没有波兰字符）。

2）通过mysql dump导出/导入

我也尝试过使用：

  mysqldump -h OLD_HOST -u OLD_USER -p DB | mysql -h localhost -u root NEW DATABASE 
  

但编码也失败。

尝试也使用编码变量，但它也失败了：

  mysqldump --default-character -set = latin1 | mysql --default-character-set = utf8 
  

转储文件 p>

在我的转储文件中使用带有UTF-8编码集的Programers Notepad，charcters看起来像这样：

 Ä而不是ę
  

 Ä™而不是ę
  

编码转换器（gżegżółka）识别文件位于：
C：\Users\mkondej001\Desktop\14271425_mk.sql

  Kodowanie：UNICODE UTF-8 
 EOL：LF（Unix）
  

任何线索如何正确传输DB / set服务器变量？

解决方案

最后我发现，问题是与数据写入SQL在我的原始服务器不正确的事实有关。

我最后使用传输数据库：

  mysqldump  - default-character-set = utf8 [ORYGINAL_DB] | mysql [TARGET_DB] --default-character-set = utf8 
  

  UPDATE [table name] SET [field] = CONVERT（BINARY CONVERT（[field] USING latin2）USING utf8）
  
 
 
 
。
 
 
 
 存储数据的奇怪字符编码，旧脚本显示它们罚款新的不会 
 
 
 
希望上述解决方案对其他人也有帮助。 
 
I am trying to transfer one of my databases from one host (home.pl) to another (my newly set server). The script that I am trying to transfer is wordpress. Unluckily irrespective of the method used I am struggling with encoding problems. 

New host configuration

In my new server I am using the following directives in my.cnf:
[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
init_connect='SET collation_connection = utf8_general_ci' 
init_connect='SET NAMES utf8' 

[client]
default-character-set=utf8
My mySQL vars:
character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8
character_set_system    utf8
collation_connection    utf8_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci
Php.ini on new server:
; PHP's default character set is set to UTF-8.
; http://php.net/default-charset
default_charset = "UTF-8"
Old host configuration

I have runned SHOW VARIABLES in my old host from which I am trying to transfer database and I got the following:
character_set_client    utf8
character_set_connection    utf8mb4
character_set_database  utf8
character_set_results   utf8
character_set_server    latin2
character_set_system    utf8
/usr/local/pssql55/share/charsets/
collation_connection    utf8mb4_general_ci
collation_database  utf8_polish_ci
collation_server    latin2_general_ci
Transfer methods tried out

1) Transfer via phpmyadmin

I have tried using PHPMYADMIN export/import. In particular I have pointed out UTF-8 as file character set both during export and import via phpmyadmin. 

What is strange both in phpmyadmin on source server and new host I don't see polish chars (the output is the same without polish chars).

2) Export / Import via mysql dump

I have tried also to use:
 mysqldump -h OLD_HOST -u OLD_USER -p DB | mysql -h localhost -u root NEW DATABASE
but the encoding also fails.

Tried to use also encoding variables but it also failed:
 mysqldump --default-character-set=latin1  | mysql --default-character-set=utf8 
Dump file

In my dump file using Programers Notepad with UTF-8 encoding set,  charcters look like this:
"Ä" instead of "ę"
Opening them in microsoft word I see
Ä™ instead of "ę"
The encoding converter (gżegżółka) recognises that the file is in:
C:\Users\mkondej001\Desktop\14271425_mk.sql
Kodowanie: Unicode UTF-8
EOL: LF (Unix) 
Any clues how to transfer DB / set server variables correctly ? 
解决方案
At the end I have founded out that the problem was related to the fact that the data was written to SQL incorrectly in my original server. 

I ended up with transferring DB using:
mysqldump --default-character-set=utf8 [ORYGINAL_DB] | mysql [TARGET_DB] --default-character-set=utf8
and the executing:
UPDATE [table name] SET [field] = CONVERT(BINARY CONVERT([field] USING latin2) USING utf8)
as it was advices here:

strange character encoding of stored data , old script is showing them fine new one doesn't 

Hope that the above solution will be helpful for others too. 

                        
这篇关于在传输数据库/服务器设置期间使用波兰字符集编码错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！