国产精品亚洲aV三区_ SQLServer中文處理

按：
只要接觸了電腦，亂碼問(wèn)題總會(huì )遇到過(guò)。這是一個(gè)讓人惱火的問(wèn)題。如果對字符編碼一知半解，亂碼就仿佛一種神秘咒語(yǔ)，似乎一不小心就觸怒了電腦爺，扔出一堆天書(shū)般的亂碼來(lái)；而如果深入理解了字符編碼，各種編碼在你看來(lái)就會(huì )不一不異，而一切亂碼問(wèn)題都不過(guò)是浮云。
本文不是專(zhuān)門(mén)介紹字符編碼的文章，只是談一下與SQLServer中文處理相關(guān)的字符編碼和排序規則，希望對各位SQLServer玩家有所幫助。

首先插句題外話(huà)：創(chuàng )建一個(gè)自然數表Nums。這是《SQL Server 2005技術(shù)內幕：T-SQL查詢(xún)》一書(shū)的建議。
在SQL Server 2005中，可以借用ROW_NUMBER排名函數輕松生成我們所需的自然數表：

SQL code

--自然數表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

以上語(yǔ)句生成前100萬(wàn)個(gè)自然數。

以下開(kāi)始正題。

一、字符編碼與排序規則

做過(guò)Web開(kāi)發(fā)的人對字符編碼一定不陌生。簡(jiǎn)單來(lái)說(shuō)，人所能夠識別的字符如“A”、“一”與計算機內部操作的數字01000001、1101001010111011是不一樣的，需要建立一種對應關(guān)系來(lái)讓計算機能夠“識別”人們所使用的字符（或者說(shuō)是讓人們能夠用自己習慣的方式識別計算機操作的數字），字符編碼就是這個(gè)對應關(guān)系。

對于英語(yǔ)來(lái)說(shuō)，大小寫(xiě)字母加數字加標點(diǎn)符號，總共也不會(huì )超過(guò)128個(gè)，一個(gè)字節就夠用了；ASCII編碼只使用了一個(gè)字節中的7位，便已經(jīng)包括了英語(yǔ)常用字符，還加上了一組電傳打字機時(shí)代的控制字符（至今仍在使用其中幾個(gè)）。

然而世上并不僅有英語(yǔ)。歐洲一些語(yǔ)言需要使用的一些重音字符并沒(méi)有包括在A(yíng)SCII編碼中；而亞洲的CJK（指China+Japan+Korea）語(yǔ)言字符多達幾萬(wàn)個(gè)，更是遠遠超過(guò)了一個(gè)字節所能表示的范圍；再加上阿拉伯語(yǔ)、希伯來(lái)語(yǔ)等等……

解決辦法自然是擴充字符編碼位數。雙字節可以表示65536個(gè)字符，通常情況下是足夠了。但這時(shí)又有一個(gè)新的問(wèn)題：當計算機讀到兩個(gè)連續的字節，它應該將之理解為兩個(gè)單獨的字符還是一個(gè)字符？編碼方案需要解決這個(gè)問(wèn)題。

第一種方案是微軟引入的代碼頁(yè)的概念。ASCII只使用了一個(gè)字節的7位，字節最高位是0，那么可以用最高位是1的范圍來(lái)表示擴展字符。對于多數歐洲語(yǔ)言，一個(gè)字節的256個(gè)字符已然足夠，那么便用字節最高位是1的128個(gè)字符來(lái)表示如重音字符、制表符等擴展字符；對于亞洲語(yǔ)言，使用兩個(gè)連續的最高位是1的字節來(lái)表示CJK字符，這樣，當計算機讀到一個(gè)最高位是0的字符，便知道將之解釋為單字節的ASCII編碼，當計算機讀到一個(gè)最高位是1的字符，便知道要將這個(gè)字符與下一個(gè)字符一起來(lái)解釋為一個(gè)相應的CJK字符；對于其他語(yǔ)言的處理方法類(lèi)似（具體不甚了解，無(wú)法詳述^_^|||）。

由于不同語(yǔ)言對最高位是1的字節解釋不同，因此需要一個(gè)系統設置來(lái)進(jìn)行區分，這便是代碼頁(yè)(Code Page)。在Windows系統中進(jìn)行區域與語(yǔ)言設置可以設定默認代碼頁(yè)（還需要安裝相應的字符集來(lái)支持），如簡(jiǎn)體中文是代碼頁(yè)936，簡(jiǎn)稱(chēng)cp936。除微軟這套事實(shí)標準外，中國也制訂有幾個(gè)國家標準字符編碼，如GB2312、GBK、GB18030，具體聯(lián)系和區別可以Google之。一般情況下，cp936可以與GBK近似等價(jià)地看待。

