Latin1 to UTF-8: A single query to find all the Latin1 database tables

MySQL uses Latin1 character set as default. This is something many web developers are concerned about and for good reason. Using latin1 excludes 98% of the world's languages (even a single word) from appearing correctly inside your website.

I agree that for certain unilingual North American sites special cases latin1 is good enough. Otherwise in our ever more international world with visitors from dozens of countries, you really should make all your sites utf8. Latin1 just can not support new websites.

On top of that you can avoid a lot of PHP errors by using correct utf-8 encoding. We've found that even WordPress sites which are principally already UTF-8 have the odd Latin1 table sneaking into them.

So how can you find all latin1 database tables without combing through dozens of sites one by one? Thankfully MySQL keeps this data in one place and you can find them with one database query:
SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_COLLATION` LIKE '%latin1%';

This is what the output looks like:

sample latin1 tables readout
sample latin1 tables readout

For converting your tables from latin1 to utf8 I recommend a article from Nic Jansma.

WARNING: Be extremely careful on what you convert. As I said, in special cases latin1 is enough and some tables may be designed for that. For instance, don't change anything in `information_schema` database. This is a main MySQL database with important data. So think before you make any changes.

This entry was posted in IT, WordPress and tagged , , . Bookmark the permalink.

Leave a Reply