Knowledgebase: MySQL
How to change the collation for all tables in a MySQL database?
Posted by Bill Kish, Last modified by Dick DeVance on 08 November 2009 05:23 PM
Changing the collation for all tables in a MySQL database can be time consuming. However, this task can be completed quickly using the following PHP script:

<?php
$db = mysql_connect('localhost','cpaneluser_dbuser','dbpassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('cpaneluser_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!";
?>

Make sure to substitute the following in the script:
- cpaneluser_dbname with your database name
- cpaneluser_dbuser with your mysql username
- dbpassword with your password for the mysql user
- utf8-general_ci with your needed collation if different
Comments (0)