這種方案的弊端有二：第一個(gè)問(wèn)題是編碼方案依賴(lài)于系統設置，這便導致不同系統之間可能無(wú)法兼容，一個(gè)常見(jiàn)的問(wèn)題便是在一臺電腦上保存的文本文件復制到另一臺不同代碼頁(yè)設置的電腦上會(huì )顯示亂碼。第二個(gè)問(wèn)題是字符處理的難度增加，比如常見(jiàn)的字符串計算長(cháng)度、截取子串等操作，由于每個(gè)字符的實(shí)際字節數不同，便無(wú)法直接按地址偏移量計算，需要依次識別每一個(gè)字符的長(cháng)度，這無(wú)疑會(huì )降低效率。

由此產(chǎn)生的第二種方案便是Unicode，一個(gè)類(lèi)似于巴別塔(Babel)的計劃。準確地說(shuō)，Unicode組織與國際標準化組織的ISO-10646工作組很有默契地共同制訂編碼方案，但又獨立頒布各自的標準。兩者的編碼方案基本兼容，但在實(shí)際應用中卻有兩種不同的實(shí)現方案：通用編碼轉換格式(Unicode Translation Format, UTF)和通用字符集(Universal Character Set, UCS)，前者在名稱(chēng)后加一個(gè)編碼所用位數，如UTF-8、UTF-16、UTF-32，后者在名稱(chēng)后加一個(gè)編碼所用字節數，如UCS-2、UCS-4。其中，UCS-2是UTF-16的子集，對應后者中的雙字節編碼，該字符集又被稱(chēng)為基本多語(yǔ)言平面(Basic Multilingual Plane, BMP)；UCS-4和UTF-32是等價(jià)的。

目前使用最多的Unicode編碼主要是UTF-8和UTF-16(UCS-2)。其中UTF-8是一種以8位為單元的變長(cháng)編碼方案，其單字節編碼部分與ASCII完全兼容，漢字部分主要是三個(gè)字節的編碼；事實(shí)上，通常語(yǔ)境中提到Unicode，所指的往往是UCS-2，即UTF-16中的BMP雙字節編碼子集。

UCS-2采用雙字節編碼又會(huì )存在另一個(gè)問(wèn)題：由于CPU處理字節的順序不同，相鄰兩個(gè)字節，比如0x4E59，在Mac機（PowerPC、68000等芯片）上會(huì )解釋為U+4E59（乙），而在PC機（x86等芯片）上會(huì )解釋為U+594E（奎）；其中，前者被稱(chēng)為大端(Big-Endian)，后者被稱(chēng)為小端(Little-Endian)，這組概念來(lái)自于《格列佛游記》一書(shū)中描述的小人國戰爭，戰爭的起因是關(guān)于吃雞蛋應該從大的一頭(Big-Endian)還是從小的一頭(Little-Endian)敲開(kāi)。Unicode的處理措施是引入一個(gè)特殊字符U+FEFF，稱(chēng)為BOM(Byte Order Mark)，相反的U+FFFE在Unicode中是不存在的。通過(guò)在一個(gè)文本的開(kāi)頭寫(xiě)一個(gè)BOM，比如0xFEFF4E59，這樣程序就可以知道這是一個(gè)大端格式的文本。

UTF-8因為是以8位字節為單元，因而不存在字節序的問(wèn)題。但有些程序也會(huì )在UTF-8格式的文本開(kāi)頭加上BOM（U+FEFF對應的UTF-8編碼是0xEFBBBF），但這有時(shí)會(huì )給文本解析帶來(lái)一些困擾。詳見(jiàn)http://en.wikipedia.org/wiki/Byte_Order_Mark。

在SQLServer中，還有一個(gè)排序規則的概念，即對字符串進(jìn)行比較和排序的規則。事實(shí)上，SQLServer安裝程序中進(jìn)行的排序規則設置，包含了字符集、字符串排序規則和系統區域設置。除了在安裝程序過(guò)程中進(jìn)行的服務(wù)器級設置，還有數據庫級、列級和表達式級，這四個(gè)級別中，后面級別的默認設置依賴(lài)于前一級的設置，但在后面級別中特別指定則可以覆蓋默認設置。

通常情況下，大陸的簡(jiǎn)體中文的系統會(huì )指定Chinese_PRC_CI_AS為默認排序規則，區域設置LCID為2052(0x804)，字符集代碼頁(yè)為936。在這樣設置的SQLServer服務(wù)器中，nchar/nvarchar使用UCS-2編碼（這是獨立于排序規則的），char/varchar使用cp936（近似GBK）編碼，以上字符串均按不區分大小寫(xiě)(CI)、區分重音(AS)、不區分假名、不區分全半角的方式排序，其中重音和假名對中文來(lái)說(shuō)不必關(guān)心。

