Fixing UTF-8 characters which are stored in Latin1 or other encoding

September 8th, 2010 by Alex Leave a reply »

I noticed this issue while working on US-based servers. There’s usually ISO-8859-1 encoding in MySQL by default, so if you accidentally didn’t change that and worked with russian or other than english characters, the data may be presented as:

Коллекция стоковых фотографий

Which in my case was converted upon output to

Коллекция стоковых фотографий

This is ok if you need to just display it, but if you need to perform search or sort or any other mysql operations, you will FAIL. The fix for this is to convert the table column to binary, then back to UTF8. This way the data remains untouched but in proper encoding.

SET NAMES latin1; 
ALTER TABLE links MODIFY COLUMN link_title TEXT CHARACTER SET latin1; 
ALTER TABLE links MODIFY COLUMN link_title BLOB; 
ALTER TABLE links MODIFY COLUMN link_title TEXT CHARACTER SET utf8; 
SET NAMES utf8;

After that, don’t forget to always “SET NAMES utf8” in your php application if you don’t have access to php.ini. It fixed my issues like magic and i continued working with same data without any loss.

This page can be found by searching for:

unscrambling utf8



Trackbacks /
Pingbacks

  1. My Blog Title