I can’t remember what all I looked at for references, but this completed tool will generate the SQL commands necessary to convert your database, tables and fields from any collation and character set into another.
It goes without saying to make a backup of your database before running such commands. If you don’t make a backup first, shame on you 😛
<?php
function show_error($string) {
header('Content-Type: text/plain');
echo $string;
exit(1);
}
function db_connect() {
if ( ! mysql_connect($_POST['host'], $_POST['user'], $_POST['pass']) || ! mysql_select_db($_POST['name'])) {
show_error(mysql_error());
}
}
if (empty($_POST['host']) || empty($_POST['user']) || empty($_POST['pass']) || empty($_POST['name'])) {
?>
<form method="post">
<fieldset>
<legend>Database</legend>
<label>Host</label>
<input type="text" name="host" value="<?php echo (empty($_POST['host'])) ? 'localhost' : htmlentities($_POST['host']); ?>">
<label>User</label>
<input type="text" name="user" value="<?php echo htmlentities($_POST['user']); ?>">
<label>Pass</label>
<input type="text" name="pass" value="<?php echo htmlentities($_POST['pass']); ?>">
<label>Name</label>
<input type="text" name="name" value="<?php echo htmlentities($_POST['name']); ?>">
<input type="submit" value="Go">
</fieldset>
</form>
<?php
} elseif (empty($_POST['collate']) || empty($_POST['charset']) || !isset($_POST['alter_database']) || !isset($_POST['alter_tables']) || !isset($_POST['alter_fields'])) {
db_connect();
$collations = array();
$sql = 'SHOW COLLATION WHERE `Compiled` = \'Yes\'';
$rs = mysql_query($sql);
while ($row = mysql_fetch_assoc($rs)) {
$collations[] = $row['Collation'];
}
if (count($collations)) {
sort($collations);
} else {
show_error('NO RESULTS FOR: ' . $sql);
}
$charsets = array();
$sql = 'SHOW CHARACTER SET';
$rs = mysql_query($sql);
while ($row = mysql_fetch_assoc($rs)) {
$charsets[] = $row['Charset'];
}
if (count($charsets)) {
sort($charsets);
} else {
show_error('NO RESULTS FOR: ' . $sql);
}
?>
<form method="post">
<fieldset>
<legend>Configuration</legend>
<label>Collation</label>
<select name="collate">
<option><?php echo implode('</option><option>', $collations); ?></option>
</select>
<label>Character Set</label>
<select name="charset">
<option><?php echo implode('</option><option>', $charsets); ?></option>
</select>
<label>Show Database Alterations</label>
<select name="alter_database">
<option value="1">Yes</option>
<option value="0"<?php echo (isset($_POST['tables']) && $_POST['tables'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
</select>
<label>Show Table Alterations</label>
<select name="alter_tables">
<option value="1">Yes</option>
<option value="0"<?php echo (isset($_POST['tables']) && $_POST['tables'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
</select>
<label>Show Field Alterations</label>
<select name="alter_fields">
<option value="1">Yes</option>
<option value="0"<?php echo (isset($_POST['fields']) && $_POST['fields'] == '0') ? ' selected="selected"' : ''; ?>>No</option>
</select>
<input type="submit" value="Go">
</fieldset>
<input type="hidden" name="host" value="<?php echo htmlentities($_POST['host']); ?>">
<input type="hidden" name="user" value="<?php echo htmlentities($_POST['user']); ?>">
<input type="hidden" name="pass" value="<?php echo htmlentities($_POST['pass']); ?>">
<input type="hidden" name="name" value="<?php echo htmlentities($_POST['name']); ?>">
</form>
<?php
} else {
db_connect();
set_time_limit(0);
$convert_to = $_POST['collate'];
$character_set = $_POST['charset'];
$show_alter_database = (bool) $_POST['alter_database'];
$show_alter_table = (bool) $_POST['alter_tables'];
$show_alter_field = (bool) $_POST['alter_fields'];
$database = mysql_real_escape_string($_POST['name']);
header('Content-Type: text/plain');
echo "/* DATABASE: $_POST[name] */\n";
echo "/* COLLATE: $convert_to */\n";
echo "/* CHARSET: $character_set */\n";
echo "/* ---------------------------------------- */\n";
if ($show_alter_database) {
$rs = mysql_query("SHOW CREATE DATABASE `$database`") or show_error(mysql_error());
$row = mysql_fetch_assoc($rs);
if (strpos($row['Create Database'], "CHARACTER SET $character_set") === FALSE) {
echo "ALTER DATABASE `$database` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\n";
}
}
$rs_tables = mysql_query('SHOW TABLES') or show_error(mysql_error());
while ($row_tables = mysql_fetch_row($rs_tables)) {
$table = mysql_real_escape_string($row_tables[0]);
if ($show_alter_table) {
$rs = mysql_query("SHOW CREATE TABLE `$table`") or show_error(mysql_error());
$row = mysql_fetch_assoc($rs);
if (strpos($row['Create Table'], "DEFAULT CHARSET=$character_set") === FALSE) {
echo "ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\n";
}
}
if ($show_alter_field) {
$rs = mysql_query("SHOW FULL FIELDS FROM `$table`") or show_error(mysql_error());
while ($row = mysql_fetch_assoc($rs)) {
if ($row['Collation'] == '' || $row['Collation'] == $convert_to) {
continue;
}
$nullable = ($row['Null'] == 'YES') ? 'NULL' : 'NOT NULL';
if ($row['Default'] === NULL && $row['Null'] == 'YES') {
$default = ($nullable == 'NOT NULL') ? '' : ' DEFAULT NULL';
} elseif ($row['Default'] != '') {
$default = ' DEFAULT \'' . mysql_real_escape_string($row['Default']) . '\'';
} else {
$default = '';
}
$field = mysql_real_escape_string($row['Field']);
echo "ALTER TABLE `$table` CHANGE `$field` `$field` LONGBLOB;\n";
echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable$default;\n";
}
}
}
echo "/* ---------------------------------------- */\n";
}
- You have to provide your MySQL connection information (duh)
- Select the desired collation, and character set
- Select whether to show database, table and/or field alterations
- The SQL code is output to the screen for review
Note: Collations and character sets are automatically retrieved from the MySQL server 😉
I used to go through phpMyAdmin and do this manually on each table and field… seriously. This tool could easily save you an hour or two having to do that with a robust database schema 😀