转至:http://blog.zol.com.cn/2404/article_2403287.html
为什么需要字符集?
从本质上来说,计算机只能识别二进制代码,因此,不论是计算机程序还是其处理的数据,最终都必须转换成二进制码,计算机才能认识。从这个意义上讲,计算机既不认识 English ,也不懂中文,根本就是个“文盲”!为了使计算机不仅能做科学计算,也能处理文字信息,人们想出了给每个文字符号编码以便于计算机识别处理的办法,这就是计算机字符集的由来。
字符集及其校对规则的概念
简单地说,字符集就是一套文字符号及其编码的集合。而校对规则是指一个字符集中比较排序规则的集合。
例如,对于符号“ A,B,a,b” ,我们对每一个符号分配一个数字编码: A=0,B=1,a=2,b=3 ,则这些符号及其编码就是一个字符集。
如果现在要比较上述字符集中的两个符号 A 和 B 的大小该怎么做呢?最简单的办法就是看其编码的大小,因为 B 的编码 1 大于 A 的编码 0 ,所以我们说 B 大于 A 。在上述比较过程中,我们实际上是定义了一条规则:通过比较符号的编码大小来确定符号的大小。这其实就是定义了一个字符集的校对规则,只不过这个校对规则中只有一条规则。
第一个计算机字符集 ---ASCII
1960 年代初期,美国标准化组织 ANSI 发布了第一个计算机字符集 ── ASCII ( American Standard Code for Information Interchange ),后来进一步变成了国际标准 ISO-646 。这个字符集采用 7 位编码,定义了包括大小写英文字母、阿拉伯数字和标点符号,以及 33 个控制符号等。虽然现在看来,这个美式的字符集很简单,包括的符号也很少,但直到今天它依然是计算机世界里奠基性的标准,其后制定的各种字符集基本都兼容 ASCII 字符集。
自 ASCII 之后,为了处理不同的文字,各大计算机公司、各国政府、标准化组织等先后发明了几百种字符集,如大家熟悉的 ISO-8859 系列、 GB2312-80 、 GBK 、 BIG5 等。这些五花八门的字符集,从收录的字符到编码规则各不相同,给计算机软件开发和移植带来了很大困难。一个软件要在使用不同文字的国家或地区发布,必须进行本地化开发!基于这个原因,统一字符编码,成了 1980 年代计算机业的迫切需要和普遍共识。
unicode 简述
为了统一字符编码,国际标准化组织 ISO ( International Organization for Standardization )的一些成员国于 1984 年发起制定新的国际字符集标准,以容纳全世界各种语言文字和符号。这个标准最后叫做 “ Universal Multiple-Octet Coded Character Set ” ,简称 UCS ,标准编号则定为 ISO-10646 。 ISO-10646 标准采用 4 字节( 32bit )编码,因此简称 UCS-4 。具体编码规则是:将代码空间划分为组( group )、面( plane )、行( row )和格( ceil );第 1 个字节代表组( group ),第 2 个字节代表面( plane ),第 3 个字节代表行( row ),第 4 个字节代表格( ceil ),并规定字符编码的第 32 位必须为 0 ,且每个面( plane )的最后两个码位 FFFEh 和 FFFFh 保留不用;因此, ISO-1064 共有 128 个群组( 0 ~ 0x7F ),每个群组有 256 个字面( 00 ~ 0xFF ),每个字面有 256 行( 00 ~ 0xFF ),每行包括 256 格( 0 ~ 0xFF ),共有 256 * 128 = 32,768 个字面,每个字面有 256×256 - 2 = 65,534 个码位,合计 65534×32768 = 2,147,418,112 个码位。
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 。
都是为了解决字符编码统一问题, ISO 和 Unicode 协会却推出了两个不同的编码标准,这显然是不利的。后来,大家都认识到了这一点,经过双方谈判, 1991 年 10 月达成协议, ISO 将 Unicode 编码并入 ISO-10646 的 0 组 0 字面,叫作基本多语言文字面( Basic Multi-lingual Plane, BMP ),共有 65,534 个码位,并根据不同用途分为若干区域。除 BMP 外的 32,767 个字面又分为辅助字面( supplementary planes )和专用字面( private use planes )两部分,辅助字面用以收录 ISO-10646 后续搜集的各国文字,专用字面供使用者自定义收录 ISO-10646 未收录的文字符号。其实,大部分用户只使用 BMP 字面就足够了,早期的 ISO-10646-1 标准也只要求实现 BMP 字面,这样只需要 2 字节来编码就足够了, Unicode 也正是这么做的,这叫作 ISO-10646 编码的基本面形式,简称为 UCS-2 编码, UCS-2 编码转换成 UCS-4 编码也很容易,只要在前面加两个取值为 0 的字节即可。
ISO-10646 的编码空间足以容纳人类从古至今使用过的所有文字和符号,但其实许多文字符号都已经很少使用了,超过 99% 的在用文字符号都编入了 BMP ,因此,绝大部分情况下, Unicode 的双字节编码方式都能满足需求,而这种双字节编码方式比起 ISO-10646 的 4 字节原始编码来说,在节省内存和处理时间上都具有优势,这也是 Unicode 编码方式更流行的原因。但如果万一要使用 ISO-10646 BMP 字面以外的文字怎么办呢? Unicode 提出了名为 UTF-16 或代理法( surrogates )的解决方案, 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 就是其两种变通表示方式。
ISO-10646 与 Unicode 统一以后,两个组织虽然都继续发布各自的标准,但二者之间是一致的。由于 Unicode 最早投入应用,其编码方式更加普及,因此,许多人都知道 Unicode ,但对 ISO- 10646 却了解不多。但由于二者是一致的,因此,区分 ISO-10646 和 Unicode 的意义也就不大了。现在,大家说 Unicode 和 ISO-10646 ,一般指的是同一个东西,只是 Unicode 更直接、更普及罢了。二者不同版本的对应关系如下。
- Unicode 2.0 等同于 ISO/IEC 10646-1:1993 。
- Unicode 3.0 等同于 ISO/IEC 10646-1:2000 。
- Unicode 4.0 等同于 ISO/IEC 10646:2003 。
最后要说的是, UTF-16 和 UTF-32 因字节序的不同,又有了 UTF-16BE ( Big Endian )、 UTF-16LE ( Little Endian )和 UTF-32BE ( Big Endian )、 UTF-32LE ( Little Endian )等
常见汉字字符集简介
在计算机发展的不同阶段,我国也参照当时的国际标准和实际需要,制定了一些汉字字符集编码标准,主要包括:
- GB2312-80 :全称《信息交换用汉字编码字符集 基本集》,于 1980 年发布。根据 ISO/IEC 2022 提供的字符编码扩充规范,形成双字节编码的字符集。收录了 6763 个常用汉字和 682 个非汉字图形符号。
-
GB13000 :全称《信息技术 通用多八位编码字符集 (UCS) 第一部分:体系结构与基本多文种平面》,于 1993 年发布。根据 ISO/IEC 10646-1:1993 ,在 CJK (中、日、韩简称)统一汉字区和 CJK 统一汉字扩充区 A ,除收录 GB2312-80 外,还收录了第 1 、 3 、 5 、 7 辅助集的全部汉字,共 27 , 484 个,以及一些偏旁部首等。但 GB13000 推出后,几乎没有得到业界的支持,也就成了一个形式上的标准。
-
GBK :全称《汉字内码扩展规范》 1.0 版,发布于 1995 年。 GBK 在 GB2312 内码系统的基础上进行了扩充,收录了 GB13000.1-1993 的全部 20902 个 CJK 统一汉字,包括 GB2312 的全部 6763 个汉字。此外,它增补编码了 52 个汉字, 13 个汉字结构符(在 ISO/IEC 10646.1: 2000 中称为表意文字描述符)和一些常用部首与汉字部件。在 GBK 的内码系统中, GB2312 汉字所在码位保持不便,这样,保证了 GBK 对 GB2312 的完全兼容。同时, GBK 内码与 GB13000.1 代码一一对应,为 GBK 向 GB13000.1 的转换提供了解决办法。有意思的是 GBK 并不是一个强制性的国家标准,只是一个行业指导规范,并没有强制力,但由于得到了 Microsoft Windows 95 的支持而大为流行。
- GB18030 :全称《信息技术信息交换用汉字编码字符集、基本集的扩充》,发布于 2000 年。根据 ISO/IEC 10646-1:2000 ,收录了 ISO/IEC 10646.1: 2000 全部 27,484 个 CJK 统一汉字, 13 个表意文字描述符、部分汉字部首和部件、欧元符号等。 GB18030 采用 2 字节或 4 字节编码,其二字节编码部分与 GBK 保持一致,因此, GB18030 是 GBK 的超集,也完全与 GB13000 向上兼容,制定 GB18030 也是为了解决 GBK 强制力不够的问题。
以上简要介绍了几种汉字字符集,下面将一些常用字符集的特点归纳如表 2.2-1 所示。
表 2.2-1 常用字符集比较
字符集 | 是否定长 | 编码方式 | 其他说明 |
ACSII | 是 | 单字节 7 位编码 | 最早的奠基性字符集 |
ISO-8859-1/latin1 | 是 | 单字节 8 位编码 | 西欧字符集,经常被一些程序员用来转码 |
GB2312-80 | 是 | 双字节编码 | 早期标准,不推荐再使用 |
GBK | 是 | 双字节编码 | 虽然不是国标,但支持的系统不少 |
GB18030 | 否 | 2 字节或 4 字节编码 | 开始有一些支持,但数据库支持的还少见 |
BIG5 | 是 | 双字节编码 | 繁体汉字字符集,由台湾 5 公司联合制定,因此又叫大五码,曾经是港奥台等繁体字地区的首选。 |
UTF-32 | 是 | 4 字节编码 | UCS-4 原始编码,目前很少采用 |
UCS-2 | 是 | 2 字节编码 | Windows 2000 内部用 UCS-2 |
UTF-16 | 否 | 2 字节或 4 字节编码 | Java 和 Windows XP/NT 等内部使用 UTF-16 |
UTF-8 | 否 | 1 至 4 字节编码 | 互联网和 UNIX/Linux 广泛支持的 Unicode 字符集; MySQLServer 也使用 UTF-8 |
选择字符集的一般原则
对数据库来说,字符集更加重要,因为数据库存储的数据大部分都是各种文字,字符集对数据库的存储、处理性能,以及日后系统的移植、推广都会有影响。
MySQL 5.0 目前支持几十种字符集, UTF-8 是 MySQL 5.0 支持的唯一 Unicode 字符集,但版本是 3.0 ,不支持 4 字节的扩展部分。面对众多的字符集,我们该如何选择呢?
虽然没有一定之规,但在选择数据库字符集时,可以根据应用的需求,结合上面介绍的一些字符集的特点来权衡,主要考虑因素包括:
( 1 )满足应用支持语言的需求,如果应用要处理各种各样的文字,或者将发布到使用不同语言的国家或地区,就应该选择 Unicode 字符集。对 MySQL 来说,目前就是 UTF-8 。
( 2 )如果应用中涉及已有数据的导入,就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字,如果选择 GB2312-80 为数据库字符集,就很可能出现某些文字无法正确导入的问题。
( 3 )如果数据库只需要支持一般中文,数据量很大,性能要求也很高,那就应该选择双字节定长编码的中文字符集,比如 GBK 。因为,相对于 UTF-8 而言, GBK 比较“小”,每个汉字只占 2 个字节,而 UTF-8 汉字编码需要 3 个字节,这样可以减少磁盘 I/O 、数据库 cache ,以及网络传输的时间,从而提高性能。相反,如果应用主要处理英文字符,仅有少量汉字数据,那么选择 UTF-8 更好,因为 GBK 、 UCS-2 、 UTF-16 的西文字符编码都是 2 个字节 , 会造成很大不必要的开销。
( 4 )如果数据库需要做大量的字符运算,如比较、排序等,选择定长字符集可能更好,因为定长字符集的处理速度要比变长字符集的处理速度快。
( 5 )如果所有客户端程序都支持相同的字符集,应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。
MySQL 支持的字符集
MySQL 服务器可以支持多种字符集,在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集,相比 Oracle 等其他数据库管理系统,在同一个数据库只能使用相同的字符集, MySQL 明显存在更大的灵活性。
查看所有可用的字符集的命令是 show character set :
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Decript:ion | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
……
或者查看 information_schema.character_set ,可以显示所有的字符集和该字符集默认的校对规则。
mysql> desc information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |
| DEcript:ION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MySQL 的字符集包括字符集( CHARACTER )和校对规则( COLLATION )两个概念。字符集和校对规则是一对多的关系, MySQL 支持 30 多种字符集的 70 多种校对规则。
每个字符集至少对应一个校对规则。可以用“ SHOW COLLATION LIKE '***'; ”命令或者查看 information_schema.COLLATIONS 。查看相关字符集的校对规则。
mysql> SHOW COLLATION LIKE 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,一个说明性后缀,如 _ci 后缀,表示大小写不敏感, _cs 后缀,表示大小写敏感, _bin 后缀表示二进制代码比较,即比较是基于字符编码的值而与 language 无关等。
例如,上面例子中 GBK 的校对规则,其中 gbk_chinese_ci 是默认的校对规则,大小写不敏感的, gbk_bin 按照编码的值进行比较,是大小写敏感的。
下面的这个例子中,如果指定 'A' 和 'a' 按照 gbk_chinese_ci 校对规则进行比较,则认为两个字符是相同的,如果按照 gbk_bin 校对规则进行比较,则认为两个字符是不同的。我们事先需要确认应用的需求,是需要按照什么样的排序方式,是否需要区分大小写,以确定校对规则的选择。
mysql> select case when 'A' COLLATE gbk_chinese_ci = 'a' collate gbk_chinese_ci then 1 else 0 end;
+--------------------------------------------------------------------------------------+
| case when 'A' COLLATE gbk_chinese_ci = 'a' collate gbk_chinese_ci then 1 else 0 end |
+--------------------------------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select case when 'A' COLLATE gbk_bin = 'a' collate gbk_bin then 1 else 0 end;
+------------------------------------------------------------------------+
| case when 'A' COLLATE gbk_bin = 'a' collate gbk_bin then 1 else 0 end |
+------------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
服务器字符集
服务器字符集和校对规则用做 mysql 数据库的默认字符集和校对规则。服务器字符集和校对规则可以在 MySQL 配置文件 my.cnf 中设置:
[mysqld]
Character_set_server=gbk
Collation_server=gbk_chinese_ci
或
[mysqld]
default-character-set=gbk
也可以通过命令行参数指定:
mysqld –-character_set_server=gbk –collation_server=gbk_chinese_ci
或
mysqld --default-character-set=gbk
在数据库启动后,也可以进行动态修改:
Set character_set_server = utf8;
Set collation_server=utf8_bin
如果没有特别指定服务器字符集,默认使用 latin1 作为服务器字符集,如果不指定校对规则,则使用该字符集默认的校对规则。服务器字符集的默认校对规则是在编译时指定的,如果你希望改变默认的服务器字符集,就需要在编译前执行以下配置指令:
./configure --with-charset=gbk
./configure –with_collation=gbk_chinese_ci
可以用“ show variables like 'character_set_server'; ”命令查询当前服务器的字符集和校对规则。
mysql> show variables like 'character_set_server';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | gbk |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'collation_server';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| collation_server | gbk_chinese_ci |
+------------------+----------------+
1 row in set (0.00 sec)
数据库字符集
数据库的字符集和校对规则用做数据库表的默认字符集和校对规则,除此以外, load data infile 语句也会使用数据库字符集和校对规则。
数据库的字符集和校对规则在创建数据库的时候指定,也可以通过“ alter database ”命令动态修改数据库的字符集和校对规则。但需要注意的是,字符集和校对规则的修改,只对此后该数据库中新建的表生效,对于数据库里已经存在的表,修改数据库字符集并不会改变其原有的字符集和校对规则。
MySQL 设置数据库字符集的规则是:
-
如果指定了字符集和校对规则,则使用指定的字符集和校对规则;
-
如果指定了字符集没有指定校对规则,则使用指定字符集的默认校对规则;
-
如果没有指定字符集和校对规则,则使用服务器字符集和校对规则作为数据库的字符集和校对规则。
推荐在创建数据库的时候明确指定字符集和校对规则,避免受到默认值的影响。
要显示当前数据库的字符集和校对规则,可以使用“ show variables like ' character_set_database ' ”和“ show variables like ' collation_database ' ” 命令查看 :
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)
表字符集
表字符集和校对规则用做 mysql 表字段的默认字符集和校对规则。表字符集和校对规则是 mysql 的扩展,不是标准 SQL 定义的内容。
表的字符集和校对规则在创建表的时候指定,也可以通过 alter table 命令进行修改,特别要注意,如果表中已有记录,修改字符集对原有的记录并没有影响,不会自动将其转换成新的字符集,表的字段仍然使用原来的字符集,只是在新增字段时,如果不特别指定字符集和校对规则, MySQL 会使用新的表字符集和校对规则。
设置表的字符集的规则和上面基本类似:
-
如果指定了字符集和校对规则,使用指定的字符集和校对规则;
-
如果指定了字符集没有指定校对规则,使用指定字符集的默认校对规则;
-
如果没有指定字符集和校对规则,使用数据库字符集和校对规则作为表的字符集和校对规则。
推荐在创建表的时候明确指定字符集和校对规则,避免受到默认值的影响。要显示表的字符集和校对规则,可以使用 show create table 命令查看:
mysql> show create table z1 \G;
*************************** 1. row ***************************
Table: z1
Create Table: CREATE TABLE `z1` (
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
列字符集
MySQL 可以定义列级别的字符集和校对规则,主要是为了在特殊的情况下,对同一表的不同字段使用不同的字符集,以达到空间及性能优化的目的。应该说一般遇到这种情况的几率比较小,这只是 MySQL 提供给我们一个灵活设置的手段。
列字符集和校对规则的定义可以在创建表时指定,如果在创建表的时候没有特别指定字符集和校对规则,则默认使用表的字符集和校对规则。
列字符集和校对规则可以通过 alter table 命令进行修改,但与修改数据库和表字符集不同, mysql 会自动将该列已有的数据转换成新的字符集,如果新旧字符集不兼容,将造成数据损失!
客户字符集,连接字符集及结果字符集
以上提到的几种字符集和校对规则,都是 mysql 数据库内部的字符集和校对规则。在实际的应用访问 MySQL 数据库的交互过程中,使用什么字符集呢?
实际上,对于客户端和服务器的交互操作, MylSQL 提供了 3 个不同的参数:客户字符集( character_set_client )、连接字符集( character_set_connection )和结果字符集( character_set_results ),分别用来指定客户端使用的字符集,客户端与 MySQL Server 通讯连接过程中使用的字符集,以及返回结果的字符集。客户端与 MySQL 的交互过程大致是:
-
MySQL 客户接口程序通过 character_set_client 确定客户端 sql 语句使用的字符集。
-
MySQL 将客户端发送的 sql 语句字符集由 character_set_client 转换成 character_set_connection 指定的字符集(带有字符集引导符的除外)。
-
MySQL 将处理结果,如查询结果集,转换成 character_set_results 指定的字符集,并返回给客户端。
通常情况下,这 3 个字符集应该是相同的。如果这三个字符集设置不同,不仅会增加字符集转换的开销,而且可能因设置不当,导致字符集转换时因丢失数据而产生错误。
这三个参数可以单独设置,但一般不这么做,更多的是通过以下命令来一次设置上述三个参数:
SET NAMES ***;
使用这个方法修改连接的字符集和校对规则,需要应用每次连接数据库后都执行这个命令。
另外,也可以在客户程序配置文件 my.cnf 中设置这三个字符集,例如:
[mysql]
default-character-set=gbk
这样服务器启动后,所有连接默认就是使用 GBK 字符集进行连接的,而不需要在程序中再执行 set names 命令。
最后,字符串常量如果没有明确的字符集引导符,其字符集也是由 character_set_connection 参数来决定的。可以通过“ [_charset_name]'string' [COLLATE collation_name] ”命令强制字符串的字符集和校对规则。例如:
select _gbk ' 字符集 ';
select _latin1 ' 字符集 ';
当然,一般情况下,基本不需要显式指定字符串的字符集
字符集相关的 MySQL 函数和操作符
许多 MySQL 函数都和字符集相关,如 MySQL 函数返回的字符串也都有字符集属性,该属性是从其输入参数或 character_set_connection 参数继承来的。下面几个函数和操作符,更是与字符集密切相关的。
-
Binary
Binary 是一个操作符,它的作用是将紧跟其后的字符串强制转换成“二进制字符串”。 Binary 字符串没有字符集属性,因此比较是大小写敏感的,例如:
默认情况下,字符串比较是忽略大小写的
Mysql> select ‘test’=’TEST’;
-> 1
如果加上 binary 操作符,比较就变成了大小写敏感的了:
Mysql> select binary ‘test’=’TEST’;
-> 0
必要要时,使用 binary 操作做大小写敏感的比较,非常方便。
-
Convert(expr using transcoding_name)
使用带 using 的 convert 函数,我们可以对数据在不同字符集间进行转换,例如:
mysql> select charset(' 测试 ');
+-----------------+
| charset(' 测试 ') |
+-----------------+
| gbk |
+-----------------+
1 row in set (0.00 sec)
mysql> select charset(convert(' 测试 ' using utf8));
+-------------------------------------+
| charset(convert(' 测试 ' using utf8)) |
+-------------------------------------+
| utf8 |
+-------------------------------------+
1 row in set (0.01 sec)
-
Charset ()
Charset() 函数返回其输入参数的字符集。例如:
mysql> select charset(' 测试 ');
+-----------------+
| charset(' 测试 ') |
+-----------------+
| gbk |
+-----------------+
1 row in set (0.01 sec)
- Collation()
Collation() 函数返回其输入参数的校对规则。例如:
mysql> select collation(' 测试 ');
+-------------------+
| collation(' 测试 ') |
+-------------------+
| gbk_chinese_ci |
+-------------------+
1 row in set (0.00 sec)
-
字符串长度函数: length() 和 character_length()
MySQL 有两个返回字符串长度的函数:一个是 length() ,其返回的字符串长度是以字节( byte )为单位的;另一个是 character_length() ,其返回的字符串长度是以字符个数为单位的。这两个函数是有区别的:对于多字节字符集,如 GBK , length ()每一个汉字的长度是 2 ,因为 GBK 的编码是 2 字节的;而 character_length() 函数每个汉字的长度是 1 ,因为 1 个汉字就是 1 个符号。例如:
mysql> select length(' 测试 ');
+----------------+
| length(' 测试 ') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select character_length(' 测试 ');
+--------------------------+
| character_length(' 测试 ') |
+--------------------------+
| 2 |
+--------------------------+
1 row in set (0.00 sec)
修改字符集引起的问题
前面提到, MySQL 的数据库、表、表中的每个列都有各自的字符集属性,分别可以在创建数据库、创建表和定义列时指定。在创建以后,可以随时进行修改。但需要注意三点:一是修改数据库的字符集,只是改变了在该数据库创建新表时使用的默认字符集,并不会对数据库中已有表的定义和数据产生任何影响,也就是说现存表的字符集不会发生任何改变;二是修改表的字符集,只是改变了在该表增加新列时使用的默认字符集,并不会对表中已有列的定义和数据产生任何影响,也就是说表中已有列的字符集不会发生任何改变;最后,修改列字符集,不仅会修改该列的字符集定义,而且会将该列的现有数据转换成目标字符集!最后一点要特别注意,因为它需要进行字符集转换,在数据量很大时,会引起性能问题,而且,在目标字符集不是原字符集超集的情况下,贸然进行修改,还会导致数据损失!例如下面的例子中,需要将 name 列的字符集由 UTF8 改成 GB2312 ,但由于没有确认该列中的数据是否全部可以进行无损转换,就导致了部分汉字信息丢失!
表中原来的数据:
mysql> select name,charset(name) from test;
+--------+---------------+
| name | charset(name) |
+--------+---------------+
| 香水 | utf8 |
| 粉底 | utf8 |
| 啫喱氺 | utf8 |
+--------+---------------+
3 rows in set (0.00 sec)
修改列字符集:
alter table test modify name varchar(40) charset gb2312;
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 1
修改列字符集后,部分汉字数据损失:
mysql> select name,charset(name) from test;
+------+---------------+
| name | charset(name) |
+------+---------------+
| 香水 | gb2312 |
| 粉底 | gb2312 |
| ? 喱 ? | gb2312 |
+------+---------------+
3 rows in set (0.00 sec)
数据迁移中的字符集问题
在数据迁移过程中,经常会出现各种字符集问题,特别是在不同种类数据库间迁移数据,更是如此。数据迁移过程中出现的字符集问题,大致有两类:一是目标字符集与原数据库的字符集不兼容,造成数据迁移后,部分数据变成了 ”?” (其实是数据库无法完成字符集转换,只好用“?”来代替);二是因目标表的列宽不够,导致数据截断,造成部分数据损失。
校对规则问题
一般情况下,只要使用字符集的默认校对规则就可以了,所以大家很少关注校对规则。但校对规则对字符比较和排序都有影响。看下面的例子:
有一个表 tes t ,其中的一个字段 value 是 text 类型:
mysql> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | text | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
应用程序中经常要根据 value 的值来查询其 ID :
mysql> select id from test where value like 'cambridge%';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
在一次数据迁移的过程中,因为各种原因误将其类型变成了 blob :
mysql> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | blob | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
数据导入的过程中一切正常,没有出现任何问题,但系统上线后不久,就接到反馈说:许多以前存在的数据,现在却查不到了!例如:
mysql> select id from test where value like 'cambridge%';
Empty set (0.00 sec)
这是一个真实的案例,只是本文将问题简化了而已。为了查找问题的原因,当时花了很长时间,最后才找到问题症结。原因是类型变成 blob 后,校对规则发生了变化,原来的 text 类型有字符集,比较中使用其字符集默认的校对规则,该校对规则是不区分大小写的,无论用户输入大写或是小写字符串查询都可以。但类型改成 blob 后,字符串比较就变成了二进制串的比较,是大小写敏感的,如果用户输入的查询条件与数据的大小写不匹配,就无法查出数据!
其实, mysql 的二进制数据类型 BINARY, VARBINARY, BLOB 都没有字符集的概念,其比较都是二进制位串比较,区分大小写。这与 CHAR,VARCHAR,TEXT 数据类型不同,需要特别注意。
汉字“乱码”问题
汉字“乱码”可能是最常见和最令人头痛的现象。其实质是汉字在传送、处理过程中需要进行字符集转换,但由于字符集不兼容或设置不当等原因,造成无法完成字符集转换,或者进行了错误的转换。看下面的例子。
某用户反映查询 mysql 表 test 出现汉字乱码:
mysql> select * from test;
+------+
| name |
+------+
| ?? |
| ?? |
+------+
2 rows in set (0.00 sec)
查看表定义,一切正常:
mysql> show create table test;
+-------+-----------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------+
| test | CREATE TABLE `test` (
`name` varchar(40) character set gbk default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------+
问题出在哪呢?检查其 session 的字符集设置:
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
可见其 character_set_client 、 character_set_connection 和 character_set_results 的设置是 mysql 默认的 latin1 。修改上述设置:
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
重新执行查询,汉字显示恢复正常:
mysql> select * from test;
+------+
| name |
+------+
| 张三 |
| 李四 |
+------+
2 rows in set (0.00 sec)
上述例子虽然简单,但许多汉字乱码问题,本质上都与此类似,只要认真分析汉字传递处理过程中的转换过程,检查相应环节的字符集相关设置,都不难找出问题所在