converts mysql data to character set utf8mb4 set / collation utf8mb4_unicode_ci

convert_db_to_utf8.sh

#!/bin/bash

function make_mysql_utf8_convert() {
db="$1"
echo "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci, COLLATION_CONNECTION = utf8mb4_unicode_ci, COLLATION_DATABASE = utf8mb4_unicode_ci, COLLATION_SERVER = utf8mb4_unicode_ci;" > update_utf8.sql;
echo "SET FOREIGN_KEY_CHECKS=0;" >> update_utf8.sql;
echo "ALTER DATABASE ${db} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;" >> update_utf8.sql;
for t in $(echo "show table status where Engine IS NOT NULL;" | mysql -s "${db}"| awk '{ print $7 " " $1 }' |sort -n | awk '{ print $2 }'); do
                echo "ALTER TABLE ${t} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci$(mysqldump -d "${db}" "$t" | grep "^ *\`"| \
                        grep -i -E -e "\` (varchar|mediumtext|text|char)" | \
                        sed -e s#" COLLATE utf8mb4_unicode_ci"#""#g -e s#",$"#""#g \
                        -e s#"^ *\`\([^\`]*\)\` \([^ ]*\) \([COMMENT\|COLLATE\|DEFAULT\ NOT\|unsigned\|NULL].*\)$"#", CHANGE \`\1\` \`\1\` \2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci \3"#g \
                        -e s#"^ *\`\([^\`]*\)\` \([^ ]*\)$"#", CHANGE \`\1\` \`\1\` \2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"#g | \
                        tr "\n" " " |sed s#" *, CHANGE "#",\n  CHANGE "#g);" >> update_utf8.sql;
done
echo "SET FOREIGN_KEY_CHECKS=1;" >> update_utf8.sql;
}


make_mysql_utf8_convert my