MySQL命令行查询乱码

楔子

情况比较复杂，我是在window上安装MySQL数据库，使用编码是UTF8。查询乱码是在虚拟机里面的Linux上查询的

查询乱码

mysql> select * from k_s_fam;
+----+--------+--------+--------+---------+
| id | name   | birthN | birthG | rela    |
+----+--------+--------+--------+---------+
|  1 | ?????? |    102 |    211 | zhuziym |
|  2 | ?????? |    102 |      0 | zhuziym |
|  3 | ?????? |    106 |      0 | zhuziym |
|  4 | ?????? |    106 |      0 | zhuziym |
|  5 | ???    |    107 |      0 | zhuziym |
|  6 | ??     |    107 |      0 | zhuziym |
|  7 | ???    |    109 |      0 | zhuziym |
|  8 | ???    |    109 |      0 | zhuziym |
|  9 | ???    |    109 |    214 | zhuziym |
| 11 | ???    |    112 |    203 | zhuziym |
| 31 | ??     |    305 |      0 | ?       |
| 32 | ???    |    308 |    429 | ?       |
+----+--------+--------+--------+---------+
12 rows in set (0.00 sec)

查看环境变量编码

mysql> show variables like '%char%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | latin1                                   |
| character_set_connection | latin1                                   |
| character_set_database   | utf8                                     |
| character_set_filesystem | binary                                   |
| character_set_results    | latin1                                   |
| character_set_server     | utf8                                     |
| character_set_system     | utf8                                     |
| character_sets_dir       | D:\soft\work\MysqlServer\share\charsets\ |
+--------------------------+------------------------------------------+
8 rows in set (0.01 sec)

解决

1 仅限于当前命令行窗口有效 set character_set_results=utf8;

mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from k_s_fam;
+----+--------------------+--------+--------+---------+
| id | name               | birthN | birthG | rela    |
+----+--------------------+--------+--------+---------+
|  1 | 李** |    102 |    211 | zhuziym |
------------------------------------------------------------------------

2 set names utf8;

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

mysql> select * from k_s_fam;
+----+--------------------+--------+--------+---------+
| id | name               | birthN | birthG | rela    |
+----+--------------------+--------+--------+---------+
|  1 | 李** |    102 |    211 | zhuziym |
|  2 | 王**峰 |    102 |      0 | zhuziym |

描述

解决乱码的方法是，在执行SQL语句之前，将MySQL以下三个系统参数设置为与服务器字符集character-set-server相同的字符集。

character_set_client：客户端的字符集。
character_set_results：结果字符集。
character_set_connection：连接字符集。
设置这三个系统参数通过向MySQL发送语句：set names utf8

---

使用set names设置前后变化

###############设置前
mysql> show variables like '%char%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | latin1                                   |
| character_set_connection | latin1                                   |
| character_set_database   | utf8                                     |
| character_set_filesystem | binary                                   |
| character_set_results    | latin1                                   |
| character_set_server     | utf8                                     |
| character_set_system     | utf8                                     |
| character_sets_dir       | D:\soft\work\MysqlServer\share\charsets\ |
+--------------------------+------------------------------------------+
8 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
###############设置后
mysql> show variables like '%char%';
+--------------------------+------------------------------------------+
| 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       | D:\soft\work\MysqlServer\share\charsets\ |
+--------------------------+------------------------------------------+
8 rows in set (0.01 sec)

用图片对比上述变化

参考

MySQL命令行查询乱码解决方法