Change the collation for all tables in a MySQL database

How to change the database collation is easy, but how to change all the table collation? You can use script as below

This example is change all table collation to utf8_general_ci

<?php
$db = mysql_connect('localhost','dbuser','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('dbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>

Note:
dbuser= database user
password= database user password
dbname= database name

One Reply to “Change the collation for all tables in a MySQL database”

Leave a Reply

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