Recently I’ve upgrade the mysql version to the latest 5.1.30.
I want to convert all collation into utf8_general_ci.
Googling around, I found script to list and alter all tables in just one particular database.
Modify it a bit. This is what came out. You can use and modified it to your needs. I hope it would be useful to you.
Warning: Run it with command line php! as it would generate script execution timeout on browser, especially on large list databases/tables (mine only 50 db and around 5k tables)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <?php mysql_connect("localhost","dbadminuser","dbadminpassword"); $dbs = mysql_query("SHOW DATABASES"); while ($baris = mysql_fetch_array($dbs)) { foreach ($baris as $node => $db) { mysql_select_db($db); // convert code $res = mysql_query("SHOW TABLES"); while ($row = mysql_fetch_array($res)) { foreach ($row as $key => $table) { mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"); echo $key . " => " . $table . " CONVERTED<br />"; } } }} ?> |
Unique visitors to post: 13