MySQL修改默认字符集解决中文乱码问题
兮陌 • 2020 年 04 月 23 日
MySQL默认得字符集为Latin1(ISO-8859-1),只包含西文字符,如果有中文就会显示乱码,比如显示???
我的环境是:windwos10+MySQL5.7,下面就以此为例
查看字符集
在修改之前我们先查看一下当前数据库的默认字符集:show variables like "%colla%";
show variables like "%char%";
得到结果如下:mysql> show variables like "%colla%";
+----------------------+---------- --------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\mysql57\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
修改字符集
修改字符集需要修改MySQL的配置文件,Windows的配置文件在MySQL安装目录下,文件名为my.ini
如果你没有找到这个文件有两个方法:自己新建一个
MySQL一般会有一些预设的配置文件,比如my-default.ini等等,拷贝一份,然后重命名为my.ini
每个节点都需要配置,如果有就修改,没有则添加:[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
[mysql]
default-character-set=utf8
修改成功后重启mysql服务(需要管理员权限):net stop mysql
net start mysql
重新查询字符集配置:mysql> show variables like "%colla%";
+----------------------+---------- ------+
| 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)
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 | C:\mysql57\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)为了确保字符集统一, 在建表的时候最好强制设定统一的字符集
另外通过JBDBC连接MySQL的时候最好加上以下参数:
jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=UTF-8
附Linux下的配置方法:
Linux的配置文件为my.cnf,通常位于etc/mysql/my.cnf或者/etc/my.cnf路径下。
但由于安装版本或是系统环境的不同,我们可能无法在这个目录中找到该文件,这时需要自己创建一个。
查找一个my-medium.cnf文件,然后复制到/etc文件夹下,其他同Windows:[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
[mysql]
default-character-set=utf8