Skip to content

Instantly share code, notes, and snippets.

@halilim

halilim/mysql_mass_change_charset.php

Last active December 22, 2015 02:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save halilim/6405786 to your computer and use it in GitHub Desktop.
Save halilim/6405786 to your computer and use it in GitHub Desktop.
Download ZIP
Mass MySQL database character set changer
Raw
mysql_mass_change_charset.php
<html>
<head>
<title>Mass MySQL database character set changer</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<pre><?php
$db_host = 'localhost';
$db_user = 'root';
$db_pass = 'root';
$db_name = 'db_name';
$to_charset = 'utf8';
$to_collate = 'utf8_turkish_ci';
// If false, only show the queries generated
$apply = false;
mysql_connect($db_host, $db_user, $db_pass);
mysql_query('set names '.$to_charset);
mysql_select_db($db_name);
$q='ALTER DATABASE `'.$db_name.'` CHARACTER SET '.$to_charset.' COLLATE '.$to_collate.';';
echo $q."\n\n";
if ($apply) {
mysql_query($q);
}
$res=mysql_query('show tables');
while ($table=mysql_fetch_array($res)) {
$table_name = $table[0];
$q='ALTER TABLE `'.$table_name.'` CHARACTER SET '.$to_charset.' COLLATE '.$to_collate.';';
echo $q."\n";
if ($apply) {
mysql_query($q);
}
$res2=mysql_query('describe '.$table_name);
while ($column=mysql_fetch_assoc($res2)) {
if (preg_match('/varchar|char|text|text|mediumtext|longtext/', $column['Type'])) {
$defStr = '';
if (is_null($column['Default'])) {
$defStr = ' DEFAULT NULL ';
} else if (!empty($column['Default'])) {
$defStr = ' DEFAULT \''.mysql_real_escape_string($column['Default']).'\' ';
}
$q='ALTER TABLE `'.$table_name.'` MODIFY COLUMN `'.$column['Field'].'` '.$column['Type'].' CHARACTER SET '.$to_charset.' COLLATE '.$to_collate.' '.$defStr.';';
echo "\t".$q."\n";
if ($apply) {
mysql_query($q);
}
}
}
}
?>
</pre>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment