SQLServer中文处理

按：
只要接触了电脑，乱码问题总会遇到过。这是一个让人恼火的问题。如果对字符编码一知半解，乱码就仿佛一种神秘咒语，似乎一不小心就触怒了电脑爷，扔出一堆天书般的乱码来；而如果深入理解了字符编码，各种编码在你看来就会不一不异，而一切乱码问题都不过是浮云。
本文不是专门介绍字符编码的文章，只是谈一下与SQLServer中文处理相关的字符编码和排序规则，希望对各位SQLServer玩家有所帮助。



首先插句题外话：创建一个自然数表Nums。这是《SQL Server 2005技术内幕：T-SQL查询》一书的建议。
在SQL Server 2005中，可以借用ROW_NUMBER排名函数轻松生成我们所需的自然数表：

SQL code

1 2 3 4 5 6 7 8 9 10

--自然数表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./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”相关文档。


以下查询，显示中文系统中常用字符及其在常见排序规则下的表现：

SQL code

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

--所有简体中文的排序规则 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是中文字符：

SQL code

1 2 3

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，全角空格需要特殊处理：

SQL code

1 2 3 4

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实现这样的函数，以下为两个示例：

SQL code

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

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

四、UTF-8

出于国际化和平台独立性的考虑，越来越多的网站和应用程序开始采用UTF-8作为默认字符编码。通常编码转换的工作都是在前端编程语言中实现的。下面给出一下用T-SQL实现的UCS-2与UTF-8的互转函数，没有太多实际应用价值，仅仅是一个示例：（也没准会遇到需要在数据库层面对utf8字串进行编解码的情况，这两个函数就排上用场了。）

SQL code

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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

CREATE FUNCTION dbo.ucs2_to_utf8( @ucs2 varbinary(max) ) RETURNS varbinary(max) AS /* U-00000000 ... U-0000007F 0xxxxxxx  U-00000080 ... U-000007FF 110xxxxx 10xxxxxx  U-00000800 ... U-0000FFFF 1110xxxx 10xxxxxx 10xxxxxx  */ BEGIN     DECLARE         @output varbinary(max),         @i int,         @code int     SET @output = 0x     SET @i = 1     WHILE 1 = 1     BEGIN         SET @code = CAST(SUBSTRING(@ucs2,@i+1,1) + SUBSTRING(@ucs2,@i,1) AS int)         IF @code = 0             BREAK         IF @code >= 0x0800             SET @output = @output +                 CAST(@code / 4096 + 224 AS binary(1)) +                 CAST((@code % 4096) / 64 + 128 AS binary(1)) +                 CAST((@code % 4096) % 64 + 128 AS binary(1))         ELSE IF @code >= 0x0080             SET @output = @output +                 CAST(@code / 64 + 192 AS binary(1)) +                 CAST(@code % 64 + 128 AS binary(1))         ELSE             SET @output = @output + CAST(@code AS binary(1))         SET @i = @i + 2     END     RETURN @output END GO CREATE FUNCTION dbo.utf8_to_ucs2( @utf8 varbinary(max) ) RETURNS varbinary(max) AS BEGIN     DECLARE         @output varbinary(max),         @i int,         @next int,         @code int,         @tmp varbinary(1)     SET @output = 0x     SET @i = 1     SET @next = 0     WHILE 1 = 1     BEGIN         SET @tmp = SUBSTRING(@utf8,@i,1)         IF @tmp = 0x             BREAK         IF @tmp BETWEEN 0x01 AND 0x7F             SET @output = @output + @tmp + 0x00         ELSE IF @tmp BETWEEN 0xC0 AND 0xDF         BEGIN             SET @code = (CAST(@tmp AS int) & 0x1F) * 64             SET @next = 1         END         ELSE IF @tmp BETWEEN 0xE0 AND 0xEF         BEGIN             SET @code = (CAST(@tmp AS int) & 0x0F) * 4096             SET @next = 2         END         ELSE IF @tmp BETWEEN 0x80 AND 0xBF AND @next IN (1,2)         BEGIN             IF @next = 1             BEGIN                 SET @code = @code + (CAST(@tmp AS int) & 0x3F)                 SET @output = @output + CAST(NCHAR(@code) AS binary(2))             END             IF @next = 2                 SET @code = @code + (CAST(@tmp AS int) & 0x3F) * 64             SET @next = @next - 1         END         ELSE             RETURN NULL         SET @i = @i + 1     END     RETURN @output END GO

五、其它常见问题

如上所述，在指定Chinese_PRC_CI_AS为默认排序规则的情况下，char/varchar使用cp936编码，也可以存储中文。但个人建议是，char/varchar只用以存储ASCII字符，对于包含大于127的非ASCII字符的字符串，统一用nchar/nvarchar存储。这样，不但可以支持多语言，不会造成其他语言的字符遗失，而且可以避免许多计算上的问题。

例如：

SQL code

1 2 3 4 5 6 7

DECLARE @str varchar(100) SET @str = '1234567一二三四五六七' SELECT LEN(@str)               --14 SELECT LEFT(@str,10)           --1234567一二三 DECLARE @col varchar(10) SET @col = LEFT(@str,10) SELECT @col                    --1234567一

倘若在char/varchar中包含了中文字符，SQLServer的字符串函数（包括LEN、LEFT/RIGHT、SUBSTRING、STUFF、CHARINDEX/PATINDEX）会把一个中文字符（双字节）作为一个字符处理，而定义变量或列时指定的数据类型char/varchar却是以字节为单位，结果则如上例，截取了一个字串的10个字符，却无法放入一个varchar(10)的变量或列中，这种违反直觉的不一致会给系统带来一些讨厌的BUG。