How to change the collation for all tables in a MySQL database to UTF-8?


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;