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.


About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.