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:
|
1 2 3 4 |
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.
