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.

One comment on “Convert all MySQL tables and columns to UTF8 and InnoDB

Leave a Reply

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

*
*
*