MySQL--字符集-白红宇

MySQL--字符集

阅读量：5316 次

发布时间：2019-06-14

本文共 7872 字，大约阅读时间需要 26 分钟。

1.字符集概述

简单的说字符集就是一套文字符号及其编码、比较规则的集合

20世纪60年代初期，美国标准化组织ANSI发布了第一个计算机的字符集ASCII(American Standard Code for Information Interchange)，后来进一步变成了国际标准ISO-646。这个字符集采用7位编码，定义了包括大小写英文字母、阿拉伯数字和标点符号，以及33个控制符号等。

2.Unicode简述

为了统一字符编码，国际标准化组织ISO(International Organization for Standardization)的一些成员国于1984年发起制定新的国际字符集标准，以容纳全世界各种语言文字和符号。这个标准最后叫做Universal Multiple-Octet Coded Character Set，简称UCS，标准编号则定位ISO-10646。ISO-10646标准采用4字节(32bit)编码，因此简称UCS-4。

ISO-10646发布以后，遭到了部分美国计算机公司的反对。1988年Xerox公司提议指定新的以16为编码的统一字符集Unicode，并联合Apple, IBM, DEC, Sun, Microsoft, Novell等公司成立Unicode协会(The Unicode Consortium)，并成立Unicode技术委员会(Unicode Technical Committee)，专门负责Unicode文字的搜集、整理合编码，并于1991年推出了Unicode 1.0

1991年10月，ISO将Unicode编码并入ISO-10646的0组0字面，叫做基本多语言文字面（Basic Muiti-lingual Plane,BMP），共有65534个码位，并根据不同用途分为若干区域。除BMP外的32767个字面又分为辅助字面和专用字面两部分，辅助字面用以收录IS0-10646后续搜集的各国文字，专用字面供使用者自定义收录ISO-10646未收录的文字符号。大部分用户只是用BMP字面就足够了，早期的ISO-10646标准也只要求实现BMP字面，这样只需要2字节来编码就足够了，Unicode也正是这么做的，这叫做ISO-10646编码的基本面形式，简称为UCS-2编码，UCS-2编码转换成UCS-4编码也很容易，只要在前面加两个取值为0的字节即可

Unicode双字节编码方式比起ISO-10646的4字节原始编码来说，在节省内存和处理时间上都具有优势，这也是Unicode编码方式更流行的原因。如果需要使用ISO-10646 BMP字面意外的文字，Unicode提出了名为UTF-16或代理法的解决方案，UTF是UCS/Unicode Transformation Format的缩写。UTF-16的解决办法是：对BMP字面的编码保持二字节不变，对其他字面的文字按一定规则将其32位编码转换为两个16位的Unicode编码，供两个字节的取值范围分别限定为0XD800~0xDBFF和0xDC00~0xDFFF，因此，UTF-16共有( 4 * 256 ) * ( 4 * 256 ) = 1048576 个码位

虽然UTF-16解决了ISO-10646除BMP外第1~15字面的编码问题，但当时的计算机和网络还是ASCII的天下，只能处理单字节数据流，UTF-16在离开Unicode环境后，在传输和处理中都存在问题。于是Unicode又提出了名为UTF-8的解决方案，UTF-8按一定规则将一个ISO-10646或Unicode字元码转换成1~4个字节的编码，其中将ASCII码(0~0x7F)转换成单字节编码，也就是严格兼容ASCII字符集；UTF-8的2字节编码，用以转换ISO-10646标准0x0080~0x07FF的UCS-4原始码；UTF-8的3字节编码，用以转换ISO-10646标准0x0800~0xFFFF的UCS-4原始码；UTF-8的4字节编码，用以转换ISO-10646标准0X00010000~0001FFFF的UCS-4原始码。

ISO-10646只是给每一个文字符号分配了一个4字节无符号整数编号(UCS-4)，并未规定在计算机中如何去表示这个无符号的整数编号。UTF-16和UTF-8就是其两种变通表示方式。

现在，Unicode和ISO-10646一般指的是同一个东西

UTF-16和UTF-32因字节序的不同，又有了UTF-16BE(B一个Endian), UTF-16LE(Little Endian), UTF-32BE(Big Endian), UTF-32LE(Little Endian)等

3.汉字及一些常见的字符集

GB 2312-80：全称《信息交换用汉字编码字符集基本集》，于1980年发布。根据ISO/IEC 2022提供的字符编码扩充规范，形成双字节编码的字符集。收录了常用汉字6763个和非汉字图形符号682个

