I found an answer on this website: http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html
I cleaned up the code to print out better from a linux shell and added the $new_charset and $new_collation variables to the alter database statement.
vi change_collation_everything.php
<?php
// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'root';
$password = 'password';
$database = 'database';
$new_charset = 'latin1';
$new_collation = 'latin1_general_cs';
// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());
// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");
// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
$table = $tables[0];
mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");
// loop through each column changing collation
$columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
while($cols = mysql_fetch_array($columns)) {
$column = $cols[0];
$type = $cols[1];
mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
print "changed $table collation of $column to $new_collation\n";
}
print "changed collation of $table to $new_collation\n";
}
print 'The collation of your database has been successfully changed!\n';
?>
I tested the answer on my own database changing from charset latin7 to latin1 and latin7_general_cs to latin1_general_cs for the collation.
I chose _cs instead of _ci for the collation because if you choose _ci which is the default collation for charset latin_1, the collation no longer shows up in the show create table tablename column output.
Try it out
$> php change_collation_everything.php
You can see all of the available charsets and their corresponding collations with the show collation sql query