Instantly share code, notes, and snippets.

Embed
What would you like to do?
Change mysql collation from latin to utf8 - all tables and all columns
<?php
/*
Description: Change all tables and all columns from latin* to utf8 collation
*/
error_reporting(E_ALL);
$log = '';
$db = mysql_connect('localhost','user','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('project'); $result=mysql_query('show tables');
echo "<pre>";
while($tables = mysql_fetch_array($result))
{
foreach ($tables as $key => $table)
{
echo "---------$table------------ \n";
$res = mysql_query("SHOW FULL COLUMNS FROM $table");
while ($columns = mysql_fetch_assoc( $res ) )
{
print_r($columns);
foreach ($columns as $ckey => $val)
{
if( preg_match('/latin/', $columns['Collation']) != false )
{
echo "changing $val \n";
$query = "ALTER TABLE $table CHANGE ".$columns['Field']." ".$columns['Field']." ".$columns['Type']." CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT ".($columns['Default'] ? $columns['Default'] : 'NULL');
mysql_query($query) or $log += "Can't change table: $table column: $column Collation \n";
}
}
}
}
}
echo "The collation of your database has been successfully changed!".( !empty($log) ? 'but with errors': '');
if ($log) echo $log;
echo "</pre>";
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment