Windows环境下MySQL5.7中文显示乱码的三种解决方案

Windows环境下MySQL5.7中文显示乱码的三种解决方案

最近在将数据从Oracle迁移到MySQL的过程中，遇到一些问题，其中就包括中文字符显示乱码。

数据导入成功之后，中文字段内容无法正常显示。后来才发现客户端的字符集设置是对的，服务器端的字符集设置不支持中文字符。

后来经过测试发现，我们可以通过三种方法解决这个问题，按照从低到高的级别分别是：

​表级​​数据库级​​服务器级​

1.测试环境 Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16

我是在Windows Server 2008 r2环境下进行测试，创建了测试数据库hoegh。

mysql> mysql> create database hoegh; Query OK, 1 row affected (0.00 sec) mysql> mysql> show create database hoegh; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> use hoegh; Database changed mysql> mysql> status -------------- mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64) Connection id: 2 Current database: hoegh Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.16 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 16 min 25 sec Threads: 1 Questions: 18 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.018 -------------- mysql>

我们看到hoegh数据库的默认字符集是latin1。

2.create table设置DEFAULT CHARSET参数

首先我们创建测试表hoegh，分别向表中插入一条英文信息和中文信息。

mysql> mysql> create table hoegh(id int,name varchar(20)); Query OK, 0 rows affected (0.20 sec) mysql> insert into hoegh values(1,'HOEGH');--插入成功 Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into hoegh values(1,'霍格');--插入失败 ERROR 1366 (HY000): mysql> mysql> show create table hoegh; +-------+------------------------------------------------------------------------------------------------------------------------------ | Table | Create Table +-------+------------------------------------------------------------------------------------------------------------------------------ | hoegh | CREATE TABLE `hoegh` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------ 1 row in set (0.02 sec) mysql>

我们发现包含中文的记录插入失败，通过show create table语句可以看到表的默认字符集为latin1，不支持中文。

接下来，我们重建测试表hoegh并设置

DEFAULT CHARSET为utf8，然后 向表中插入一条英文信息和中文信息。

mysql> mysql> drop table hoegh; Query OK, 0 rows affected (0.13 sec) mysql> mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.17 sec) mysql> show create table hoegh; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | hoegh | CREATE TABLE `hoegh` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into hoegh values(1,'HOEGH');--插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into hoegh values(1,'霍格');--插入成功 Query OK, 1 row affected (0.00 sec) mysql> select * from hoegh; +------+--------+ | id | name | +------+--------+ | 1 | HOEGH | | 1 | 霍格 | +------+--------+ 2 rows in set (0.00 sec) mysql>

我们看到插入过程没有报错，也能正常查询。

3.create database设置CHARACTER SET参数

我们重建测试库hoegh，并设置

CHARACTER SET参数。

然后，重建测试表hoegh并插入两条记录。

mysql> mysql> drop database hoegh;--删除测试库 Query OK, 1 row affected (0.11 sec) mysql> mysql> create database hoegh CHARACTER SET 'utf8';--重建测试库hoehg并设置CHARACTER SET参数 Query OK, 1 row affected (0.00 sec) mysql> show create database hoegh; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> use hoegh Database changed mysql> create table hoegh(id int,name varchar(20));  Query OK, 0 rows affected (0.19 sec) mysql> show create table hoegh; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | hoegh | CREATE TABLE `hoegh` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into hoegh values(1,'HOEGH');--插入成功 Query OK, 1 row affected (0.02 sec) mysql> insert into hoegh values(1,'霍格');--插入成功 Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from hoegh; +------+--------+ | id | name | +------+--------+ | 1 | HOEGH | | 1 | 霍格 | +------+--------+ 2 rows in set (0.00 sec) mysql>

我们看到插入过程 没有报错，也能正常查询。

此时，为了方便对比，我们查看一下系统的字符集参数。

mysql> show variables like '%char%'; +--------------------------+-------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ | +--------------------------+-------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql>

我们看到character_set_server参数为latin1，这表明当前服务器端的字符集为latin1。接下来，我们就通过修改参数文件来修改这个参数。

4.配置my.ini中的character_set_server参数

通过直接配置my.ini方式修改mysql的服务器端字符集，这样就可以一劳永逸。

首先我们找到配置文件，在里面添加一行记录——character-set-server=utf8。

[mysql] default-character-set=utf8 [mysqld] max_connections=200 default-storage-engine=INNODB basedir =E:\mysql-5.7.16-winx64\bin datadir =E:\mysql-5.7.16-winx64\data port = 3306 autocommit=0 character-set-server=utf8

需要注意的是，上述配置中default-character-set=utf8是针对客户端的设置，

而character-set-server=utf8是针对服务器端的设置。

接下来，我们重启一下MySQL服务，查看数据库的字符集参数。

E:\mysql-5.7.16-winx64\bin>net stop mysql MySQL 服务正在停止. MySQL 服务已成功停止。 E:\mysql-5.7.16-winx64\bin> E:\mysql-5.7.16-winx64\bin>net start mysql MySQL 服务正在启动 . MySQL 服务已经启动成功。 E:\mysql-5.7.16-winx64\bin>mysql -u root -proot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.16 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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 | E:\mysql-5.7.16-winx6in\share\charsets\ | +--------------------------+-------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql>

我们看到

character_set_server已经成功设置为utf8。然后我们重建测试数据库hoegh，并重复之前的建表语句。

mysql> mysql> drop database hoegh; Query OK, 1 row affected (0.16 sec) mysql> create database hoegh;--重建测试数据库hoegh Query OK, 1 row affected (0.00 sec) mysql> mysql> show create database hoegh; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> use hoegh; Database changed mysql> create table hoegh(id int,name varchar(20)); Query OK, 0 rows affected (0.17 sec) mysql> mysql> show create table hoegh; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | hoegh | CREATE TABLE `hoegh` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into hoegh values(1,'HOEGH'); Query OK, 1 row affected (0.00 sec) mysql> insert into hoegh values(1,'霍格'); Query OK, 1 row affected (0.00 sec) mysql> select * from hoegh; +------+--------+ | id | name | +------+--------+ | 1 | HOEGH | | 1 | 霍格 | +------+--------+ 2 rows in set (0.00 sec) mysql>

我们看到一切正常，搞定！

最后强烈建议大家使用utf8！号称万国码！

​~~~~~~~ the end~~~~~~~~~hoegh2016.11.04​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。