Migrating to Unicode

[Claus Jensen]

Im currently trying to change all our datbases, tables and columns to unicode since the company i work for is planning to expand.

I have a script which i use to change all this for me.

$server = "xxx";
$username = "xxx";
$password = "xxx";
$database = "xxx";

mysql_connect($server, $username, $password) or die("cannot connect to db");
mysql_select_db($database) or die("cannot select database");

$sql = "ALTER DATABASE " . $database . " CHARACTER SET UTF8;
ALTER DATABASE " . $database . " COLLATE utf8_unicode_ci;";
print $sql ."<br><br>";
mysql_query($sql);

$result = mysql_query("SHOW TABLES");

while ($row = mysql_fetch_array($result)){
$tablename = $row[0];
$sql = "ALTER TABLE `" . $tablename . "` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
print "<br>" . $sql;
mysql_query($sql);
}

The script worked fine until i saw some tables was not converted.

Problem is that when i try and convert a field (username) which is UNIQUE of the type varchar i get a error : Duplicate entry 'matte' for key 2

This happends because : utf8_unicode_ci a = å = â etc. You cant have a user called måtte and matte at same time.

The issue is to upgrade my user table with UNIQUE usernames.

I read a discussion about this being a bug (http://bugs.mysql.com/bug.php?id=19567), and i tried to find a solution for this problem but with no luck.

I also tried to look into to the utf8_bin instead. But that will cause other problems since f!=F (case sensitive). You can have both a user called flemming and Flemming.

Anyone have any nice solution for this kinda problem?

I would like to have everything in UTF8 when im done, so we dont run into any character issues in the future.

Regards Claus