MySQL: Encoding issues – latin1 to utf8 – easy

There are many reasons why you should get the encoding right in the mysql columns. Unfortunately when you started it, the databases default encoding was in latin1 and now you want to convert to utf8 as per your needs. You can easily do it within the mysql.

Lets do this using an example. For this purpose we have a table called news. This used to have default encoding as latin1, so the column title varchar(255) and content text were storing values in latin1.

To change the values from latin1 to utf8 for the two string columns:

Step 1: set the original default encoding to the columns:

alter table news 
       modify title varchar(255) character set 'latin1', 
       modify content text character set 'latin1';

Step 2: now change the column type to binary types:

alter table news 
       modify title varbinary(255),
       modify content varbinary(65536);

Step 3: now convert to utf8:

alter table news 
        modify title varchar(255) character set 'utf8',
        modify content text character set 'utf8';

This is it, you should be all converted from latin1 to utf8. Also remember to pass the original attributes of the those columns again when using modify column.

1 Comment »

  1. Susheel Chandradhas said,

    August 17, 2008 @ 4:06 pm

    Yeah… Very easy. Maybe I should tak it up as a profession. :P

RSS feed for comments on this post · TrackBack URI

Leave a Comment