Mysql – collation_database, collation_connection, collation_server used for in MySQL

collationdatabase connectionMySQL

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

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

Best Answer

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

Collation is a set of rules on 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 function available in MySQL 5.2 converts the character to-be compared to binary_key.

SELECT WEIGHT_STRING('a');

gives 0x41.

SELECT WEIGHT_STRING('A');

gives 0x41.

This pdf from Mysqlwiki has examples for what you want!

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘find_in_set’

If you are using mysqli, issue this command right after connecting:

$mysqli->set_charset("utf8");

This will set your connection encoding to UTF8 (same as your table uses).

With plain mysql, use this:

mysql_query("SET NAMES utf8", $conn);
mysql_query("SET CHARACTER SET utf8", $conn);

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘find_in_set’

Related Topic