A script for converting your MySQL database collation to utf8 and utf8_general_ci and table columns to uf8_bin in Drupal
I recently had to migrate a database from one server to another. The new server was MySQL 5.xxx and I got the chance to change the collation and character set of my database. Being of Swedish origin there's always a hassle with å,ä,ö letters. So now I wanted to be certain of full compliance with everything after some googling i found some code here which did almost what I wanted except it wasn't for Drupal and treated blobs as having a collation(I think I got it right) which it doesn't. Rewriting the script I finally came up with this script read to copy and paste into a page and run.
It turns all your tables into utf8_general_ci and all your columns into utf8_bin. I don't know the usefulness of having utf8_bin instead of utf8_general_ci for the columns but I assume they are right.
Also I found this bug in phpMyAdmin after doing the collation or migration which messed up the search-the-whole-database function - Read more about it here there's also a link on the page to a fix.
And so here is the code
<?php
# Do not change anything below this ( :-) <- Rewwrite Editors note.)
$sql = 'SHOW TABLES';
if ( !( $result = db_query( $sql ) ) ) {
echo '<span style="color: red;">Get SHOW TABLE - SQL Error: <br>' . "</span>\n";
}
while ( $tables = db_fetch_array($result) ) {
echo $tables[0];
# Loop through all tables in this database
$table = $tables[key($tables)];
if ( !( $result2 = db_query("ALTER TABLE %s COLLATE utf8_general_ci", $table) ) ) {
echo '<span style="color: red;">UTF SET - SQL Error: <br>' . "</span>\n";
break;
}
print "$table changed to UTF-8 successfully.<br>\n";
# Now loop through all the fields within this table
if ( !($result2 = db_query("SHOW COLUMNS FROM %s",$table) ) ) {
echo '<span style="color: red;">Get Table Columns Query - SQL Error: <br>' . "</span>\n";
break;
}
while ( $column = db_fetch_array( $result2 ) )
{
$field_name = $column['Field'];
$field_type = $column['Type'];
# Change text based fields
$skipped_field_types = array('char', 'text', 'enum', 'set');
foreach ( $skipped_field_types as $type )
{
if ( strpos($field_type, $type) !== false )
{
$sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type CHARACTER SET utf8 COLLATE utf8_bin";
$result4 = db_query($sql4);
echo "---- $field_name changed to UTF-8 successfully.<br>\n";
}
}
}
echo "<hr>\n";
}
?>