Mac mysql 5.7.18 中文乱码问题

来源：互联网发布：yum安装nginx 编辑：程序博客网时间：2024/06/09 22:35

Mac OSX EI 10.11.6

mysql 5.7.18 最新版

这个问题困扰了我好几天，这里记录一下

情况是这样的，从数据库读出的中文数据是乱码的

mysql> select * from table_ip;+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+| id | ip        | port | country       | province      | city           | isp             | findTime            |+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+|  1 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  2 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  3 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  4 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  5 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  6 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  7 | 127.0.0.1 | 800  | ç¾Žå›½        |               |                |                 | 2015-05-15 07:27:03 ||  8 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  9 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 10 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 11 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 12 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç§»åŠ¨          | 2015-05-15 07:27:03 |+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+12 rows in set (0.01 sec)mysql>

解决思路：

show variables like 'character%';

查看数据库编码：

如上图，

character_set_database和character_set_server依然是latin1的字符集，也就是说mysql后续创建的表都是latin1字符集的，不是utf8，这就是原因。

但是

mysql 5.7.18 版本在Mac下的配置文件没有my-default.cnf，无法配置my.cnf

进入etc 目录新建文件my.cnf

cd /etc

vim my.cnf

输入：

#innodb_log_group_home_dir = /usr/local/mysql/data# You can set .._buffer_pool_size up to 50 - 80 %# of RAM but beware of setting memory usage too high#innodb_buffer_pool_size = 16M#innodb_additional_mem_pool_size = 2M# Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 5M#innodb_log_buffer_size = 8M#innodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[client]default-character-set=utf8[mysqld]character-set-server=utf8[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

然后保存退出，

如果保存失败，可以使用command ＋ S先保存到桌面保存为my.cnf

然后在把my.cnf放在etc 目录下

解决：

重启mysql 服务

查询数据还是乱码：

mysql> select * from table_ip;+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+| id | ip        | port | country       | province      | city           | isp             | findTime            |+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+|  1 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  2 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  3 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 |

但是插入数据是正常的，看最后一行

mysql> insert into table_ip(country)values("魏");Query OK, 1 row affected (0.01 sec)mysql> use weicc;Database changedmysql> select * from table_ip;+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+| id | ip        | port | country       | province      | city           | isp             | findTime            |+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+|  1 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  2 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  3 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  4 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  5 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  6 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  7 | 127.0.0.1 | 800  | ç¾Žå›½        |               |                |                 | 2015-05-15 07:27:03 ||  8 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 ||  9 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 10 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 11 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç”µä¿¡          | 2015-05-15 07:27:03 || 12 | 127.0.0.1 | 800  | ä¸å›½        | åŒ—äº¬        | åŒ—äº¬         | ç§»åŠ¨          | 2015-05-15 07:27:03 || 13 | NULL      | NULL | 魏            | NULL          | NULL           | NULL            | NULL                |+----+-----------+------+---------------+---------------+----------------+-----------------+---------------------+13 rows in set (0.01 sec)

后来使用 navigate 打开发现在命令行插入数据正常，navicate显示是乱码的。

然后我又使用navicate重新创建数据,插入中文数据，然后使用命令行查询，乱码

（ps navigate 创建数据库的时候编码格式我已经改为utf-8了）

mysql> select * from table_ip;+----+------+------+---------+----------+------+------+| id | ip   | port | country | province | city | isp  |+----+------+------+---------+----------+------+------+|  1 | 1    | NULL | ä¸º     | NULL     | NULL | NULL |+----+------+------+---------+----------+------+------+1 row in set (0.01 sec)

经过上面的折腾，可以确定，是navicate在搞鬼，

猜想：mysql 5.1.7.18版本是最新的，可能是navigate 版本与mysql 版本不匹配

解决：

如果你发现你改完以后在msql下的字符集全都改过来了，但是获取的数据还是乱码，那么重点来了，在navicat新建链接的时候把编码格式换成auto而不是utf8，然后在重新导入数据试一下，我也不知道为什么是这样。神奇。。

参考：http://www.jianshu.com/p/628bcf8bb557

http://www.cnblogs.com/Logen/p/3562215.html

http://blog.csdn.net/qq_33769766/article/details/52599675

0 0