排序規則影響所有與字符串比較相關(guān)的語(yǔ)句，包括各種排序（GROUP BY/PARTITION BY/ORDER BY）、索引內部存儲、字符串的比較（=、>、>=、<、<=、<>、LIKE）。特別需要強調的是，LIKE字符串匹配中的范圍如'[A-Z]'，也依賴(lài)于指定的排序規則。

關(guān)于SQLServer排序規則的詳細說(shuō)明，可參看聯(lián)機幫助中的“COLLATE”相關(guān)文檔。

以下查詢(xún)，顯示中文系統中常用字符及其在常見(jiàn)排序規則下的表現：

SQL code

--所有簡(jiǎn)體中文的排序規則

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 --全角標點(diǎn)字母數字，對應半角為n-65248的ASCII字符

OR n = 12288 --全角空格，對應半角空格為32

) code

ORDER BY n

二、中文字符相關(guān)的匹配

如上面查詢(xún)所示，在UCS-2中，19968至40869是中文字符：

SQL code

SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 19968 AND 40869

19968 0x4E00 一

40869 0x9FA5 龥

全角標點(diǎn)字母數字的范圍是65281至65374，全角空格需要特殊處理：

SQL code

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 ～ ~

因而，想要匹配一個(gè)包含中文字符的字符串可用如下語(yǔ)句：
LIKE N'%[一-龥](méi)%' COLLATE Chinese_PRC_BIN
或是：
LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
這是因為在以上兩種不同的排序規則下，漢字的排列順序是不同的。

類(lèi)似，想要匹配全角標點(diǎn)字母數字：
LIKE N'%[！-～]%' COLLATE Chinese_PRC_BIN

三、全角與半角的轉換

全角(Full-width)與半角(Half-width)，是對CJK字符進(jìn)行打印處理時(shí)引入的概念。相對于英文中的標點(diǎn)、字母、數字的單寬度，通常中日韓的文字都是雙寬度，當需要混排CJK字符和英文的標點(diǎn)字母數字時(shí)，由于字符寬度不同，可能打印效果就不美觀(guān)（特別是以傳統的豎排方式打印時(shí)），由此引入了全角的標點(diǎn)字母數字，與單寬度的英文標點(diǎn)字母數字一一對應，而寬度則與一般的CJK字符相同。

由此帶來(lái)的問(wèn)題是，計算機和互聯(lián)網(wǎng)程序往往只識別英文的標點(diǎn)字母數字，如URL、Email、電話(huà)號碼、以及各種編程語(yǔ)言中的關(guān)鍵字和操作符，倘若在這些地方誤用了全角的字符，程序往往無(wú)法處理。（這個(gè)問(wèn)題也可以看做是沒(méi)有做到內容與表現分離帶來(lái)的復雜度）

以數據庫系統為例，好的設計應該是在前端界面處加以驗證和提示，只允許有效的數據進(jìn)入數據庫。然而倘若由于歷史代碼問(wèn)題，系統引入了格式不好的數據，可能會(huì )需要在數據庫中進(jìn)行全角與半角的轉換。

根據全半角字符的排列規律，可以用T-SQL實(shí)現這樣的函數，以下為兩個(gè)示例：

SQL code

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

出于國際化和平臺獨立性的考慮，越來(lái)越多的網(wǎng)站和應用程序開(kāi)始采用UTF-8作為默認字符編碼。通常編碼轉換的工作都是在前端編程語(yǔ)言中實(shí)現的。下面給出一下用T-SQL實(shí)現的UCS-2與UTF-8的互轉函數，沒(méi)有太多實(shí)際應用價(jià)值，僅僅是一個(gè)示例：（也沒(méi)準會(huì )遇到需要在數據庫層面對utf8字串進(jìn)行編解碼的情況，這兩個(gè)函數就排上用場(chǎng)了。）

SQL code

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

五、其它常見(jiàn)問(wèn)題

如上所述，在指定Chinese_PRC_CI_AS為默認排序規則的情況下，char/varchar使用cp936編碼，也可以存儲中文。但個(gè)人建議是，char/varchar只用以存儲ASCII字符，對于包含大于127的非ASCII字符的字符串，統一用nchar/nvarchar存儲。這樣，不但可以支持多語(yǔ)言，不會(huì )造成其他語(yǔ)言的字符遺失，而且可以避免許多計算上的問(wèn)題。

例如：

SQL code

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）會(huì )把一個(gè)中文字符（雙字節）作為一個(gè)字符處理，而定義變量或列時(shí)指定的數據類(lèi)型char/varchar卻是以字節為單位，結果則如上例，截取了一個(gè)字串的10個(gè)字符，卻無(wú)法放入一個(gè)varchar(10)的變量或列中，這種違反直覺(jué)的不一致會(huì )給系統帶來(lái)一些討厭的BUG。

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久