Converting latin mysql data to utf8

Question

I want to use utf 8 right now , but all my data is latin1 , what is the efficient way to convert data . Also I know how to change database's structure(charset) to utf8 , What I want to do is changing charset of existing data .

update

Here are my old setting ,

1. Html output : utf8
2. Html input : utf8
3. Php - mysql connection : latin1
4. mysql (fields and tables) : latin1

Here are my new settings , and I hope this is the best way to create multi-language website

1. Html output : utf8
2. Html input : utf8
3. Php - mysql connection : utf8
4. sql (fields and tables) : utf8

Answer 1

Your best solution to to create a new database called dbname_new do a SQL dump from your old database.

Then take that dump and replace the charset info with your new utf8 data, and make sure you resave the sql file itself as utf8.

Then load that back into the new database, check everything worked OK and then rename it.

This can be a lengthy process over the 'net so i recomend you do it via a ssh shell session, and take full advantage of bash pipes and the like.

Answer 2

Do you actually use the latin1 part, or is your data actually ASCII?

It would seem that there's a command for this:

• http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

...but be careful, I also found this:

• http://bugs.mysql.com/bug.php?id=21681

Failing the command that seems to be there for this sort of thing, an alternative might be to dump the table(s) to a file, convert that, and then re-import that. (Or, if you can convince it to dump to UTF-8, even better...)

There seems to be a lot of information out there for this: http://www.google.com/search?q=mysql+convert+table+to+utf8

Answer 3

If you can/want to live with data stored as latin1, but just want to present it as UTF-8, specifying UTF-8 as the connection character set should work too. One way to test this is to issue the query

SET NAMES 'utf8'

once you establish a connection, before reading/writing any data.

More details on this here http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Answer 4

If you apply utf8_encode() to an already UTF8 string it will return a garbled UTF8 output.

I made a function that addresses all this issues. It´s called forceUTF8().

You dont need to know what the encoding of your strings is. It can be Latin1 (iso 8859-1) or UTF8, or the string can have a mix of the two. forceUTF8() will convert everything to UTF8.

I did it because a service was giving me a feed of data all messed up, mixing UTF8 and Latin1 in the same string.

Usage:

$utf8_string = forceUTF8($utf8_or_latin1_or_mixed_string);

$latin1_string = forceLatin1($utf8_or_latin1_or_mixed_string);

I've included another function, fixUFT8(), wich will fix every UTF8 string that looks garbled.

Usage:

$utf8_string = fixUTF8($garbled_utf8_string);

Examples:

echo fixUTF8("Fédération Camerounaise de Football");

echo fixUTF8("Fédération Camerounaise de Football");

echo fixUTF8("FÃÂédÃÂération Camerounaise de Football");

echo fixUTF8("Fédération Camerounaise de Football");

will output:

Fédération Camerounaise de Football

Fédération Camerounaise de Football

Fédération Camerounaise de Football

Fédération Camerounaise de Football

Update: I converted theese into a static class, and they live in Github now:

https://github.com/neitanod/forceutf8

Answer 5

Excellent resource on the subject:

Turning MySQL data in latin1 to utf8 utf-8

Answer 6

You need to change collation (to utf-8) . Here is script to do that easily. http://blog.vision4web.net/2008/11/change-collation-on-all-tables-and-columns-in-mysql/ I have experience with this script , it works perfectly