^_^小麦苗^_^

小麦苗博客,实用经典,简单易懂,接地气!---技术应用型博客,主打实用性,只讲实用的,不讲没用的,多做实验,多思考!

  • 博客访问: 5883773
  • 博文数量: 940
  • 用 户 组: 普通用户
  • 注册时间: 2012-09-23 17:46
  • 认证徽章:
个人简介

QQ:646634621 网名:小麦苗 blog:http://blog.itpub.net/26736162/abstract/1/ 微信公众号:xiaomaimiaolhr QQ群:230161599 微信群:私聊 DBA宝典号内搜:http://data.newrank.cn/m/s.html?s=OSsuPjE4LDI7 《Oracle笔试面试宝典》作者

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(940)

文章存档

2017年(385)

2016年(313)

2015年(168)

2014年(73)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
最近在将数据从Oracle迁移到MySQL的过程中,遇到一些问题,其中就包括中文字符显示乱码。
数据导入成功之后,中文字段内容无法正常显示。后来才发现客户端的字符集设置是对的,服务器端的字符集设置不支持中文字符。

后来经过测试发现,我们可以通过三种方法解决这个问题,按照从低到高的级别分别是:
  • 表级
  • 数据库级
  • 服务器级


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

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

点击(此处)折叠或打开

  1. mysql>
  2. mysql> create database hoegh;
  3. Query OK, 1 row affected (0.00 sec)

  4. mysql>
  5. mysql> show create database hoegh;
  6. +----------+------------------------------------------------------------------+
  7. | Database | Create Database |
  8. +----------+------------------------------------------------------------------+
  9. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  10. +----------+------------------------------------------------------------------+
  11. 1 row in set (0.00 sec)

  12. mysql>
  13. mysql> use hoegh;
  14. Database changed
  15. mysql>
  16. mysql> status
  17. --------------
  18. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)

  19. Connection id: 2
  20. Current database: hoegh
  21. Current user: root@localhost
  22. SSL: Not in use
  23. Using delimiter: ;
  24. Server version: 5.7.16 MySQL Community Server (GPL)
  25. Protocol version: 10
  26. Connection: localhost via TCP/IP
  27. Server characterset: latin1
  28. Db characterset: utf8
  29. Client characterset: utf8
  30. Conn. characterset: utf8
  31. TCP port: 3306
  32. Uptime: 16 min 25 sec

  33. Threads: 1 Questions: 18 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.018
  34. --------------

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

2.create table设置DEFAULT CHARSET参数

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

点击(此处)折叠或打开

  1. mysql>
  2. mysql> create table hoegh(id int,name varchar(20));
  3. Query OK, 0 rows affected (0.20 sec)


  4. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  5. Query OK, 1 row affected (0.00 sec)

  6. mysql>
  7. mysql> insert into hoegh values(1,'霍格');--插入失败
  8. ERROR 1366 (HY000):
  9. mysql>
  10. mysql> show create table hoegh;
  11. +-------+------------------------------------------------------------------------------------------------------------------------------
  12. | Table | Create Table
  13. +-------+------------------------------------------------------------------------------------------------------------------------------
  14. | hoegh | CREATE TABLE `hoegh` (
  15.   `id` int(11) DEFAULT NULL,
  16.   `name` varchar(20) DEFAULT NULL
  17. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  18. +-------+------------------------------------------------------------------------------------------------------------------------------
  19. 1 row in set (0.02 sec)

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

接下来,我们重建测试表hoegh并设置DEFAULT CHARSET为utf8,然后向表中插入一条英文信息和中文信息。

点击(此处)折叠或打开

  1. mysql>
  2. mysql> drop table hoegh;
  3. Query OK, 0 rows affected (0.13 sec)

  4. mysql>
  5. mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8;
  6. Query OK, 0 rows affected (0.17 sec)

  7. mysql> show create table hoegh;
  8. +-------+----------------------------------------------------------------------------------------------------------------------------+
  9. | Table | Create Table |
  10. +-------+----------------------------------------------------------------------------------------------------------------------------+
  11. | hoegh | CREATE TABLE `hoegh` (
  12.   `id` int(11) DEFAULT NULL,
  13.   `name` varchar(20) DEFAULT NULL
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  15. +-------+----------------------------------------------------------------------------------------------------------------------------+
  16. 1 row in set (0.00 sec)

  17. mysql>
  18. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  19. Query OK, 1 row affected (0.00 sec)

  20. mysql> insert into hoegh values(1,'霍格');--插入成功
  21. Query OK, 1 row affected (0.00 sec)

  22. mysql> select * from hoegh;
  23. +------+--------+
  24. | id | name |
  25. +------+--------+
  26. | 1 | HOEGH |
  27. | 1 | 霍格 |
  28. +------+--------+
  29. 2 rows in set (0.00 sec)

  30. mysql>
我们看到插入过程没有报错,也能正常查询。

3.create database设置CHARACTER SET参数

我们重建测试库hoegh,并设置CHARACTER SET参数。
然后,重建测试表hoegh并插入两条记录。

点击(此处)折叠或打开

  1. mysql>
  2. mysql> drop database hoegh;--删除测试库
  3. Query OK, 1 row affected (0.11 sec)

  4. mysql>
  5. mysql> create database hoegh CHARACTER SET 'utf8';--重建测试库hoehg设置CHARACTER SET参数
  6. Query OK, 1 row affected (0.00 sec)

  7. mysql> show create database hoegh;
  8. +----------+----------------------------------------------------------------+
  9. | Database | Create Database |
  10. +----------+----------------------------------------------------------------+
  11. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  12. +----------+----------------------------------------------------------------+
  13. 1 row in set (0.00 sec)

  14. mysql>
  15. mysql> use hoegh
  16. Database changed
  17. mysql> create table hoegh(id int,name varchar(20))
  18. Query OK, 0 rows affected (0.19 sec)


  19. mysql> show create table hoegh;
  20. +-------+----------------------------------------------------------------------------------------------------------------------------+
  21. | Table | Create Table |
  22. +-------+----------------------------------------------------------------------------------------------------------------------------+
  23. | hoegh | CREATE TABLE `hoegh` (
  24.   `id` int(11) DEFAULT NULL,
  25.   `name` varchar(20) DEFAULT NULL
  26. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  27. +-------+----------------------------------------------------------------------------------------------------------------------------+
  28. 1 row in set (0.00 sec)

  29. mysql>
  30. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  31. Query OK, 1 row affected (0.02 sec)

  32. mysql> insert into hoegh values(1,'霍格');--插入成功
  33. Query OK, 1 row affected (0.00 sec)

  34. mysql>
  35. mysql> select * from hoegh;
  36. +------+--------+
  37. | id | name |
  38. +------+--------+
  39. | 1 | HOEGH |
  40. | 1 | 霍格 |
  41. +------+--------+
  42. 2 rows in set (0.00 sec)

  43. mysql>
我们看到插入过程没有报错,也能正常查询。

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

点击(此处)折叠或打开

  1. mysql> show variables like '%char%';
  2. +--------------------------+-------------------------------------------+
  3. | Variable_name | Value |
  4. +--------------------------+-------------------------------------------+
  5. | character_set_client | utf8 |
  6. | character_set_connection | utf8 |
  7. | character_set_database | latin1 |
  8. | character_set_filesystem | binary |
  9. | character_set_results | utf8 |
  10. | character_set_server | latin1 |
  11. | character_set_system | utf8 |
  12. | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
  13. +--------------------------+-------------------------------------------+
  14. 8 rows in set, 1 warning (0.00 sec)

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

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

通过直接配置my.ini方式修改mysql的服务器端字符集,这样就可以一劳永逸。
首先我们找到配置文件,在里面添加一行记录——character-set-server=utf8。

点击(此处)折叠或打开

  1. [mysql]
  2. default-character-set=utf8
  3. [mysqld]
  4. max_connections=200
  5. default-storage-engine=INNODB
  6. basedir =E:\mysql-5.7.16-winx64\bin
  7. datadir =E:\mysql-5.7.16-winx64\data
  8. port = 3306
  9. autocommit=0
  10. character-set-server=utf8
需要注意的是,上述配置中default-character-set=utf8是针对客户端的设置,
而character-set-server=utf8是针对服务器端的设置。

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

点击(此处)折叠或打开

  1. E:\mysql-5.7.16-winx64\bin>net stop mysql
  2. MySQL 服务正在停止.
  3. MySQL 服务已成功停止。


  4. E:\mysql-5.7.16-winx64\bin>
  5. E:\mysql-5.7.16-winx64\bin>net start mysql
  6. MySQL 服务正在启动 .
  7. MySQL 服务已经启动成功。


  8. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
  9. mysql: [Warning] Using a password on the command line interface can be insecure.
  10. Welcome to the MySQL monitor. Commands end with ; or \g.
  11. Your MySQL connection id is 2
  12. Server version: 5.7.16 MySQL Community Server (GPL)

  13. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  14. Oracle is a registered trademark of Oracle Corporation and/or its
  15. affiliates. Other names may be trademarks of their respective
  16. owners.

  17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  18. mysql> show variables like '%char%';
  19. +--------------------------+-------------------------------------------+
  20. | Variable_name | Value |
  21. +--------------------------+-------------------------------------------+
  22. | character_set_client | utf8 |
  23. | character_set_connection | utf8 |
  24. | character_set_database | utf8 |
  25. | character_set_filesystem | binary |
  26. | character_set_results | utf8 |
  27. | character_set_server | utf8 |
  28. | character_set_system | utf8 |
  29. | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
  30. +--------------------------+-------------------------------------------+
  31. 8 rows in set, 1 warning (0.00 sec)

  32. mysql>

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

点击(此处)折叠或打开

  1. mysql>
  2. mysql> drop database hoegh;
  3. Query OK, 1 row affected (0.16 sec)

  4. mysql> create database hoegh;--重建测试数据库hoegh
  5. Query OK, 1 row affected (0.00 sec)

  6. mysql>
  7. mysql> show create database hoegh;
  8. +----------+----------------------------------------------------------------+
  9. | Database | Create Database |
  10. +----------+----------------------------------------------------------------+
  11. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  12. +----------+----------------------------------------------------------------+
  13. 1 row in set (0.00 sec)

  14. mysql>
  15. mysql> use hoegh;
  16. Database changed
  17. mysql> create table hoegh(id int,name varchar(20));
  18. Query OK, 0 rows affected (0.17 sec)

  19. mysql>
  20. mysql> show create table hoegh;
  21. +-------+----------------------------------------------------------------------------------------------------------------------------+
  22. | Table | Create Table |
  23. +-------+----------------------------------------------------------------------------------------------------------------------------+
  24. | hoegh | CREATE TABLE `hoegh` (
  25.   `id` int(11) DEFAULT NULL,
  26.   `name` varchar(20) DEFAULT NULL
  27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  28. +-------+----------------------------------------------------------------------------------------------------------------------------+
  29. 1 row in set (0.00 sec)

  30. mysql>
  31. mysql> insert into hoegh values(1,'HOEGH');
  32. Query OK, 1 row affected (0.00 sec)

  33. mysql> insert into hoegh values(1,'霍格');
  34. Query OK, 1 row affected (0.00 sec)

  35. mysql> select * from hoegh;
  36. +------+--------+
  37. | id | name |
  38. +------+--------+
  39. | 1 | HOEGH |
  40. | 1 | 霍格 |
  41. +------+--------+
  42. 2 rows in set (0.00 sec)

  43. mysql>
我们看到一切正常,搞定!

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


~~~~~~~ the end~~~~~~~~~
hoegh
2016.11.04


阅读(200) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册