Skip to content

Instantly share code, notes, and snippets.

@timotheemoulin
Last active November 27, 2018 07:30
Show Gist options
  • Save timotheemoulin/d8b5c47af9a95afa276aa83c8d1c405f to your computer and use it in GitHub Desktop.
Save timotheemoulin/d8b5c47af9a95afa276aa83c8d1c405f to your computer and use it in GitHub Desktop.
Convert all tables and columns from a given database from old ISO to UTF8.
#!/usr/bin/env bash
# Timothée Moulin
#
# Install : copy this script anywhere (you can place it in the (/usr/local/bin) directory
# and give it the execution permission : chmod +x convert-db-latin-utf8.sh
#
# You must call this script and give it the following parameters.
# 1) [required] database user
# 2) [required] database name
# 3) [required] database host
# 4) [optional] table exclusion pattern (single pattern that can use the mysql wildcard '%'. Default : v_.*
# 5) [optional] database password (if ommited, it will be asked later)
# e.g.
# convert-db-latin-utf8.sh user database localhost
echo "Connect to the database"
user=$1
pass=${5:-}
host=${3:-'localhost'}
db=$2
exclude=${4:-v_%}
# Check required variables
if [ -z $1 ]
then
echo "A MySQL user must be provided."
exit;
fi
if [ -z $2 ]
then
echo "A MySQL database must be provided."
exit;
fi
if [ -z $3 ]
then
echo "A MySQL host must be provided."
exit;
fi
if [ -z $pass ]
then
while true; do
echo -n "What's the password for user '$user'? "
read -s pass
echo ""
if [ ! -z $pass ]
then
break;
fi
done
fi
# check the mysql connection
while ! mysql -u$1 -p$pass -h$host $2 -e ";" 2>/dev/null
do
echo "MySQL connection failed."
exit;
done
echo "MySQL connection successful."
# convert to InnoDB ?while true; do
convertInnodb=0
read -p "Do you want to convert your database to InnoDb? (y/n) " yn
if [ "$yn" == "y" ] || [ "$yn" == "yes" ]
then
echo "Converting the database to InnoDb."
convertInnodb=1
fi
# binary types mapping
declare -A binaryTypes=()
binaryTypes["varchar"]="varbinary"
binaryTypes["char"]="varbinary"
binaryTypes["text"]="blob"
binaryTypes["tinytext"]="tinyblob"
binaryTypes["mediumtext"]="mediumblob"
binaryTypes["longtext"]="longblob"
echo "Fetching tables not matching exclusion pattern : $exclude"
declare -A columnInfo
headerNames=()
rowCount=1
currentTableName=
oldifs=$ifs
mysql -u$1 -p$pass -h$host $2 -e "select table_name, column_name, column_type from information_schema.columns where table_schema = '$db' and table_name not like '$exclude' and character_set_name is not null;" | while IFS=$'\t' read table_name column_name column_type
do
if [ $rowCount -eq 1 ] # this is the header line
then
# store the header names
headerNames[1]=${columnInfo[1]}
headerNames[2]=${columnInfo[2]}
headerNames[3]=${columnInfo[3]}
# skip the first row as it contains the request header
rowCount=$((rowCount+1))
else # this is not the header line
# the column info has been fully fetched
tableName=$table_name
columnName=$column_name
columnType=$column_type
columnTypeName=
columnTypeLength=
if [[ $columnType =~ ^(.*)\(([0-9]*)\)$ ]]
then
columnTypeName=${BASH_REMATCH[1]}
columnTypeLength="(${BASH_REMATCH[2]})"
else
columnTypeName=$columnType
columnTypeLength=
fi
# convert to InnoDb
if [ "$currentTableName" == $tableName ]
then
if [ $convertInnodb -eq 1 ]
then
-u$1 -p$pass -h$host $2 -e "alter table $tableName engine=InnoDB;"
fi
fi
# get equivalent binary type
columnBinaryType=${binaryTypes[$columnTypeName]}
if [ -z "$columnBinaryType" ]
then
echo "/!\\ No binary type found for column '$tableName.$columnName' of type '$columnTypeName' /!\\"
exit;
else
columnBinaryType=${binaryTypes[$columnTypeName]}
fi
# set the column as binary
echo "Changing to binary $tableName.$columnName"
mysql -u$1 -p$pass -h$host $2 -e "alter table $tableName change \`$columnName\` \`$columnName\` $columnBinaryType$columnTypeLength;"
# set the column back with its original format but in utf8
echo "Changing to binary $tableName.$columnName"
mysql -u$1 -p$pass -h$host $2 -e "alter table $tableName change \`$columnName\` \`$columnName\` $columnTypeName$columnTypeLength character set utf8;"
# reset values for the next loop
itemCount=1
columnInfo=()
rowCount=$((rowCount+1))
fi
itemCount=$((itemCount+1))
done
ifs=$oldifs
# process is finish
echo "All ${itemCount} columns have been successfully changed."
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment