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
最后修改:2020 年 06 月 27 日 02 : 56 PM