2

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)

2 Answers2

0
  • 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.

JYelton
  • 35,664
  • 27
  • 132
  • 191
  • i am not sure i can install software on production server :(. Are there any way to do it with phpmyadmin? – dynamic Dec 28 '10 at 23:02
  • I'm not familiar with phpmyadmin but check this thread: http://stackoverflow.com/questions/153706/change-default-collation-in-phpmyadmin – JYelton Dec 29 '10 at 00:55
  • ok thanks here the queries: mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"); $res = mysql_query("SHOW TABLES FROM `$dbname`"); while($row = mysql_fetch_row($res)) { $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"; mysql_query($query); $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"; – dynamic Dec 29 '10 at 10:38
0

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`

Parth Bera
  • 1
  • 1