【转】Mysql SET NAMES 字符集解决方案

大家都知道
SET NAMES x
相当于
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

是用来设置客户端的字符集的，与服务器端的字符集无关，应该说msyql服务器端存储的字符是一定的。
因此在只要保持set names x 与页面的编码一致 ,无论什么编码都可以正确显示.

以下从MySQL5.0官方文档上摘录了相关内容，并翻译，说明了相关系统变量的用处：

What character set is the statement in when it leaves the client?
statement离开客户端的时候是什么字符集？

The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
客户端送过来的statement，服务器认为它的字符集是系统变量character_set_client的值。

What character set should the server translate a statement to after receiving it?
服务器收到一个statement后，会把它转换成什么字符集？

For this, the server uses the character_set_connection and collation_connection system variables. It converts statements 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, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
为了这一用途，服务器使用系统变量character_set_connection和collation_connection。它把客户端传来的 statement，从character_set_client字符集转换成character_set_connection字符集（除非字符串中有类似_latin1或者_utf8的字符集声明）。collation_connection对于字符串的比较是非常重要的。对于字符类型的字段值的比较，collation_connection是不起作用的。因为字段有自己的collation，有更高的优先级。

What character set should the server translate to before shipping result sets or error messages back to the client?
在结果集由服务器传递给客户端之前，需要转换成什么字符集？

The character_set_results system 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.
character_set_results系统变量表明了服务器返回查询结果时使用的字符集。返回的数据，有比如字段的值和元数据（例如字段名）。

If you are using the mysql client with auto-reconnect enabled (which is not recommended), it is preferable to use the charset command rather than SET NAMES. For example:
如果你使用mysql客户端的自动重连（不推荐使用），最好用charset命令，而不是SET NAMES。例如：

mysql> charset utf8
Charset changed

The charset command issues a SET NAMES statement, and also changes the default character set that is used if mysql reconnects after the connection has dropped.
charset命令发出了一个SET NAMES语句，并且连接断开后自动重连时使用的缺省字符集也被修改了。

The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose.
如果CREATE TABLE中没有明确指出字符集和collation，那么database字符集和collation将做为缺省值，它们没有其他的用处。

The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.
缺省database的字符集和collation可以通过系统变量character_set_database和 collation_database查看。服务器当缺省database改变时设置这些变量的值。如果没有缺省的database，这些变量的将与对应的服务器级的系统变量－character_set_server和collation_server的值相同。

The table character set and collation are used as default values if the column character set and collation are not specified in individual column definitions. The table character set and collation are MySQL extensions; there are no such things in standard SQL.
表的字符集和collation会作为缺省值，如果列的定义中没有指明字符集和collation的话。

Every “character” column (that is, a column of type CHAR, VARCHAR, or TEXT) has a column character set and a column collation.
每个“字符”字段（即CHAR、VARCHAR或者TEXT类型的字段）都有一个字段字符集和字段校验（collation）。

Every character string literal has a character set and a collation.
每个字符串有一个字符集和一个较验。

A character string literal may have an optional character set introducer and COLLATE clause:
每个字符串有一个可选的字符集introducer和COLLATE子句：

[_charset_name]’string’ [COLLATE collation_name]

Examples:

SELECT ’string’;
SELECT _latin1’string’;
SELECT _latin1’string’ COLLATE latin1_danish_ci;

评论

1 楼 xiebo1983 2013-03-22  

http://www.sqkoo.com/show/detail/300