Skip to content

Instantly share code, notes, and snippets.

@keyurdg
Created March 18, 2013 18:58
Show Gist options
  • Save keyurdg/5189805 to your computer and use it in GitHub Desktop.
Save keyurdg/5189805 to your computer and use it in GitHub Desktop.
Generate ALTERs for all tables using MySQL's UTF8 character set. Also bump up any TEXT columns to LONGTEXT.
<?php
if (!isset($argv[1]) || strlen($argv[1]) < 1 ||
!isset($argv[2]) || strlen($argv[2]) < 1 ||
!isset($argv[3]) || strlen($argv[3]) < 1 ||
!isset($argv[4]) || strlen($argv[4]) < 1 ) {
echo "{$argv[0]} <hostname> <dbname> <login-name> <login-password> is the way to go!\n";
die();
}
$host = trim($argv[1]);
$db_name = trim($argv[2]);
$db_user = trim($argv[3]);
$db_pass = trim($argv[4]);
$conn = new PDO("mysql:dbname=information_schema;host=$host", $db_user, $db_pass);
$query = "SELECT c.table_name, c.column_name, c.column_default, c.is_nullable,
c.column_type, c.character_maximum_length, c.column_key, i.index_name,
i.sub_part FROM information_schema.columns c LEFT JOIN information_schema.statistics i
ON i.table_schema = c.table_schema AND i.table_name = c.table_name AND
i.column_name = c.column_name WHERE (c.data_type like '%text%' OR c.data_type like '%char%')
AND (c.character_set_name LIKE 'utf8' OR c.collation_name like '%_general_%') AND
c.table_schema = '$db_name' ORDER BY c.table_name";
$last_table_name = "";
$first = true;
$cannot_fix = array();
foreach ($conn->query($query) as $row) {
$table_name = $row['table_name'];
$col_name = $row['column_name'];
$col_type = $row['column_type'];
$col_is_null = $row['is_nullable'];
$col_default = $row['column_default'];
$col_char_length_max = intval($row['character_maximum_length']);
$col_is_index = $row['column_key'];
$index_name = $row['index_name'];
$index_prefix_length = $row['sub_part'];
if ($last_table_name != $table_name) {
if (!$first) {
echo ";\n";
} else {
$first = false;
}
$last_table_name = $table_name;
echo "ALTER TABLE `$last_table_name` DEFAULT CHARSET utf8mb4";
}
// If column has an index on it with prefix length length > 191 we need to skip this. The code
// will either need to be modified first to accomodate a smaller column with length 191, or a different
// pattern will need to be chosen to index the data (like hashing)
if ($index_name !== null) {
if ((($index_prefix_length !== null) && (intval($index_prefix_length) > 191)) ||
(($index_prefix_length === null) && ($col_char_length_max > 191))) {
$cannot_fix[] = "`$table_name`: `$col_name` is $col_type with an index on it. It is too long and cannot be modified automatically.";
continue;
}
}
echo ",\n";
echo "\tMODIFY `$col_name` ";
if (strpos($col_type, "text") !== false) {
echo "LONGTEXT ";
} else {
echo "$col_type ";
}
echo "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ";
if ($col_is_null == "YES") {
echo "DEFAULT NULL";
} else {
echo "NOT NULL";
echo ($col_default === null) ? "" : " DEFAULT '$col_default'";
}
}
if (!$first) {
echo ";";
}
echo "\n";
if (count($cannot_fix)) {
echo "\n";
foreach ($cannot_fix as $err) {
echo $err . "\n";
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment