funky code

Are you stuck in Debian/MySQL/Charset/Collation hell?

So while Debian still hasn’t changed the MySQL default caracter set and collation to utf8, we all know that the first thing to do on a vanilla Debian MySQL installation is to add the following utf8.cnf file to /etc/mysql/conf.d/:

[mysqld]
default-character-set=utf8
default-collation=utf8_unicode_ci

However, if for some reason you didn’t do that and have used software which hasn’t been consistently explicit about character sets and collations, you end up with a nice mess of character sets and collations.

There is a great post on serverfault which helps you out. It comes down to one command which will take some time based on the size of your database:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
CONCAT( 'ALTER TABLE \`', TABLE_SCHEMA, '\`.\`', TABLE_NAME, '\` CONVERT \
TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) FROM \
information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" \
| mysql --user=user --password=secret

Update:

And of course you need to alter the defaults for existing databases as well:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
CONCAT( 'ALTER SCHEMA \`', SCHEMA_NAME, '\` CHARACTER SET utf8 COLLATE \
utf8_unicode_ci;' ) FROM information_schema.SCHEMATA where SCHEMA_NAME \
!= 'information_schema';" | mysql --user=user --password=secret