All geek questions in one place

How to convert mysql latin1 to utf8

I have inherited a web system that I need to develop further. It seems that the system was created by someone who read two chapters of a PHP tutorial and thought he could write code ...

So ... the web page itself is in UTF8 and displays and enters everything into it. Database tables were created with the UTF8 character set. But in the config there is "SET NAMES LATIN1". In other words, UTF8 encoded strings are populated in the database using forced Latin encoding.

Is there a way to convert this mess to my own repository in utf8 and get rid of latin1?

I tried this , but since the database table is set to utf8, this does not work. Also tried this one without success.

Perhaps I was able to do this by reading all the tables in PHP encoded with latin1 and then writing them back to the new database in utf8, but I want to avoid this if possible.

+7
source share
3 answers

I was able to solve this problem by performing updates in the text fields:

UPDATE table SET title = CONVERT(CONVERT(CONVERT(title USING latin1) USING binary) USING UTF8)
+17
source

The situation is not as bad as you think if you already have many non-Roman characters (that is, characters that are not represented in Latin-1) in your database already. Latin-1 is its own subset of utf8. Your web application runs in utf8, and the contents of your tables are also in utf8. Therefore, there is no need to convert tables.

So try changing SET NAMES latin1 to SET NAMES utf8 . This will probably solve your problem by allowing your connection to the php program to work with the same character set as the code on either end of the connection.

Read this. http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

+2
source

Change column data type

to VARBINARY and it automatically converts latin1 data

Thanks guys. Hope this will be helpful.

0
source

Source: https://habr.com/ru/post/1246812/

More articles:


All Articles