How To Change Multiple MySQL Tables’ Collation At Once In Bulk

Using PHPMyAdmin to change table collations is a tedious task because you have to change each table one-by-one. In order to change ALL the tables’ collations at once, you’ll need to use a PHP script to help you.

The following code does the trick:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
$db_name	= "dbname"; 		//your database name
$db_username	= "dbusername";		//your database user
$db_password	= "dbpassword";		//your database user password
$db_host 	= "localhost";		//hostname of your database
$collation	= "utf8_general_ci";	//collation desired
 
$conn = mysql_connect($db_host,$db_username,$db_password);
 
if(!$conn) {
    echo "Cannot connect to the database";
} else {
	mysql_select_db($db_name); 
	$result = mysql_query("show tables");
	while ($tables = mysql_fetch_array($result)) {
		//iterate through tables
		foreach ($tables as $key => $value) {
			mysql_query("ALTER TABLE ".$value." COLLATE ".$collation);
		}
	}
}
?>

The only thing that you need to change is the first 5 lines of the code. Once done, save the code above as a PHP file, e.g. myfile.php, and upload it to your server via FTP. Next, use your browser to access the file, and once the page finishes loading, your tables’ collations should be changed.

, , , ,

About Site Fixit!

Sam is a professional web designer and web developer. He has over 15 years of experience with web-related technologies, and loves making things work.

View all posts by Site Fixit!

No comments yet.

Leave a Reply