How to change the collation for 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:
<?php
$db = mysql_connect('localhost','DBUserName','DBPassword');
if(!$db) echo "Cannot connect to the database – incorrect details";
mysql_select_db('DBName'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_unicode_ci");
}}
echo "The collation of your database has been successfully changed!";
?>
Make sure to substitute in the above script:
- DBName with your database name;
- DBUserName with your Database User Name;
- Password with your password for the mysqldatabase user;
- utf8-general_ci with your new collation if different;
- NOTE: MySQL user must have administrative rights to do this.