converting text field from latin1_swedish to utf8?

Hi guys is there a safe way to convert a table from latin1_swedish to fully utf8?

Someone can explain me which are the steps to safely do it?

开发者_开发技巧

(maybe using phpmyadmin dunno)

---

• Grab Sqlyog Community Edition.
• Make a backup of your database.
• Press F6 on the tables you need to edit.
• Disable the "Hide Language Options" checkmark in the bottom right if needed.
• Change the charset and collation as needed.

I am not sure of any specific pitfalls in changing to/from utf8, but the backup should serve you well should anything go wrong.

---

I assume latin1_swedish_ci is default collation of your database server. In such case i use the following collations:

• Database: defaults (latin1 & latin1_swedish_ci)
• Table: defaults (latin1 & latin1_swedish_ci)
• Column: utf8 & utf8_unicode_ci whenever needed defaults (latin1 & latin1_swedish_ci) otherwise.

There can be number of solutions to this problem, but the following solution worked for me in some worst case scenarios too.

Steps:

1. Copy the structure to new table.
2. Change the charset & collation of table fields to utf8 & utf8_unicode_ci respectively as needed.

3. Fill the new table with data in old table, using CONVERT.

   --Copy table structure

   CREATE TABLE `table_name` LIKE `old_table_name`;

   --Change collations as you like

   ALTER TABLE `table_name`

   CHANGE `column_name1` `column_name1` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,

   CHANGE `column_name2` `column_name2` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;

   -- Import the data

   INSERT INTO `table_name` (`id`,`column_name1`,`column_name2`,`column_name3`)

   SELECT `id`,

   CONVERT(CONVERT(_latin1 `column_name1` USING binary) USING utf8) collate utf8_unicode_ci,

   CONVERT(CONVERT(_latin1 `column_name2` USING binary) USING utf8) collate utf8_unicode_ci,

   `column_name3`

   FROM `old_table_name`