mysql乱码问题解决方案备忘

今天碰到了mysql的乱码问题，解决了，备忘一下

mysql> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

我觉得，其实应该不用care mysql使用什么编码，字符集来保存数据，只要你导入数据时指定的编码跟你获取数据时指定的编码一致，那就应该不会出现乱码。

set character_set_connection=gbk;
set character_set_client=gbk;
set character_set_results=gbk;

导入数据的时候要指定三个编码（至少前两个），select数据的时候要指定这三个编码，谨防乱码.....

以下是官方文档对上述几个变量的说明
链接http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

What character set is the statement in when it leaves the client?
      The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

What character set should the server translate a statement to after receiving it?
      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.

What character set should the server translate to before shipping result sets 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.
Clients can fine-tune the settings for these variables, or depend on the defaults (in which case, you can skip the rest of this section). If you do not use the defaults, you must change the character settings for each connection to the server.

评论

3 楼 xiebo1983 2013-03-22  

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

2 楼 beneo 2010-12-30  

第三个为啥修改不了？？

1 楼 beneo 2010-12-10  

顶，正好需要解决这个问题