At some point in your web dev life you might face the “Illegal mix of collations” mysql error. Nothing complicated, you just need all your tables to use the same charset and collation and this can be fixed really easy using phpmyadmin or even from the command line. However, when the database has 300+ tables, doing it manually is really not an option, so it needs to be done using some kind of a script. It can be done in perl, php, python or anything for that matter, but I particularly liked this one line command, using bash & awk (always loved awk):

mysql --user=username --password=your_pass --database=dbname -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --user=username --password=your_pass --database=dbname &

Found it on commandlinefu.com and worked like a charm.

Make sure to first read this article about converting charsets in mysql. Executing the command above might break things, depending on the database architecture. It’s always a good idea to create a backup of the database first.

Cristi MySQL bash, mysql