小树林的数据库从sql server迁到了mysql5，出现了Illegal mix of collations异常，找了一下发现根本解决之道还是得弄明白mysql的charset机制，官网的这篇写得十分明了了：
Consider what a “connection” is: It's what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables:
-
What character set is the query in when it leaves the client?
The server takes the character_set_client variable to be the character set in which queries are sent by the client.
-
What character set should the server translate a query to after receiving it?
For this, character_set_connection and collation_connection are used by the server. It converts queries sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, it does not matter because columns have a higher collation precedence.
-
What character set should the server translate to before shipping result sets or error messages back to the client?
The character_set_results variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names.
You can fine-tune the settings for these variables, or you can depend on the defaults (in which case, you can skip this section).
