How to convert an entire MySQL database character set and collation to UTF-8?

I found myself in need of upgrading a number of older wordpress sites to change from the old latin1_swedish_ci to UTF-8, both the character set and collation.  I utilized a command line SQL prompt to accomplish this.  Modify the appropriate parameters “test” for your database, and “yourpasswordhere” with your password, ymmv, and have fun.

I am using Fedora 21 and MariaDB 10.0.15

(echo ‘ALTER DATABASE `'”test”‘` CHARACTER SET utf8 COLLATE utf8_general_ci;’; mysql -u root –password=”yourpasswordhere” “test” -e “SHOW TABLES” –batch –skip-column-names | xargs -I{} echo ‘ALTER TABLE `'{}’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’ ) | mysql -u root –password=”yourpasswordhere” “test”

I don’t believe I needed to login twice; however, I was rewriting this from some notes and it worked, so I left it.  Feel free to send me the shorter code.  I always appreciate it.

Leave a Reply