shoprex-icon-big

Shoprex

Developer site for the Wordpress Plugin Shoprex

Convert all MySQL tables and columns to UTF8 and InnoDB


Copy the following script into a php file. Fill in the 4 details and then execute.

<?php
 
$server = "z.B. localhost";
$user = "An account name that extended rights on the database has"";
$password = "The Password";
$database = "The selected database containing the tables";

echo "<html><head><title></title></head><body><pre>\n"; 
$db = mysql_connect($server, $user, $password);
if(!$db)
{
    echo "Error: Cannot connect to the database\n</pre></body></html>";
    die();
}
mysql_select_db($database);
$result = mysql_query('show tables');
$i = 0;
while($tables = mysql_fetch_array($result))
{
	$query = "ALTER TABLE `".$tables[0]."` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
	echo $query."\n";
	mysql_query($query);
	if(mysql_error())
	{
		echo "Error: ".mysql_error()."\n</pre></body></html>";
		die();
	}
	$query = "ALTER TABLE `".$tables[0]."` ENGINE=innodb";
	echo $query."\n";
	mysql_query($query);
	if(mysql_error())
	{
		echo "Error: ".mysql_error()."\n</pre></body></html>";
		die();
	}
	echo "\n";
	$i++;
}
echo "\nFinished! $i Tables.</pre></body></html>";

The InnoDB MyISAM is preferable because it offers more functions, eg can lock tables. InnoDB supports all functions provided by MyISAM, thus a conversion without risk. But as always: Make a backup!

On the subject of speed: I had been somewhat neglected, that all my tables and columns in the database have the same encoding (UTF-8) and engine (InnoDB) in use. It was all a bit mixed. Through the execution of my script, the charging time of my page has considerably accelerated.