Posts Tagged → character set
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