MySQL - Find all Table Columns in all Databases with a given Collation

If you've stumbled upon this post it probably means that you're trying to normalize the Collation of your Database(s), Table(s) and/or Column(s) to avoid hitting illegal mix type errors such as the following:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ...

In case you don’t know what the term Collation actually stands for, you can get the proper definition from the official MySQL docs:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

That said, there's a great query which I got from this StackOverflow thread (many thanks to Dean Rather for that) that can be used to check which columns do actually have a given Collation:

SELECT table_schema, table_name, column_name, character_set_name, collation_name 
FROM information_schema.columns
WHERE collation_name = 'latin1_general_ci'
ORDER BY table_schema, table_name,ordinal_position;

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'latin1_general_ci'

ORDER BY table_schema, table_name,ordinal_position;

You can either replace latin1_general_ci with the Collation name you're looking for: alternatively, you can also work in the opposite way by changing the WHERE condition in order to find any Collation which differs from a given one.

If you need to change the Collation for a Database, together with all its Tables and/or Columns Character Set, you can also read this other post for precise instruction on how to do that.