Change collation for all MySQL tables to UTF-8? Print

  • 0

Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have.

That's why we recommend using the following PHP script for changing the collation for all tables at a time:

$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $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 in the above script:

  • myuser_mydbname with your database name;
  • myuser_mydbuser with your mysql username;
  • mypassword with your password for the mysql user;
  • utf8-general_ci with your new collation if different;

Was this answer helpful?

« Back