按:只要接触了电脑,乱码问题总会遇到过。这是一个让人恼火的问题。如果对字符编码一知半解,乱码就仿佛一种神秘咒语,似乎一不小心就触怒了电脑爷,扔出一堆天书般的乱码来;而如果深入理解了字符编码,各种编码在你看来就会不一不异,而一切乱码问题都不过是浮云。
本文不是专门介绍字符编码的文章,只是谈一下与SQLServer中文处理相关的字符编码和排序规则,希望对各位SQLServer玩家有所帮助。
首先插句题外话:创建一个自然数表Nums。这是《SQL Server 2005技术内幕:T-SQL查询》一书的建议。
在SQL Server 2005中,可以借用ROW_NUMBER排名函数轻松生成我们所需的自然数表:
以上语句生成前100万个自然数。
以下开始正题。
一、字符编码与排序规则
做过Web开发的人对字符编码一定不陌生。简单来说,人所能够识别的字符如“A”、“一”与计算机内部操作的数字01000001、1101001010111011是不一样的,需要建立一种对应关系来让计算机能够“识别”人们所使用的字符(或者说是让人们能够用自己习惯的方式识别计算机操作的数字),字符编码就是这个对应关系。
对于英语来说,大小写字母加数字加标点符号,总共也不会超过128个,一个字节就够用了;ASCII编码只使用了一个字节中的7位,便已经包括了英语常用字符,还加上了一组电传打字机时代的控制字符(至今仍在使用其中几个)。
然而世上并不仅有英语。欧洲一些语言需要使用的一些重音字符并没有包括在ASCII编码中;而亚洲的CJK(指China+Japan+Korea)语言字符多达几万个,更是远远超过了一个字节所能表示的范围;再加上阿拉伯语、希伯来语等等……
解决办法自然是扩充字符编码位数。双字节可以表示65536个字符,通常情况下是足够了。但这时又有一个新的问题:当计算机读到两个连续的字节,它应该将之理解为两个单独的字符还是一个字符?编码方案需要解决这个问题。
第一种方案是微软引入的 代码页的概念。ASCII只使用了一个字节的7位,字节最高位是0,那么可以用最高位是1的范围来表示扩展字符。对于多数欧洲语言,一个字节的256个字符已然足够,那么便用字节最高位是1的128个字符来表示如重音字符、制表符等扩展字符;对于亚洲语言,使用两个连续的最高位是1的字节来表示CJK字符,这样,当计算机读到一个最高位是0的字符,便知道将之解释为单字节的ASCII编码,当计算机读到一个最高位是1的字符,便知道要将这个字符与下一个字符一起来解释为一个相应的CJK字符;对于其他语言的处理方法类似(具体不甚了解,无法详述^_^|||)。
由于不同语言对最高位是1的字节解释不同,因此需要一个系统设置来进行区分,这便是代码页(Code Page)。在Windows系统中进行区域与语言设置可以设定默认代码页(还需要安装相应的字符集来支持),如简体中文是代码页936,简称cp936。除微软这套事实标准外,中国也制订有几个国家标准字符编码,如GB2312、GBK、GB18030,具体联系和区别可以Google之。一般情况下,cp936可以与GBK近似等价地看待。
这种方案的弊端有二:第一个问题是编码方案依赖于系统设置,这便导致不同系统之间可能无法兼容,一个常见的问题便是在一台电脑上保存的文本文件复制到另一台不同代码页设置的电脑上会显示乱码。第二个问题是字符处理的难度增加,比如常见的字符串计算长度、截取子串等操作,由于每个字符的实际字节数不同,便无法直接按地址偏移量计算,需要依次识别每一个字符的长度,这无疑会降低效率。
由此产生的第二种方案便是 Unicode,一个类似于巴别塔(Babel)的计划。准确地说,Unicode组织与国际标准化组织的ISO-10646工作组很有默契地共同制订编码方案,但又独立颁布各自的标准。两者的编码方案基本兼容,但在实际应用中却有两种不同的实现方案:通用编码转换格式(Unicode Translation Format, UTF)和通用字符集(Universal Character Set, UCS),前者在名称后加一个编码所用位数,如UTF-8、UTF-16、UTF-32,后者在名称后加一个编码所用字节数,如UCS-2、UCS-4。其中,UCS-2是UTF-16的子集,对应后者中的双字节编码,该字符集又被称为基本多语言平面(Basic Multilingual Plane, BMP);UCS-4和UTF-32是等价的。
目前使用最多的Unicode编码主要是UTF-8和UTF-16(UCS-2)。其中UTF-8是一种以8位为单元的变长编码方案,其单字节编码部分与ASCII完全兼容,汉字部分主要是三个字节的编码;事实上,通常语境中提到Unicode,所指的往往是UCS-2,即UTF-16中的BMP双字节编码子集。
UCS-2采用双字节编码又会存在另一个问题:由于CPU处理字节的顺序不同,相邻两个字节,比如0x4E59,在Mac机(PowerPC、68000等芯片)上会解释为U+4E59(乙),而在PC机(x86等芯片)上会解释为U+594E(奎);其中,前者被称为大端(Big-Endian),后者被称为小端(Little-Endian),这组概念来自于《格列佛游记》一书中描述的小人国战争,战争的起因是关于吃鸡蛋应该从大的一头(Big-Endian)还是从小的一头(Little-Endian)敲开。Unicode的处理措施是引入一个特殊字符U+FEFF,称为BOM(Byte Order Mark),相反的U+FFFE在Unicode中是不存在的。通过在一个文本的开头写一个BOM,比如0xFEFF4E59,这样程序就可以知道这是一个大端格式的文本。
UTF-8因为是以8位字节为单元,因而不存在字节序的问题。但有些程序也会在UTF-8格式的文本开头加上BOM(U+FEFF对应的UTF-8编码是0xEFBBBF),但这有时会给文本解析带来一些困扰。详见http://en.wikipedia.org/wiki/Byte_Order_Mark。
在SQLServer中,还有一个排序规则的概念,即对字符串进行比较和排序的规则。事实上,SQLServer安装程序中进行的排序规则设置,包含了字符集、字符串排序规则和系统区域设置。除了在安装程序过程中进行的服务器级设置,还有数据库级、列级和表达式级,这四个级别中,后面级别的默认设置依赖于前一级的设置,但在后面级别中特别指定则可以覆盖默认设置。
通常情况下,大陆的简体中文的系统会指定Chinese_PRC_CI_AS为默认排序规则,区域设置LCID为2052(0x804),字符集代码页为936。在这样设置的SQLServer服务器中,nchar/nvarchar使用UCS-2编码(这是独立于排序规则的),char/varchar使用cp936(近似GBK)编码,以上字符串均按不区分大小写(CI)、区分重音(AS)、不区分假名、不区分全半角的方式排序,其中重音和假名对中文来说不必关心。
排序规则影响所有与字符串比较相关的语句,包括各种排序(GROUP BY/PARTITION BY/ORDER BY)、索引内部存储、字符串的比较(=、>、>=、<、<=、<>、LIKE)。特别需要强调的是,LIKE字符串匹配中的范围如'[A-Z]',也依赖于指定的排序规则。
关于SQLServer排序规则的详细说明,可参看联机帮助中的“COLLATE”相关文档。
以下查询,显示中文系统中常用字符及其在常见排序规则下的表现:
二、中文字符相关的匹配
如上面查询所示, 在UCS-2中,19968至40869是中文字符:
全角标点字母数字的范围是65281至65374,全角空格需要特殊处理:
因而,想要匹配一个包含中文字符的字符串可用如下语句:
LIKE N'%[一-龥]%' COLLATE Chinese_PRC_BIN
或是:
LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
这是因为在以上两种不同的排序规则下,汉字的排列顺序是不同的。
类似,想要匹配全角标点字母数字:
LIKE N'%[!-~]%' COLLATE Chinese_PRC_BIN
三、全角与半角的转换
全角(Full-width)与半角(Half-width),是对CJK字符进行打印处理时引入的概念。相对于英文中的标点、字母、数字的单宽度,通常中日韩的文字都是双宽度,当需要混排CJK字符和英文的标点字母数字时,由于字符宽度不同,可能打印效果就不美观(特别是以传统的竖排方式打印时),由此引入了全角的标点字母数字,与单宽度的英文标点字母数字一一对应,而宽度则与一般的CJK字符相同。
由此带来的问题是,计算机和互联网程序往往只识别英文的标点字母数字,如URL、Email、电话号码、以及各种编程语言中的关键字和操作符,倘若在这些地方误用了全角的字符,程序往往无法处理。(这个问题也可以看做是没有做到内容与表现分离带来的复杂度)
以数据库系统为例,好的设计应该是在前端界面处加以验证和提示,只允许有效的数据进入数据库。然而倘若由于历史代码问题,系统引入了格式不好的数据,可能会需要在数据库中进行全角与半角的转换。
根据全半角字符的排列规律,可以用T-SQL实现这样的函数,以下为两个示例:
本文不是专门介绍字符编码的文章,只是谈一下与SQLServer中文处理相关的字符编码和排序规则,希望对各位SQLServer玩家有所帮助。
首先插句题外话:创建一个自然数表Nums。这是《SQL Server 2005技术内幕:T-SQL查询》一书的建议。
在SQL Server 2005中,可以借用ROW_NUMBER排名函数轻松生成我们所需的自然数表:
1
2
3
4
5
6
7
8
9
10
11
|
--自然数表1-1M
CREATE
TABLE
Nums(n
int
NOT
NULL
PRIMARY
KEY
CLUSTERED)
WITH
B1
AS
(
SELECT
n=1
UNION
ALL
SELECT
n=1),
--2
B2
AS
(
SELECT
n=1
FROM
B1 a
CROSS
JOIN
B1 b),
--4
B3
AS
(
SELECT
n=1
FROM
B2 a
CROSS
JOIN
B2 b),
--16
B4
AS
(
SELECT
n=1
FROM
B3 a
CROSS
JOIN
B3 b),
--256
B5
AS
(
SELECT
n=1
FROM
B4 a
CROSS
JOIN
B4 b),
--65536
CTE
AS
(
SELECT
r=ROW_NUMBER() OVER(
ORDER
BY
(
SELECT
1))
FROM
B5 a
CROSS
JOIN
B3 b)
--65536 * 16
INSERT
INTO
Nums(n)
SELECT
TOP
(1000000) r
FROM
CTE
ORDER
BY
r
|
以上语句生成前100万个自然数。
以下开始正题。
一、字符编码与排序规则
做过Web开发的人对字符编码一定不陌生。简单来说,人所能够识别的字符如“A”、“一”与计算机内部操作的数字01000001、1101001010111011是不一样的,需要建立一种对应关系来让计算机能够“识别”人们所使用的字符(或者说是让人们能够用自己习惯的方式识别计算机操作的数字),字符编码就是这个对应关系。
对于英语来说,大小写字母加数字加标点符号,总共也不会超过128个,一个字节就够用了;ASCII编码只使用了一个字节中的7位,便已经包括了英语常用字符,还加上了一组电传打字机时代的控制字符(至今仍在使用其中几个)。
然而世上并不仅有英语。欧洲一些语言需要使用的一些重音字符并没有包括在ASCII编码中;而亚洲的CJK(指China+Japan+Korea)语言字符多达几万个,更是远远超过了一个字节所能表示的范围;再加上阿拉伯语、希伯来语等等……
解决办法自然是扩充字符编码位数。双字节可以表示65536个字符,通常情况下是足够了。但这时又有一个新的问题:当计算机读到两个连续的字节,它应该将之理解为两个单独的字符还是一个字符?编码方案需要解决这个问题。
第一种方案是微软引入的 代码页的概念。ASCII只使用了一个字节的7位,字节最高位是0,那么可以用最高位是1的范围来表示扩展字符。对于多数欧洲语言,一个字节的256个字符已然足够,那么便用字节最高位是1的128个字符来表示如重音字符、制表符等扩展字符;对于亚洲语言,使用两个连续的最高位是1的字节来表示CJK字符,这样,当计算机读到一个最高位是0的字符,便知道将之解释为单字节的ASCII编码,当计算机读到一个最高位是1的字符,便知道要将这个字符与下一个字符一起来解释为一个相应的CJK字符;对于其他语言的处理方法类似(具体不甚了解,无法详述^_^|||)。
由于不同语言对最高位是1的字节解释不同,因此需要一个系统设置来进行区分,这便是代码页(Code Page)。在Windows系统中进行区域与语言设置可以设定默认代码页(还需要安装相应的字符集来支持),如简体中文是代码页936,简称cp936。除微软这套事实标准外,中国也制订有几个国家标准字符编码,如GB2312、GBK、GB18030,具体联系和区别可以Google之。一般情况下,cp936可以与GBK近似等价地看待。
这种方案的弊端有二:第一个问题是编码方案依赖于系统设置,这便导致不同系统之间可能无法兼容,一个常见的问题便是在一台电脑上保存的文本文件复制到另一台不同代码页设置的电脑上会显示乱码。第二个问题是字符处理的难度增加,比如常见的字符串计算长度、截取子串等操作,由于每个字符的实际字节数不同,便无法直接按地址偏移量计算,需要依次识别每一个字符的长度,这无疑会降低效率。
由此产生的第二种方案便是 Unicode,一个类似于巴别塔(Babel)的计划。准确地说,Unicode组织与国际标准化组织的ISO-10646工作组很有默契地共同制订编码方案,但又独立颁布各自的标准。两者的编码方案基本兼容,但在实际应用中却有两种不同的实现方案:通用编码转换格式(Unicode Translation Format, UTF)和通用字符集(Universal Character Set, UCS),前者在名称后加一个编码所用位数,如UTF-8、UTF-16、UTF-32,后者在名称后加一个编码所用字节数,如UCS-2、UCS-4。其中,UCS-2是UTF-16的子集,对应后者中的双字节编码,该字符集又被称为基本多语言平面(Basic Multilingual Plane, BMP);UCS-4和UTF-32是等价的。
目前使用最多的Unicode编码主要是UTF-8和UTF-16(UCS-2)。其中UTF-8是一种以8位为单元的变长编码方案,其单字节编码部分与ASCII完全兼容,汉字部分主要是三个字节的编码;事实上,通常语境中提到Unicode,所指的往往是UCS-2,即UTF-16中的BMP双字节编码子集。
UCS-2采用双字节编码又会存在另一个问题:由于CPU处理字节的顺序不同,相邻两个字节,比如0x4E59,在Mac机(PowerPC、68000等芯片)上会解释为U+4E59(乙),而在PC机(x86等芯片)上会解释为U+594E(奎);其中,前者被称为大端(Big-Endian),后者被称为小端(Little-Endian),这组概念来自于《格列佛游记》一书中描述的小人国战争,战争的起因是关于吃鸡蛋应该从大的一头(Big-Endian)还是从小的一头(Little-Endian)敲开。Unicode的处理措施是引入一个特殊字符U+FEFF,称为BOM(Byte Order Mark),相反的U+FFFE在Unicode中是不存在的。通过在一个文本的开头写一个BOM,比如0xFEFF4E59,这样程序就可以知道这是一个大端格式的文本。
UTF-8因为是以8位字节为单元,因而不存在字节序的问题。但有些程序也会在UTF-8格式的文本开头加上BOM(U+FEFF对应的UTF-8编码是0xEFBBBF),但这有时会给文本解析带来一些困扰。详见http://en.wikipedia.org/wiki/Byte_Order_Mark。
在SQLServer中,还有一个排序规则的概念,即对字符串进行比较和排序的规则。事实上,SQLServer安装程序中进行的排序规则设置,包含了字符集、字符串排序规则和系统区域设置。除了在安装程序过程中进行的服务器级设置,还有数据库级、列级和表达式级,这四个级别中,后面级别的默认设置依赖于前一级的设置,但在后面级别中特别指定则可以覆盖默认设置。
通常情况下,大陆的简体中文的系统会指定Chinese_PRC_CI_AS为默认排序规则,区域设置LCID为2052(0x804),字符集代码页为936。在这样设置的SQLServer服务器中,nchar/nvarchar使用UCS-2编码(这是独立于排序规则的),char/varchar使用cp936(近似GBK)编码,以上字符串均按不区分大小写(CI)、区分重音(AS)、不区分假名、不区分全半角的方式排序,其中重音和假名对中文来说不必关心。
排序规则影响所有与字符串比较相关的语句,包括各种排序(GROUP BY/PARTITION BY/ORDER BY)、索引内部存储、字符串的比较(=、>、>=、<、<=、<>、LIKE)。特别需要强调的是,LIKE字符串匹配中的范围如'[A-Z]',也依赖于指定的排序规则。
关于SQLServer排序规则的详细说明,可参看联机帮助中的“COLLATE”相关文档。
以下查询,显示中文系统中常用字符及其在常见排序规则下的表现:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
--所有简体中文的排序规则
SELECT
*
FROM
fn_helpcollations()
WHERE
name
LIKE
'Chinese[_]PRC[_]%'
--中文系统常用字符
SELECT
n, x,
u_cias , u_cias_RN = RANK() OVER(
ORDER
BY
u_cias),
u_cias_ws , u_cias_ws_RN = RANK() OVER(
ORDER
BY
u_cias_ws),
u_stroke , u_stroke_RN = RANK() OVER(
ORDER
BY
u_stroke),
u_stroke_ws , u_stroke_ws_RN = RANK() OVER(
ORDER
BY
u_stroke_ws),
u_en_cias , u_en_cias_RN = RANK() OVER(
ORDER
BY
u_en_cias),
u_en_cias_ws , u_en_cias_ws_RN = RANK() OVER(
ORDER
BY
u_en_cias_ws),
u_bin , u_bin_RN = RANK() OVER(
ORDER
BY
u_bin),
a_zh_cias , a_zh_cias_RN = RANK() OVER(
ORDER
BY
a_zh_cias),
a_zh_cias_ws , a_zh_cias_ws_RN = RANK() OVER(
ORDER
BY
a_zh_cias_ws),
a_zh_stroke , a_zh_stroke_RN = RANK() OVER(
ORDER
BY
a_zh_stroke),
a_zh_stroke_ws, a_zh_stroke_ws_RN = RANK() OVER(
ORDER
BY
a_zh_stroke_ws),
a_zh_bin , a_zh_bin_RN = RANK() OVER(
ORDER
BY
a_zh_bin)
FROM
(
SELECT
n, x =
CAST
(n
AS
binary
(2)),
u_cias =
NCHAR
(n)
COLLATE
Chinese_PRC_CI_AS,
u_cias_ws =
NCHAR
(n)
COLLATE
Chinese_PRC_CI_AS_WS,
u_stroke =
NCHAR
(n)
COLLATE
Chinese_PRC_Stroke_CI_AS,
u_stroke_ws =
NCHAR
(n)
COLLATE
Chinese_PRC_Stroke_CI_AS_WS,
u_en_cias =
NCHAR
(n)
COLLATE
Latin1_General_CI_AS,
u_en_cias_ws =
NCHAR
(n)
COLLATE
Latin1_General_CI_AS_WS,
u_bin =
NCHAR
(n)
COLLATE
Chinese_PRC_BIN,
--Unicode字符串所有BIN排序都相同,与n和x排序结果一致
a_zh_cias =
CAST
(
NCHAR
(n)
AS
char
(2))
COLLATE
Chinese_PRC_CI_AS,
a_zh_cias_ws =
CAST
(
NCHAR
(n)
AS
char
(2))
COLLATE
Chinese_PRC_CI_AS_WS,
a_zh_stroke =
CAST
(
NCHAR
(n)
AS
char
(2))
COLLATE
Chinese_PRC_Stroke_CI_AS,
a_zh_stroke_ws =
CAST
(
NCHAR
(n)
AS
char
(2))
COLLATE
Chinese_PRC_Stroke_CI_AS_WS,
a_zh_bin =
CAST
(
NCHAR
(n)
AS
char
(2))
COLLATE
Chinese_PRC_BIN
--ANSI相同CodePage的字符串所有BIN排序都相同
FROM
Nums
WHERE
n
BETWEEN
32
AND
126
--ASCII
OR
n
BETWEEN
19968
AND
40869
--中文字符
OR
n
BETWEEN
65281
AND
65374
--全角标点字母数字,对应半角为n-65248的ASCII字符
OR
n = 12288
--全角空格,对应半角空格为32
) code
ORDER
BY
n
|
二、中文字符相关的匹配
如上面查询所示, 在UCS-2中,19968至40869是中文字符:
1
2
3
4
|
SELECT
n,x=
CAST
(n
AS
binary
(2)),u=
NCHAR
(n)
FROM
Nums
WHERE
n
BETWEEN
19968
AND
40869
19968 0x4E00 一
40869 0x9FA5 龥
|
全角标点字母数字的范围是65281至65374,全角空格需要特殊处理:
1
2
3
4
5
|
SELECT
n,x=
CAST
(n
AS
binary
(2)),uq=
NCHAR
(n),ub=
NCHAR
(n-65248)
FROM
Nums
WHERE
n
BETWEEN
65281
AND
65374
SELECT
NCHAR
(12288),
NCHAR
(32)
65281 0xFF01 ! !
65374 0xFF5E ~ ~
|
因而,想要匹配一个包含中文字符的字符串可用如下语句:
LIKE N'%[一-龥]%' COLLATE Chinese_PRC_BIN
或是:
LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
这是因为在以上两种不同的排序规则下,汉字的排列顺序是不同的。
类似,想要匹配全角标点字母数字:
LIKE N'%[!-~]%' COLLATE Chinese_PRC_BIN
三、全角与半角的转换
全角(Full-width)与半角(Half-width),是对CJK字符进行打印处理时引入的概念。相对于英文中的标点、字母、数字的单宽度,通常中日韩的文字都是双宽度,当需要混排CJK字符和英文的标点字母数字时,由于字符宽度不同,可能打印效果就不美观(特别是以传统的竖排方式打印时),由此引入了全角的标点字母数字,与单宽度的英文标点字母数字一一对应,而宽度则与一般的CJK字符相同。
由此带来的问题是,计算机和互联网程序往往只识别英文的标点字母数字,如URL、Email、电话号码、以及各种编程语言中的关键字和操作符,倘若在这些地方误用了全角的字符,程序往往无法处理。(这个问题也可以看做是没有做到内容与表现分离带来的复杂度)
以数据库系统为例,好的设计应该是在前端界面处加以验证和提示,只允许有效的数据进入数据库。然而倘若由于历史代码问题,系统引入了格式不好的数据,可能会需要在数据库中进行全角与半角的转换。
根据全半角字符的排列规律,可以用T-SQL实现这样的函数,以下为两个示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
CREATE
FUNCTION
dbo.full2half(
@String nvarchar(
max
)
)
RETURNS
nvarchar(
max
)
AS
/*
全角(Fullwidth)转换为半角(Halfwidth)
*/
BEGIN
DECLARE
@chr
nchar
(1)
DECLARE
@i
int
SET
@String =
REPLACE
(@String,N
' '
,N
' '
)
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET
@chr =
SUBSTRING
(@String,@i,1)
SET
@String =
REPLACE
(@String,@chr,
NCHAR
(UNICODE(@chr)-65248))
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
END
RETURN
@String
END
GO
CREATE
FUNCTION
dbo.half2full(
@String nvarchar(
max
)
)
RETURNS
nvarchar(
max
)
AS
/*
半角(Halfwidth)转换为全角(Fullwidth)
*/
BEGIN
DECLARE
@chr
nchar
(1)
DECLARE
@i
int
SET
@String =
REPLACE
(@String,N
' '
,N
' '
)
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
WHILE @i > 0
BEGIN
SET
@chr =
SUBSTRING
(@String,@i,1)
SET
@String =
REPLACE
(@String,@chr,
NCHAR
(UNICODE(@chr)+65248))
SET
@i = PATINDEX(N
'%[!-~]%'
COLLATE
Latin1_General_BIN,@String)
END
RETURN
@String
END
GO
|