2019-05-25 MySQL数据库字符集

1. MySQL数据库字符集知识

1. 什么是字符集

字符编码就是将人类使用的汉字（或其他语言）、英文字母、特殊符号等信息，通过预先设定的转换规则，将其转换为计算机可以识别的二进制数字的一种编码方式。

2. MySQL数据库字符集

字符集其实就是一套文字符号及编码，对应的文字及编码，可以将人类可以识别的内容与计算机可以识别的信息进行互相转换。
假设有一个字母表使用了四个字母：A、B、a、b。我们为每个字母赋予一个数值：A=0，B=1，a=2，b=3.字母A是一个符号，数字0是A的编码，这四个字母和它们的编码组合在一起就可以成为一个字符集。
MySQL数据库字符集不仅包括字符集（CHARACTER），还包括校对规则（COLLATION）。其中，校对规则的作用是定义比较字符串的方式。
假设希望比较两个字符串的值：A和B。最简单的方法是查找编码：A为0，B为1。因为0小于1，所以可以说A小于B。可见，校对规则是一套规则，作用是对编码进行比较。

3. 常用字符集介绍与选择建议

3.1 常用字符集介绍

常用字符集知识

3.2 MySQL如何选择合适的字符集

如果存储的是各种各样的语言文字，则可以选择UTF8，这是目前国内应用最为广泛的字符集，没有之一
如果只需要支持中文，并且数据量很大，此外，还包含了大量的运算，则可以选择GBK，理论上其可以获得更高的性能，但不推荐使用
对于新型的互联网以及移动互联网的混合业务，推荐使用utf8mb4字符集替代UTF8字符集。总之，如果没有极特别的需求，请选择UTF8或utf8mb4作为数据库的字符集
如果使用开源程序，则可以根据上述说明进行选择，如果是公司开发人员自己开发产品，那么选择权就在开发人员手里，DBA只能提供建议

3.3 查看MySQL数据库字符集和校对规则

MySQL数据库支持的字符集有很多种，通过以下命令可以查看当前MySQL支持的字符集：

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
---省略若干行...
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
---省略若干行...
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
---省略若干行...
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
---省略若干行...
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.04 sec)

2. MySQL数据库字符集配置

设置MySQL的字符集需要考虑到很多个层次，大概分为以下7个级别：

操作系统级别
操作系统客户端级别（SSH）
MySQL实例级别
数据库中的库级别
表级别（含字段级别）
MySQL客户端级别（连接及返回结果）
程序代码级别

1. Linux系统服务端字符集设置

很多人在使用MySQL时经常会被中文乱码所困扰，其中Linux系统和连接Linux系统客户端的字符集设置可能就是问题之一，对此，要尽量将系统的字符集和系统中软件的字符集进行统一，设置和生效的方法具体如下：

[root@oldboy ~]# cat /etc/sysconfig/i18n     ---配置到配置文件里可以永久生效
LANG="zh_CN.UTF-8"    ---LANG为系统字符集环境变量，设置为中文UTF8
SYSFONT="latarcyrheb-sun16"
[root@oldboy ~]# source /etc/sysconfig/i18n    ---使得修改立即生效
[root@oldboy ~]# echo $LANG
zh_CN.UTF-8

如果在Linux服务器里使用MySQL登录到数据库，则请注意系统字符集的使用

2. Linux系统客户端字符集设置

常见的连接Linux系统的客户端为SecureCRT、XShell，其中SecureCRT的配置如下图所示：

SecureCRT的字符集配置

XShell的配置如下图所示：

XShell的字符集配置

方法1：前文在编译安装MySQL的时候就曾指定过如下的服务器端字符集

cmake .
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \

方法2：如果编译时没指定字符集，或者指定了不合适的字符集，那么还可以在安装后修改配置文件。
可按如下要求更改my.cnf参数：

[mysqld]
character-set-server=utf8

方法3：还可以在启动数据库时，增加选项指定的字符集

mysqld --character-set-server=utf8    ---不推荐使用此方法

以上三种方法对数据库的影响具体体现在输出结果中有注释的参数行上，具体如下：

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8 ---MySQL中库的字符集            |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8 ---MySQL服务器实例字符集   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.6.41/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

4. MySQL数据库中的库的字符集设置

在MySQL中，库的字符集设置一般是在建库的时候指定的，如果在建库的时候未指定，则库的字符集与MySQL数据库实例的字符集一致。可通过如下命令查看当前实例的字符集：

mysql> show variables like 'character_set_database%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'collation_database%';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

下面创建数据库test，并查看建库的字符集：

mysql> create database tingting;
Query OK, 1 row affected (0.00 sec)
mysql> show create database tingting\G
*************************** 1. row ***************************
       Database: tingting
Create Database: CREATE DATABASE `tingting` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

在编译MySQL时，若指定了正确的字符集或者修改配置文件调整过的服务器的字符集，那么，在以后建库的时候就可以直接执行简化的命令“create database tingting;”。
也可以在建库的时候指定字符集和校对规则来建库：

create database oldboy DEFAULT CHARACTER SET UTF8 DEFAULT COLLATE = utf8_general_ci;

