change encoding of database from latin1_swedish_ci to utf8

Question

I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation.

My question is about the consistency of the information. When I do this change it is possible corrupt the data that is in database?

something like this:

mysql> alter database user_db default character set utf8 collate utf8_unicode_ci;

there is any risk of changing the information?

RolandoMySQLDBA · Accepted Answer · 2012-09-18 00:03:07Z

up vote 1 down vote accepted

If you have any stored procedures that use database defaults, you must drop and recreate those stored procedures.

If the ALTER DATABASE command runs quickly, chances are the tables were not altered.

You may have to run ALTER TABLE on all your tables as follows:

ALTER TABLE tblname CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

answered Sep 18 '12 at 0:03

RolandoMySQLDBA
41.2k528108

	I don't know, but I gave a +1 for being a good question. My sense is that Rolando is right, changing the databases default collation would only affect new tables being created, you'll still have to alter all your tables. – ers81239 Sep 18 '12 at 11:21
	A common technique would be to create an identical table structure with the new collation and then do a INSERT INTO new_table_name SELECT * from old_table_name. Then you can inspect the results of the collation change. – ers81239 Sep 18 '12 at 11:22
	In the conversion I got errors in the enums when I have at least, the character ç and/or ã. – Fel Sep 18 '12 at 16:11
	A Collation have the same characteristics as ENUM: You cannot shift the order with impunity ( dba.stackexchange.com/a/6966/877 ) – RolandoMySQLDBA Sep 18 '12 at 16:14

migrated from stackoverflow.com Sep 17 '12 at 23:53