GB13000:全称《信息技术通用多八位编码字符集(UCS) 第一部分：体系结构与基本多文种平面》，于1993年发布、根据ISO/IEC 10646-1:1993，在CJK（中日韩简称）统一汉字区和CJK统一汉字扩充区A，除收录GB 2312-80外，还收录了第1,3,5,7辅助集的全部汉字，宫27474个，以及一些偏旁部首等。但GB13000退出后，几乎没有得到业界的支持，也就成了一个形式上的标准

GBK:全称《汉字内码扩展规范》 1.0版，发布于1995年。GBK在GB2312内码系统上进行了扩充，收录了GB 13000.1-1993的全部20902个CJK统一汉字，包括GB2312的全部6763个汉字。此外，他增补编码了52个汉字，13个汉字结构符(在ISO/IEC 10646.1:2000中称为表意文字描述符)和一些常用部首与汉字部件。在GBK内码系统中，GB 2312汉字所在码位保持不变，这样，保证了GBK对GB 2312的完全兼容。同时，GBK内码与GB13000.1代码一一对应，为GBK向GB 13000.1的转换提供了解决办法。有意思的是GBK并不是一个强制性的国家标准，只是一个行业指导规范，并没有强制力，但由于得到了Microsoft Windows95的支持而大为流行

GB 18030:全称《信息技术信息交换用汉字编码字符集、基本集的扩充》，发布于2000年。根据ISO/IEC 10646-1:2000，收录了ISO/IEC 10646.1：2000全部27848个CJK统一汉字，13个表意文字描述符、部分汉字部首和部件、欧元符号等。GB 18030采用2字节或4字节编码，其二字节编码部分与GBK保持一致，因此，GB18030SHI GBK的超集，也完全与GB 13000向上兼容，制定GB 18030也是为了解决GBK强制力不够的问题

常用字符集比较

字符集	是否定长	编码方式	其他说明
ASCII	是	单字节7位编码	最早的奠基性字符集
ISO-8859-1/latin1	是	单字节8位编码	西欧字符集，经常被一些程序员用来转码
GB 2312-80	是	双字节编码	早起标准，不推荐再使用
GBK	是	双字节编码	虽然不是国标，但支持的系统不少
GB 18030	否	2字节或4字节编码	开始有一些支持，但数据库支持的还少见
UTF-32	是	4字节编码	UCS-4原始编码，目前很少采用
UCS-2	是	2字节编码	Windows2000内部用UCS-2
UTF-16	否	2字节编码或4字节编码	Java和Windows XP/NT等内部使用UTF-16
UTF-8	否	1~4字节编码	互联网和UNIX/Linux广泛支持的Unicode字符集；MySQLServer也使用UTF-8

4.选择合适的字符集

对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响

MySQL目前支持几十种字符集，包括UCS-2, UTF-16, UTF-16TE, UTF-32, UTF-8和utfmb64等Unicode字符集

满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同汉语言的国家和地区，就应该选择Unicode字符集、对MySQL来说，目前就是UTF-8

如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。

如果数据库只需要支持一般中文数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK。因为对UTF-8而言，GBK比较小，每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库Cache以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择UTF-8更好，因为GBK、UCS-2、UTF-16的西文字符编码都是2个字节，会造成很多不必要的开销

如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快

如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集，这样可以避免因字符集转换带来的性能开销和数据损失

5.MySQL支持的字符集简介

MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以指定使用不同的字符集，相比Oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL明显存在很大的灵活性。

查看所有可用的字符集的命令
　　　　　　SHOW CHARACTER SET

显示所有的字符集和该字符集默认的校对规则
　　　　　　DESC information_schema.character_set

MySQL的字符集包括字符集(CHARACTER)和校对规则(COLLAION)两个概念。其中字符集用来定义MySQL存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系，MySQL支持30多种字符集的70多种校对规则。每个字符集至少对应一个校对规则，可以用
　　　　　　SHOW COLLATION LIKE '***'
　　　　或通过表 information_schema.COLLATIONS来查看相关字符集的校对规则
校对规则命名约定：他们以其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写不敏感)，_cs(大小写敏感)，_bin(二元，即比较是基于字符编码的值而与language无关)结束

6.MySQL字符集的设置

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级、字段级。他们分别在不同的地方设置，作用也不相同

服务器字符集和校对规则
服务器字符集和校对规则，可以在MySQL服务启动的确定。
　　　　可以在 my.cnf 中设置
　　　　　　　　character-set-server=gbk
　　　　或者在启动选项中指定
　　　　　　　　mysqld --character-set-server=gbk
　　　　或者在编译时指定
　　　　　　　　cmake . -DEFAULT_CHARSET=gbk
如果没有特别的指定服务器字符集，那么默认使用latin1作为服务器字符集。上面3种设置的方式都只制定了字符集，没有指定校对规则，这样意味着使用该字符集默认的校对规则。如果要使用该字符集非默认的校对规则，则需要在指定字符集的同时指定校对规则。
查询当前服务器的字符集和校对规则
　　　　SHOW VARIABLES LIKE 'character_set_server'

数据库字符集和校对规则
数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过ALTER DATABASE命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。
设置数据库字符集的规则如下：
　　　　如果指定了字符集和校对规则。则使用指定的字符集和校对规则
　　　　如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
　　　　如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
　　　　如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则
推荐在创建数据库时明确指定字符集和校对规则，避免受到默认值的影响。
显示当前数据库默认的字符集和校对规则
　　　　SHOW VARIABLES LIKE 'character_set_database'
　　　　SHOW VARIABLES LIKE 'collation_database'

表字符集和校对规则
表的字符集和校对规则在创建表的时候指定，可以通过ALTER TABLE 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。
设置表的字符集的规则
　　　　如果指定了字符集和校对规则。则使用指定的字符集和校对规则
　　　　如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
　　　　如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
　　　　如果没有指定字符集和校对规则，则使用数据库字符集和校对规则作为数据库的字符集和校对规则
推荐在创建表的时候明确指定字符集和校对规则，以避免受到默认值得影响。
显示表的字符集和校对规则
　　　　SHOW CREATE TABLE table_name

列字符集和校对规则
MySQL可以定义列级别的字符集和校对规则，主要是针对相同的表不同字符需要使用不同的字符集情况，应该说一般遇到这种情况的几率比较小，这只是MySQL提供给我们一个灵活设置的手段。
列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。

连接字符集和校对规则
上面4种设置方式，确定的是数据保存的字符子和校对规则，对于实际应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。
对于客户端和服务器的交互操作，MySQL提供了3个不同的参数：
　　　　character_set_client　　　　客户端
　　　　character_set_connection　连接
　　　　character_set_results　　　返回结果的字符集
通常情况下，这3个字符集应该是相同的，才可以确保用户写入的数据可以正确的读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确的读出。
通常情况下，不会单个的设置这3个参数，可以通过以下命令：
　　　　SET NAMES ***
来设置连接的字符集和校对规则，这个命令可以同时修改这3个参数的值。使用这个方法修改连接的字符集和校对规则，需要应用每次连接数据库后都执行这个命令。
另一个更简便的方法，是在my.cnf中设置以下语句：
　　　　t-character-set=gbk
这样服务器启动后，所有连接默认就是使用GBK字符集进行连接的，而不需要在程序中在执行set names命令。
另外，字符串常量的字符集也是由character_set_connection参数来指定的
可以通过 [_charset_name] 'string' [COLLATE collation_name] 命令强制字符串的字符集和校对规则。通常情况下，基本不需要用户强制指定字符串字符集。

7.字符集的修改步骤

如果在应用开始阶段没有正确的设置字符集，在运行一段时间以后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。
字符集修改不能直接通过ALTER TABLE CHARACTER SET *** 或 ALTER TABLE table_name character set *** 命令进行，这两个命令都没有更新已有记录的字符集，而只是对创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。

选择目标字符集的时候，要注意最好是源字符集的超集，或者确定比源字符集的字库更大，否则如果目标字符集的字符小于源字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。
实例：
模拟将latin1字符集的数据库修改成GBK字符集的数据库的过程
1) 导出表结构
　　　　　　mysqldump -uroot -p --default-character-set=gbk -d databasename > createtab.sql
　　　　　其中--default-character-set=gbk表示设置以什么字符集连接，-d表示只导出表结构，不导出数据
2) 手工修改createtab.sql中表结构定义中的字符集为新的字符集
3) 确保记录不再更新，导出所有记录
　　　　　　mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql
　　　　　　--quick：该选项用于转出打的表。他强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将他缓存到内存中
　　　　　　--extended-insert：使用包括几个VALUES列表的多行INSERT语法。这样使转出文件更小，重载文件时可以加速插入
　　　　　　--no-create-info：不导出每个转储表的CREATE TABLE语句
　　　　　　--default-character-set=latin1：按照原有的字符集导出所有数据就，这样导出的文件中，所有中文都是可见的。不会保存成乱码
4) 打开data.sql，将SET NAMES latin1修改成SET NAMES gbk
5) 使用新的字符集创建新的数据库
　　　　　　CREATE DATABASE database_name
　　　　　　DEFAULT CHARSET charset_name
6) 创建表，执行 createtab.sql
　　　　　　mysql -uroot -p databasename < createtab.sql
7) 导入数据，执行data.sql
　　　　　　mysql -uroot -p databasename < data.sql