上面的语句为查看已建立的oldboy库的语句，其中，“CHARACTER SET UTF8”即为数据库字符集，而“utf8_general_ci”则为校对规则。
注意：采用二进制方式安装MySQL时，若没有指定字符集，则此时字符集默认为latin1，此时需要建立设置UTF8字符集的库，即需要指定UTF8字符集建库，否则就必须要提前修改配置文件，将服务器字符集调整为所需要的字符集。

5. MySQL数据库表的字符集设置

默认情况下，建表的字符集与库的字符集应一致，设置表字符集的命令如下：

mysql> use tingting
Database changed
mysql> create table test(id int(4));
Query OK, 0 rows affected (0.22 sec)
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8    ---如果在建表时需要调整字符集，则必须制定字符集，则必须指定字符集
1 row in set (0.02 sec)

6. MySQL数据库客户端字符集设置

对MySQL数据库客户端字符集进行设置，对于防止MySQL更新时，出现中文乱码有极大的影响，设置方法如下。
方法1：临时生效单条命令

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

其中，“set names utf8”也可以用下面三个命令来代替

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

方法2：登录数据库时指定字符集

mysql --default-character-set=utf8;

方法3：通过修改my.cnf实现修改MySQL客户端的字符集

[client]
default-character-set=utf8

以上几种方法对数据库的影响具体体现在输出结果中有注释的参数行上，具体如下：

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8 ---MySQL客户端字符集 |
| character_set_connection | utf8 ---MySQL连接的字符集  |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8 ---MySQL返回结果的字符集 |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.6.41/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

7. 程序代码字符集

很多开源软件都会给出多种字符集的软件代码，例如，bbs软件就是如此。Discuz! X3.2在集成和完善Discuz! 3.1的基础上，针对社区移动端进行了新的尝试，比如，退出微信登录、微社区等功能。它作为安全稳定的程序可为站长提供更加可靠的保障。

3. 如何防止数据库的中文显示乱码

管理员在配置MySQL数据库字符集时，需要尽可能地确保前面提到的7大项字符集统一，查看数据库字符集的基本方法如下：

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       | /application/mysql-5.6.41/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

更改Linux系统字符集变量之后，可以查看MySQL中字符集的变化

彻底防止MySQL数据库内的数据中文乱码方法

字符集的不一致是数据库乱码的罪魁祸首，要想避免MySQL数据库内的数据库中文乱码方法，就要遵循前文所说的7大项字符集设置规则，即Linux系统服务端与Linux系统客户端字符集、MySQL服务端数据库实例与MySQL数据库客户端字符集、MySQL数据库中的库和表的字符集、程序代码的字符集要一致。如果是利用文件还原数据，还要注意文件的编码问题。
以下是一个不按要求来操作，导致出现乱码的范例：

mysql> set names gbk;    ---修改MySQL客户端字符集为GBK
Query OK, 0 rows affected (0.01 sec)
mysql> use tingting
Database changed
mysql> create table t1(id int(4),name varchar(16));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values(1,'老男');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 老男   |
+------+--------+
1 row in set (0.00 sec)
mysql> set names utf8;    ---修改MySQL客户端字符集为UTF8
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 鑰佺敺    |    ---发现数据乱码了，这就是客户端字符集和服务端不一致时插入数据而导致的乱码问题
+------+-----------+
1 row in set (0.00 sec)

4. 如何更改MySQL数据库库表的字符集

1. 更改库的字符集

管理员可以使用alter命令对数据库的字符集进行更改：

mysql> show create database oldboy\G
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql> alter database oldboy CHARACTER SET latin1 COLLATE = latin1_swedish_ci;
Query OK, 1 row affected (0.02 sec)

mysql> show create database oldboy\G
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql> alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboy\G
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

2. 更改表的字符集

管理员也可以使用alter命令对数据库的表的字符集进行更改：

mysql> use tingting
Database changed
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(4) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table t1 CHARACTER SET latin1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(4) DEFAULT NULL,
  `name` varchar(16) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

上面仅仅是修改字符集的基本操作，若是对于已经上线的数据库，并且已经包含了很多数据，就无法使用这个方法了，使用“alter database dbname character set *”修改库的字符集，或者使用“alter table tablename character set *”更改表的字符集，只对新创建的表或者更新的数据生效。

3. 生产环境更改数据库（含数据）字符集的方法

对于已经包含了数据的库表，若要对字符集进行调整，就需要将数据先导出，然后更改数据库环境，更改建库环境，更改建库和表的字符集之后，重新导入数据，这样才能实现相应的调整。整个过程具体如下：
1）确保数据库不要更新，然后导出所有数据为SQL的文件
2）针对导出的数据进行字符集替换（替换表和库），例如把GBK改为UTF8
3）修改my.cnf配置文件，更改MySQL客户端及服务端的字符集，重启生效
4）导入更改过新字符集的库表的数据，包括表结构语句，然后提供服务
5）将操作系统，SSH客户端，以及程序更改为对应的新字符集
另外，更改字符集时，要将小的字符集集合更改为大的字符集集合，不然可能会丢失数据。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,723评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,080评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,604评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,440评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,431评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,499评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,893评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,541评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,751评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,547评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,619评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,320评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,890评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,896评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,137评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,796评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,335评论 2赞 342