I inherited a web system that I need to further develop. The system seems to be created by someone who read a 2 chapter PHP tutorial and thought he can code...
我继承了一个我需要进一步开发的网络系统。该系统似乎是由阅读2章PHP教程的人创建的,并认为他可以编写代码......
So... the webpage itself is in UTF8 and displays and inputs everything in it. The database tables have been created with UTF8 character set. But, in the config, there is "SET NAMES LATIN1". In other words, UTF8 encoded strings are populated into the database with forced latin1 coding.
所以...网页本身是UTF8,显示并输入其中的所有内容。已使用UTF8字符集创建数据库表。但是,在配置中,有“SET NAMES LATIN1”。换句话说,使用强制latin1编码将UTF8编码的字符串填充到数据库中。
Is there a way to convert this mess to actually store in utf8 and get rid of latin1? I tried this: http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion but since the database table is set to utf8, this does not work. Also tried this one https://www.percona.com/blog/2007/12/18/fixing-column-encoding-mess-in-mysql/ without success.
有没有办法将这个混乱转换为实际存储在utf8中并摆脱latin1?我试过这个:http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion但由于数据库表设置为utf8,这不起作用。还尝试了这个https://www.percona.com/blog/2007/12/18/fixing-column-encoding-mess-in-mysql/但没有成功。
I maybe able to do this by reading all tables in PHP with latin1 encoding then write them back to a new database in utf8, but I want to avoid it if possible.
我可以通过使用latin1编码读取PHP中的所有表,然后将它们写回utf8中的新数据库,但我想尽可能避免使用它。
3 个解决方案
#1
16
I managed to solve it by running updates on text fields like this:
我设法通过在这样的文本字段上运行更新来解决它:
UPDATE table SET title = CONVERT(CONVERT(CONVERT(title USING latin1) USING binary) USING UTF8)
#2
2
The situation isn't as bad as you think it is, unless you already have lots of non-Roman characters (that is, characters that aren't representable in Latin-1) in your database already. Latin-1 is a proper subset of utf8. Your web app works in utf8 and your tables' contents are in utf8 as well. So there's no need to convert the tables.
情况并不像你想象的那么糟糕,除非你的数据库中已经有很多非罗马字符(即拉丁文-1中无法表示的字符)。 Latin-1是utf8的合适子集。您的网络应用程序在utf8中工作,您的表格内容也在utf8中。所以没有必要转换表格。
So, try changing the SET NAMES latin1 to SET NAMES utf8. It will probably solve your problem, by allowing your php program's connection to work with the same character set as the code on either end of the connection.
因此,尝试将SET NAMES latin1更改为SET NAMES utf8。它可能会解决您的问题,允许您的php程序的连接使用与连接任一端的代码相同的字符集。
Read this. http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
读这个。 http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
#3
0
Change column data type
更改列数据类型
to VARBINARY and it's automatically convert the latin1 datas
到VARBINARY,它会自动转换latin1数据
Thank you guys. I hope it's helpful.
感谢你们。我希望它有用。
#1
16
I managed to solve it by running updates on text fields like this:
我设法通过在这样的文本字段上运行更新来解决它:
UPDATE table SET title = CONVERT(CONVERT(CONVERT(title USING latin1) USING binary) USING UTF8)
#2
2
The situation isn't as bad as you think it is, unless you already have lots of non-Roman characters (that is, characters that aren't representable in Latin-1) in your database already. Latin-1 is a proper subset of utf8. Your web app works in utf8 and your tables' contents are in utf8 as well. So there's no need to convert the tables.
情况并不像你想象的那么糟糕,除非你的数据库中已经有很多非罗马字符(即拉丁文-1中无法表示的字符)。 Latin-1是utf8的合适子集。您的网络应用程序在utf8中工作,您的表格内容也在utf8中。所以没有必要转换表格。
So, try changing the SET NAMES latin1 to SET NAMES utf8. It will probably solve your problem, by allowing your php program's connection to work with the same character set as the code on either end of the connection.
因此,尝试将SET NAMES latin1更改为SET NAMES utf8。它可能会解决您的问题,允许您的php程序的连接使用与连接任一端的代码相同的字符集。
Read this. http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
读这个。 http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
#3
0
Change column data type
更改列数据类型
to VARBINARY and it's automatically convert the latin1 datas
到VARBINARY,它会自动转换latin1数据
Thank you guys. I hope it's helpful.
感谢你们。我希望它有用。