Friday, January 11, 2013

MySQL Garbled Foreign Character Fix

Problem:
Working with a client to migrate their data into a better planned and more normalized database structure, we ran into the issue of foreign characters getting messed up once in the new system due to failing to set the correct character type.

Solution:
Luckily, Michael Chu had an awesome solution. Thanks, Michael! As he mentions in his solution, this doesn't always seem to work, but I had a lot of luck with it.

For varchar fields:

ALTER TABLE table_name MODIFY `field_name` BINARY(255);
ALTER TABLE table_name MODIFY `field_name` VARCHAR(255) CHARACTER SET utf8;

For text fields:
ALTER TABLE table_name MODIFY `field_name` BLOB;
ALTER TABLE table_name MODIFY `field_name` TEXT CHARACTER SET utf8;

Note:
There have been times when this does not work and I've also read that using the CONVERT keyword can help make the process better.

In some cases, I have had to run this to make it appear correctly.

UPDATE table_name SET col_name = CONVERT(CONVERT(CONVERT(col_name USING latin1) USING binary) USING utf8);

No comments: