Convert all your MySQL database text fields to unicode (or any other encoding)

Bookmark and Share

Have you ever faced a problem when the text fields in your database have different encoding? This usually happens when you import table structure from another database with a different encoding, or when you neglect the encoding at all, which I consider as a bad practice.
Later, this can cause some queries to fail specially if you are comparing two text fields. This may seem as a rare case but I ran into it enough times to write this post :D .

This code can either run from command line if you have access to your hosting command line and this is the preferred method to me due to increased performance. You also can use it from your web browser if you have a shared hosting or prefer a cleaner GUI to show results. If don’t have a hosting service yet you can head first to web hosting reviews for a professional comparison between hosting services.

You can download the file here or copy the following code directly.

<?php
$host = 'localhost';
$user = 'your_user_name';
$password = 'your_password';
$dbanme = 'your_database_here';

$link1 = mysql_connect($host, $user, $password);
mysql_select_db('information_schema', $link1);

$result = mysql_query("SELECT *  FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = '$dbname' AND `DATA_TYPE` in ('varchar', 'text')", $link1) or die(mysql_error($link1));

$link2 = mysql_connect($host, $user, $password);
mysql_select_db($dbname, $link2);
$success = $fail = array();
$tables = array();
while (($row = mysql_fetch_assoc($result)) != false){
  $tables[$row['TABLE_NAME']] = $row['TABLE_NAME'];
  $query = "ALTER TABLE `{$row['TABLE_NAME']}` MODIFY `{$row['COLUMN_NAME']}` {$row['COLUMN_TYPE']} CHARACTER SET utf8 COLLATE utf8_unicode_ci ";

   if ($row['IS_NULLABLE'] == 'YES'){
     $query .= 'NULL';
   } else {
     $query .= 'NOT NULL';
   }

  $res = mysql_query($query, $link2);
  if ($res){
    $success["{$row['TABLE_NAME']}.{$row['COLUMN_NAME']}"] = $query;
  } else {
    $fail["{$row['TABLE_NAME']}.{$row['COLUMN_NAME']}"] = mysql_error($link2);
  }
}

echo '<pre>', print_r(compact('success', 'fail'), true), '</pre>';

foreach ($tables as $table){
  mysql_query("ALTER TABLE `$table` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci", $link2);
}

mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");

For this code to work on your database don’t forget to change your database connection parameters.

You are welcome to contribute to the code at http://code.google.com/p/char2unicode/.

You also can test your skills in PHP by converting this code to use PDO or MySQLi or even introduce a code for other database providers.

Quick tip: If you want to select a php hosting with suitable prices and services you can find it at web hosting reviews.


4 comments on “Convert all your MySQL database text fields to unicode (or any other encoding)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>