Skip to content

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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.