Converting utf8_general_ci tables and fields to utf8_unicode_ci

Question

Hi Everyone,

I have made a mistake when designing my application database several years ago and collation settings of my tables and table fields are mixed. Some of them are utf8_general_ci and some of them are utf8_unicode_ci.

This causes problems when joining tables with different collations. Now, I am planning to change collation settings and make them the same: utf8_unicode_ci. I will be running these two SQL queries on all my tables.

ALTER TABLE  `table1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE  `table1` CHANGE  `Action`  `Action` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

My question is, does running these two SQL queries break any field values, especially the ones which contains accented characters? Or is it safe to run these two queries?

Looking forward to hear from you.

Thanks for your answers!

Answer 1

Collation only affects how strings within a field are compared, not how they're stored. As far as you stay with utf8 encoding, you're safe.

Answer 2

please refer mysql doc which talks about changing character set and collation

http://dev.mysql.com/doc/refman/5.1/en/charset-column.html

Its saying that - "If you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss."

--Cheers