
MySQL – Convert several tables to utf8 encoding
For those who, like need to convert all tables of a MySQL database (MariaDB should work too), please find below a very simple and efficient PHP script.
As I like to keep things simple and clear, and don’t want to install PHPmyAdmin or anything else on my database server.
Create a file named php convert_tables_utf8.php on your server then copy and the script content below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?php $dbname = "mydb"; $dbuser = "myuser"; $dbpassword = "mypassword"; $con = mysql_connect('localhost',$dbuser,$dbpassword); if(!$con) { echo "Cannot connect to the database "; die(); } mysql_select_db($dbname); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $table) { echo "Converting table $table\n"; mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"); } echo "The collation of your database $dbname has been successfully changed!"; } ?> |
Adapt the variables to your environment and convert all database tables by running the script as below
1 |
$ php convert_tables_utf8.php |
If the script complain about missing PHP modules, make sure you’ve installed PHP MySQL extensions.
Checking the warnings and/or error in your terminal should give you more information.
Enjoy 🙂