linux下mysql中文乱码问题

原文连接：http://blog.csdn.net/liuzhujun/article/details/7854520

今天同事开发过程中遇到linux下mysql中文乱码问题，直接通过sql脚本插入中文数据乱码。将解决方法记录下：

1、首先确定server、数据库和表的默认字符级

[sql]  view plain copy

1. mysql> show variables like 'character_set_%';  
2. +--------------------------+----------------------------+  
3. | Variable_name            | Value                      |  
4. +--------------------------+----------------------------+  
5. | character_set_client     | latin1                     |  
6. | character_set_connection | latin1                     |  
7. | character_set_database   | latin1                     |  
8. | character_set_filesystem | binary                     |  
9. | character_set_results    | latin1                     |  
10. | character_set_server     | latin1                     |  
11. | character_set_system     | utf8                       |  
12. | character_sets_dir       | /usr/share/mysql/charsets/ |  
13. +--------------------------+----------------------------+  
14. 8 rows in set (0.00 sec)  

2、通过上面的查询结果，我们知道server和database默认字符集为拉丁文latin1，表毫无疑问是采用server字符集作为默认字符集。这样就导致了，我们通过sql脚本插入中文时乱码的问题。由于我们代码中统一采用了UTF-8作为工程的默认字符集编码，为了统一，我们这里需要将字符集修改为UTF-8。

3、准备通过mysql下my.ini启动文件来修改默认的字符集，找了半天没找到（由于是测试环境，不记得mysql是不是系统自带的了）。那就采用命令行的方式来修改，分别修改server、database、table默认的字符集

[sql]  view plain copy

1. mysql> show variables like 'collation_%';  
2. +----------------------+-------------------+  
3. | Variable_name        | Value             |  
4. +----------------------+-------------------+  
5. | collation_connection | latin1_swedish_ci |  
6. | collation_database   | latin1_swedish_ci |  
7. | collation_server     | latin1_swedish_ci |  
8. +----------------------+-------------------+  
9. 3 rows in set (0.00 sec)  
10.   
11. mysql> show variables like 'character_set_%';  
12. +--------------------------+----------------------------+  
13. | Variable_name            | Value                      |  
14. +--------------------------+----------------------------+  
15. | character_set_client     | latin1                     |  
16. | character_set_connection | latin1                     |  
17. | character_set_database   | latin1                     |  
18. | character_set_filesystem | binary                     |  
19. | character_set_results    | latin1                     |  
20. | character_set_server     | latin1                     |  
21. | character_set_system     | utf8                       |  
22. | character_sets_dir       | /usr/share/mysql/charsets/ |  
23. +--------------------------+----------------------------+  
24. 8 rows in set (0.00 sec)  
25.   
26. mysql> use callcenter;  
27. Reading table information for completion of table and column names  
28. You can turn off this feature to get a quicker startup with -A  
29.   
30. Database changed  
31.   
32. mysql> alter database callcenter character set utf8;  
33. Query OK, 1 row affected (0.00 sec)  
34.   
35. mysql> set names 'utf8';  
36. Query OK, 0 rows affected (0.00 sec)  
37.   
38. mysql> use callcenter;  
39. Database changed  
40. mysql> select * from um_group;  
41. +----+------+----------------+------+  
42. | id | name | description    | type |  
43. +----+------+----------------+------+  
44. |  1 | ??   | ?????????????  |    2 |  
45. |  2 | ???  | ?????????????? |    2 |  
46. |  3 | aaaa | aaaaaa         |    2 |  
47. |  3 | ??   | ??             |    2 |  
48. |  4 | ??   | ??             |    2 |  
49. |  5 | ??   | ??             |    2 |  
50. +----+------+----------------+------+  
51. 6 rows in set (0.00 sec)  
52.   
53. mysql> select * from test;  
54. +----+------+-------------+------+  
55. | id | name | description | type |  
56. +----+------+-------------+------+  
57. |  5 | ??   | ??          |    2 |  
58. +----+------+-------------+------+  
59. 1 row in set (0.00 sec)  
60.   
61. mysql> show variables like 'character_%';  
62. +--------------------------+----------------------------+  
63. | Variable_name            | Value                      |  
64. +--------------------------+----------------------------+  
65. | character_set_client     | utf8                       |  
66. | character_set_connection | utf8                       |  
67. | character_set_database   | utf8                       |  
68. | character_set_filesystem | binary                     |  
69. | character_set_results    | utf8                       |  
70. | character_set_server     | latin1                     |  
71. | character_set_system     | utf8                       |  
72. | character_sets_dir       | /usr/share/mysql/charsets/ |  
73. +--------------------------+----------------------------+  
74. 8 rows in set (0.00 sec)  
75.   
76. mysql> set character_set_server=utf8;  
77. Query OK, 0 rows affected (0.00 sec)  
78.   
79. mysql> select * from test;  
80. +----+------+-------------+------+  
81. | id | name | description | type |  
82. +----+------+-------------+------+  
83. |  5 | ??   | ??          |    2 |  
84. |  6 | ??   | ??          |    2 |  
85. +----+------+-------------+------+  
86. 2 rows in set (0.00 sec)  
87.   
88. mysql> drop table test;  
89. Query OK, 0 rows affected (0.04 sec)  
90.   
91. mysql> create table test(  
92.     -> name varchar(20)  
93.     -> );  
94. Query OK, 0 rows affected (0.04 sec)  
95.   
96. mysql> insert into test values('啊啊啊');  
97. Query OK, 1 row affected (0.00 sec)  
98.   
99. mysql> select * from test;  
100. +-----------+  
101. | name      |  
102. +-----------+  
103. | 啊啊啊 |  
104. +-----------+  
105. 1 row in set (0.00 sec)