Site icon Ryadel

MySQL - How to find all Table Columns in all Databases with a given Collation Name - Character Set Name

MySQL Master-Master Replication setup in 5 easy steps

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:

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.

 

Exit mobile version