← Back to all posts

Getting busy with INFORMATION_SCHEMA

Jacob Elder — 30 seconds to read — April 8, 2009

I’ve been wanting to use this trick forever. Here’s how to quickly, effortlessly, and I should add, destructively, convert all tables in a given database to UTF8.

mysql --batch information_schema -e  "select concat('ALTER TABLE ', TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as '--' from TABLES where TABLE_SCHEMA='somedb' and TABLE_COLLATION not like '%utf8%'" | mysql somedb

The addition of INFORMATION_SCHEMA combined with CONCAT makes it possible to do arbitrarily cool things with MySQL. Love it.