Your Trusted Hosting Solutions Partner > Knowledgebase > Database > MySQL > How to change all MySQL database table collation

How to change all MySQL database table collation

After change the collation of a database, only the new tables will be created with the new collation. To change all the database collation, you can use the php script as below as it can change all the collation for all the existing table.

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

Note:
database_user : Database Username
Password: Database user password
database_name: Database Name
Collation : new collation

Add to Favourites    Print this Article

Connection Strings for MySQL (Views: 1805)