How to change the collation for all tables in MySQL database

unicornio · Involved Joined: Aug 13, 2009 Posts: 432

Hi to all of you. Today I created this little script in order to convert character latin1 to utf8. My question if I need to change anything else. I want to make a logo for that and change the abstraction if you can help me a little bit. What do you think. I tested and it is working properly. Very Happy

Code:

<?php
header('Content-type: text/plain;');
mysql_connect('localhost or IP','usernameofdatabase','password');
mysql_select_db('databasename');
$res = mysql_query("SHOW TABLES");
while ($r = mysql_fetch_array($res)){
$tablename = $r[0];
echo $sql = "ALTER TABLE $tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
echo "\r\n";
mysql_query($sql);
}
?>

Another way to do it

How can I change my MySQL database collation from phpMyadmin?

Usually you will be interested in changing your MySQL collation in order to solve problems with foreign character encodings. The most common case is to change your MySQL collation from latin1 to utf8. This can be done by using phpMyAdmin and following the instructions below:

1. Enter your cPanel and click on the phpMyAdmin icon in the Databases box.

2. Select the database you wish to manage from the drop-down menu on the left

3. Click on the Operations tab in the top menu of your phpMyAdmin

4. At the bottom of the page you will see the collation option. You can now select a collation from the drop down menu and click on the Go button.

Please note that after your change the collation of a database only the new tables will be created with the new collation. All other tables remain with the collation, they were initially created

montego · Posted: Sat Apr 17, 2010 4:59 pm

unicornio, you may be able to dump the table contents, change the collation, and then when importing back in, select the collation. I could have sworn I'd seen that process described somewhere. But, test it locally using XAMPP or similar environment FIRST. There will always be some characters/issues. One issue is even HTML entities. When you use true UTF-8, one would rather use the character rather than the entity, but some software, like phpBB2, saved certain entities within the data.

unicornio · Involved Joined: Aug 13, 2009 Posts: 432

Hi montego, I did it with this script it was really easy. can you test the script in order to know if we need to change anything. The only problem with this script you need to download the files which contains the whole tables converted, if you dont do that then you wont be able to convert it. can u please help me a little bit to improve this script. Thanks for your explanation.

montego · Posted: Sun Apr 18, 2010 3:21 pm

unicornio, actually, all my sites are in english so I am not able to assist with any kind of testing. Sorry.