Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

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

View alter_generator.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";
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.