Import mysqldump utf-8 database to latin1

Question

I have a phpnuke site dump file somehow in utf8. I am trying to open this site on a new server. But Nuke uses latin1. I need a way to create a latin1 database using this utf-8 dump file. I tried everything I could think of. iconv, mysql replace, php replace ...

Answer 1

Add the statement SET NAMES 'utf8'; at the beginning of your dump. This will tell MySQL that the commands it is about to receive are in UTF8. It will store data in any character currently set in your tables; in this case, if your database is in latin1, the data will be stored in latin1.

From http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html :

SET NAMES indicates which character set the client will use to send SQL statements to the server. Thus, SET NAMES "cp1251" tells the server, "future incoming messages from this client are in the cp1251 character set." It also indicates the character set that the server should use to send the results back to the client. (For example, it indicates which character should be used for column values ​​if you use the SELECT statement.)

The last thing latin1 has far fewer characters available than utf8. For anything other than Western European languages, you will lose data.

For example, if the test column is latin1. The first entry will appear correctly (French accent is in Latin); however, the second Korean entry will appear as question marks.

 SET NAMES 'utf8'; INSERT INTO TESTME(test) VALUES ('Bienvenue sur Wikipédia'); INSERT INTO TESTME(test) VALUES ('한국어 위키백과에 오신 것을 환영합니다!');