解决MySql Command Line Client查询结果乱码问题

最新推荐文章于 2024-10-23 14:41:36 发布

原创最新推荐文章于 2024-10-23 14:41:36 发布 · 3.6k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #Java #command #乱码 #UTF-8

mysql/mariadb 专栏收录该内容

23 篇文章

订阅专栏

本文介绍了一种在MySQL中遇到的中文乱码问题，并详细解释了如何通过调整客户端连接编码来解决这一问题。通过对比不同客户端工具显示效果，确定了问题根源并提供了具体的解决方案。

乱码问题：

数据库是MySql，存储的是中文的内容用MySql Command Line Client 查看是乱码，但是用MySql Administrator查看却显示正确

先看数据库的相关编码

mysql> show variables like 'character_set_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+

查看数据库表数据出现乱码，首先MySql Administrator查看却显示正确的，只是用MySql Command Line Client查看显示乱

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 涓?  |    4 |
+----+------+------+
1 row in set (0.00 sec)

问题分析，这个原因主要是受客户端的连接相关编码影响，下面三项

character_set_client
character_set_connection
character_set_results

而这三项是可以通过set names utf8|set names gbk来可以设置的！另外也说明当前连接的客户端的编码情况没有影响到数据库服务器本身的编码情况。

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 上     |    4 |
+----+------+------+