public
Created

Generate ALTERs for all tables using MySQL's UTF8 character set. Also bump up any TEXT columns to LONGTEXT.

  • Download Gist
alter_generator.php
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
<?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";
}
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.