what is collation_database, collation_connection, collation_server used for in mysql?

Question

Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

sometimes this error will be throw, what's up ? what's the collation_* used for ? Can someone give me a example to show how the character is encoded and transited and selected relate to collation_*?

Answer 1

In Mysql,every table has a table character set and a table collation.

Collation is a set of rules how to compare and sort character strings

every MySQL collation belongs to a single character set and every MySQL character set can have one or more collations which belong to it

Mysql uses binary_key for sorting character sets, the new weight_string frunction available in Mysql 5.2 converts the character to-be compared to binary_key

SELECT WEIGHT_STRING('a'); -> 0x41 SELECT WEIGHT_STRING('A'); -> 0x41

This pdf from Mysqlwiki has examples for what you want!