Convert all tables and columns from a given database from old ISO to UTF8.

convert-db-latin-utf8.sh

#!/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."