字符集及其校对规则

转至: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)

上述例子虽然简单,但许多汉字乱码问题,本质上都与此类似,只要认真分析汉字传递处理过程中的转换过程,检查相应环节的字符集相关设置,都不难找出问题所在








 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值