查看修改MySQL字符集以及乱码解决

查看和修改字符集

• Terminal下连接MySQL
• 输入以下语句

mysql> show variables like 'collation_%';+----------------------+-----------------+| Variable_name        | Value           |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database   | utf8_general_ci || collation_server     | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)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       | /usr/local/Cellar/mysql/5.7.18/share/mysql/charsets/ |+--------------------------+------------------------------------------------------+8 rows in set (0.00 sec)

可以看到你的数据库当前各个字符集变量的值

• 命令行修改字符集编码

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

不一一赘述了，其他的修改方式是一样的

• 配置文件修改字符集

vim /etc/my.cnf在[client]下添加default-character-set=utf8mb4在[mysql]下添加default-character-set=utf8mb4在[mysqld]下添加character-set-server=utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'# 重启mysql服务service mysql restart [Mac下为mysql.server restart]

随后配置就生效了，重新连接MySQL，可以利用前面提到的查询语句再去看一下

ps：Mac下的navicat出现乱码问题，在确认数据库编码没有问题，而且命令行下查询不是乱码的情况下，修改connection的encoding为auto，可能会解决问题。