MySQL Datenbank nach UTF-8 konvertieren

Dieses bash-Skript konvertiert eine komplette MySQL Datenbank nach UTF-8

#!/bin/bash

if [ $# -eq 0 ]; then
  echo "Example: $(basename $0) databasename"
  exit 1;
fi

DUMP=`mktemp`
DB=$1
echo "Converting database '"$DB"' to UFT8MB4"

SQL="USE information_schema;
SELECT CONCAT('ALTER DATABASE \`',table_schema,'\` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;') AS _sql
FROM \`TABLES\` WHERE table_schema LIKE '"$DB"' AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
SELECT CONCAT('ALTER TABLE \`',table_schema,'\`.\`',table_name,'\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS _sql
FROM \`TABLES\` WHERE table_schema LIKE '"$DB"' AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION
SELECT CONCAT('ALTER TABLE \`',\`COLUMNS\`.table_schema,'\`.\`',\`COLUMNS\`.table_name, '\` CHANGE \`',column_name,'\` \`',column_name,'\` ',data_type,'(',character_maximum_length,') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',IF(is_nullable='YES',' NULL',' NOT NULL'),';') AS _sql
FROM \`COLUMNS\` INNER JOIN \`TABLES\` ON \`TABLES\`.table_name = \`COLUMNS\`.table_name WHERE \`COLUMNS\`.table_schema like '"$DB"' and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT('ALTER TABLE \`',\`COLUMNS\`.table_schema,'\`.\`',\`COLUMNS\`.table_name, '\` CHANGE \`',column_name,'\` \`',column_name,'\` ',data_type,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',IF(is_nullable='YES',' NULL',' NOT NULL'),';') AS _sql
FROM \`COLUMNS\` INNER JOIN \`TABLES\` ON \`TABLES\`.table_name = \`COLUMNS\`.table_name WHERE \`COLUMNS\`.table_schema like '"$DB"' and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE';
"
echo "set foreign_key_checks=0;" > $DUMP
mysql --skip-column-names -e "$SQL" >> $DUMP
mysql < $DUMP
rm $DUMP

duration=$SECONDS
echo "UTF8MB4 convertion done. $(($duration / 60)) minutes and $(($duration % 60)) seconds elapsed."