Mondoze Knowledge Base

Search our articles or browse by category below

How to change all MySQL database table collation

Last modified: October 7, 2022
You are here:
Estimated reading time: < 1 min

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. 

$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

Was this article helpful?
Dislike 0
Views: 190