I read the documentation 'Using COLLATE in SQL Statements' https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html, but it doesn't say much about the effect of using different COLLATE values to the examples. Just as an example,

WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

What's the difference between using different values after COLLATE? What about if not using the COLLATE clause at all? How does one find the right or possible values to use after COLLATE?

2 Answers 2


Or... The Optimizer would not be able to use an index.

It is best (for performance) for everything to have the same collation:

  • The column
  • The connection
  • The client

Having to switch collations 'at run time' is costly.

Or, I use it to see how different collations handle accents, etc.

Note that an index has the data stored in a given character set and collation. Any need to deviate from that eliminates the ability to use the index for performance.


it would make both sides have the same collation, when the databse can't compare the data and gives following error or similar ones

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' ....

And there is also to possibility to make a cs (case sensitive 'ABC is not eual to 'abc') to a ci case insensitive comparisons so 'ABC' would equal 'abc' or vice versa

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.