import utf-8 mysqldump to latin1 database

Question

I have a dump file of a phpnuke site somehow in utf8. I'm trying to reopen 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 SET NAMES 'utf8'; statement at the beginning of your dump. This will indicate to MySQL that the commands it is about to receive are in UTF8. It will store the data in whatever character set your tables are currently set in; in this case if your database is in latin1, data will be stored in latin1.

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

SET NAMES indicates what 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 character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

One last thing is that latin1 has much less characters available than utf8. For anything other than the western European languages, you will lose data.

For instance, assuming column test is latin1. The first entry will appear correctly (the French accent is within latin1); however the second entry in Korean will show as question marks.

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