How to convert old database in mysql form latin1 to utf8

Question

I have a database in latin1 format, all the utf8 character stored are shown as ????

 +------+---------+-------+---------+--------------------+----------+--------------------      -----+---------------------+---------------------+---------+
 | id   | user_id | fname | lname   | designation        | location | email                    | created_at          | updated_at          | country |
 +------+---------+-------+---------+--------------------+----------+------------------------- +---------------------+---------------------+---------+
 | 6035 |    6035 | ????? | ??????? | ???????? ????????? |          |  [email protected]            | 2011-04-11 06:05:54 | 2011-04-10 06:13:04 | xxxxxxxxx |
 +------+---------+-------+---------+--------------------+----------+-------------------------+---------------------+---------------------+---------+

Now I use this command and change the format of the database and the table to utf8

  ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;

  ALTER DATABASE <database_name> CHARACTER SET utf8;

I have read that latin1 uses 1byte for every character but utf8 uses 3bytes for every character. My question is If i alter my table (Already containing lots of data) form latin1 to utf8, what will the old character data consume 3bytes or 1byte. If i use alter and convert the data will i have problem with the old data ? I am sure that new data will be in utf8.

Guillaume USE Guillaume USE · Accepted Answer · 2012-08-08T12:06:37

first, you should try :

SET NAMES 'utf8'
SET CHARACTER SET utf8

and SELECT your row #6085 in order to verify if data recorded are not corrupted and encoded in UTF8 format.

UTF8 (unlike UTF16), in order to be backward compatible, uses 1 byte for ASCII characters. It uses up to 4 bytes for other characters (unicode faq).

You should not convert your data if they are already stored in UTF8 format.

Warning

Try your ALTER TABLE on a backup.
ALTER TABLE locks your database.

How to convert old database in mysql form latin1 to utf8

1 Answers