php mysql collation change script

Written by james on January 4, 2012 — 5 Comments

There are times when we get stuck with bad collations on our mysql tables and we are overwhelmed by having to change them by hand. Well, I have written a php script which will go through all your tables and fields in a database and update them to the collation which works for you. In my case for this example, I have a database which contains a mix of collations and changes them all to utf8_unicode_ci. This example uses the mysqli object in php as the database connection.

Your will need to update the database connection credentials as they are all bogus in this example. You will also need to change the “$execute_sql” to equal true if you want the script to actually perform the collation change via this script.

<?php
 
	$execute_sql = false;
 
	$host = 'mydbhost';
	$username = 'username';
	$password = 'passsword';
	$dbname = 'databasename';
 
	$db = new mysqli($host, $username, $password, $dbname);
 
	$mysqldatabase = 'databasename';
	$collation = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci';
 
	echo '<div>';
	$db->query("ALTER DATABASE $mysqldatabase $collation");
 
	$result = $db->query("SHOW TABLES");
 
	$count = 0;
	while($row = $result->fetch_assoc()) {
		$table = $row['Tables_in_'.$mysqldatabase];
		if($execute_sql) $db->query("ALTER TABLE $table DEFAULT $collation");
		$result1 = $db->query("SHOW COLUMNS FROM $table");
		$alter = '';
		while($row1 = $result1->fetch_assoc()) {
			if (preg_match('~char|text|enum|set~', $row1["Type"])) {
				if(strpos($row1["Field"], 'uuid')){
					// why does this not work
				}else{
					$alter .= (strlen($alter)?", \n":" ") . "MODIFY `$row1[Field]` $row1[Type] $collation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : "");
				}
			}
		}
		if(strlen($alter)){
			$sql = "ALTER TABLE $table".$alter.";";
			echo "<div>$sql\n\n</div>";
			if($execute_sql) $db->query($sql);
		}
		$count++;
	}
	echo '</div>';
 
?>

The only gotcha that I have run into with my script that I have yet to solve is that when you change the collation of an existing uuid field which has a foreign key, the copy table fails in mysql. Not that the collation of a uuid field really matters, but you can see a comment in the routine which questions why this does not work.

Anyway, I hope someone finds this useful =) Enjoy…

Posted in mysql, PHP | Tagged , , ,

5 Comments to “php mysql collation change script”

You can follow all the replies to this entry through the comments feed.

  1. Jonathan Amend's Gravatar Jonathan Amend
    January 13, 2012 at 10:23 am | Permalink

    Great script! I’ve made a few changes and ran it on my databases to convert older utf8_general_ci collations to utf8_unicode_ci.

    The reason you get “could not rename table …” errors is because MySQL requires foreign key columns to have both the same type and collation, so changing the collation only on one side will be invalid. You can however easily get around this by setting foreign_key_checks = 0.

    I also noticed there were some if($execute_sql)’s missing before ALTER statements.

    (hopefully this won’t get mangled)

    <?php
$execute_sql = false;
 
$host = 'mydbhost';
$username = 'username';
$password = 'passsword';
$dbname = 'databasename';
 
$db = new mysqli($host, $username, $password, $dbname);
 
$collation = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci';
$collationPK = 'CHARACTER SET utf8 COLLATE utf8_bin';
 
$result = $db->query("SET foreign_key_checks = 0");
 
echo '<div>';
if($execute_sql) $db->query("ALTER DATABASE $dbname $collation");
 
$result = $db->query("SHOW TABLES");
 
$count = 0;
while($row = $result->fetch_assoc()) {
	$table = $row['Tables_in_'.$dbname];
	if($execute_sql) $db->query("ALTER TABLE $table DEFAULT $collation");
	$result1 = $db->query("SHOW FULL COLUMNS FROM $table");
	$alter = '';
	while($row1 = $result1->fetch_assoc()) {
		if (preg_match('~char|text|enum|set~', $row1["Type"])) {
			// support a different collation for primary keys
			if ($row1["Key"] == "PRI" || $row1["Key"] == "MUL") {
				$newCollation = $collationPK;
			} else {
				$newCollation = $collation;
			}
			// check if we actually need to change the collation
			$alter .= (strlen($alter)?", n":" ") . "MODIFY `$row1[Field]` $row1[Type] $newCollation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : "");
		}
	}
	if(strlen($alter)){
		$sql = "ALTER TABLE $table".$alter.";";
		echo "<div>$sqlnn</div>";
		if($execute_sql) {
			if ($db->query($sql)) {
				echo "<div>OK</div>";
			} else {
				echo "<div style="color: #f00;">Error: $db->error</div>";
			}
		}
	}
	$count++;
}
echo '</div>';
  2. abg's Gravatar abg
    January 19, 2012 at 1:49 am | Permalink

    Hi,
    I put together this website energySPACE.net in 2010, and therefore am very interested in Honest Buildings and am trying to get hold of Kubiak. Can you please tell me how to. I dont want to post my email, not sure if this is private, but if you can give me some clues as how to contact him I would be very grateful. Thanks.

  3. james's Gravatar james
    January 19, 2012 at 8:37 am | Permalink

    You can use our contact form at http://www.honestbuildings.com/contact

  4. CottageStuff's Gravatar CottageStuff
    May 9, 2012 at 7:58 am | Permalink

    Some feedback: Ran Jonathan’s version of the script and I notice that the collation of individual fields (cells) has not been changed.

  5. james's Gravatar james
    May 10, 2012 at 5:53 pm | Permalink

    Hmmm, when i get some time I will try and update my version to reflect his changes and fix the issue you describe…

Leave a Reply

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

«
»