環(huán)境：

• sqlserver 2014
• window 10

建議先閱讀《細(xì)說ASCII、GB2312/GBK/GB18030、Unicode、UTF-8/UTF-16/UTF-32編碼》

先說下結(jié)論：

• 如果你想在數(shù)據(jù)庫中存儲emoji表情等特殊字符，就需要將varchar改為nvarchar并且在編寫sql語句時使用大N （N'小明...'） 。
• 默認(rèn)的sqlserver中字符串的排序比較已忽略掉了全角/半角、大/小寫的差別，所以不用擔(dān)心因?yàn)榇笮懞腿虢撬阉鞑坏綌?shù)據(jù)的問題。

一、說說字符集、字符集編碼和排序規(guī)則

字符集：羅列所有圖形字符的一張大表。

比如：

• •  GBK字符集（中國制造）: 羅列了所有的中文簡體、繁體字的一張大表。
  • Unicode字符集（全 世界通用）：羅列了世界上所有圖形字符的一張大表。

字符集編碼：將字符集上羅列的圖形字符存儲到計算機(jī)中的一種編碼規(guī)則。

比如:

• GBK字符編碼（中國制造）：GBK本身既是字符集，也是編碼規(guī)則；
• UTF-16：存儲Unicode字符集的一種編碼規(guī)則，使用2個（中文）、4個（emoji表情）字節(jié)存儲。
• UTF-8：也是存儲Unicode字符集的一種編碼規(guī)則，使用1個、2個、3個、4個字節(jié)存儲。

排序規(guī)則：定義各個圖形字符之間的大小比較規(guī)則，比如：是否區(qū)分大小寫，區(qū)分全角和半角等。

在軟件使用中，一般我們只指定字符編碼即可，因?yàn)榇_定了字符編碼字符集自然就確定了。

但是在數(shù)據(jù)庫類軟件中，我們除了要指定編碼規(guī)則，還需要指定排序規(guī)則，因?yàn)?，?shù)據(jù)庫是要提供模糊匹配、排序顯示功能的。

二、sqlserver中字符集編碼和排序規(guī)則

上面雖然把字符集、字符集編碼、排序規(guī)則的概念分的很清，但sqlserver中的配置并沒有分的太清。

在sqlserver中沒有單獨(dú)設(shè)置字符集編碼的地方，僅能設(shè)置排序規(guī)則。

至于最終使用什么字符集編碼，則會受排序規(guī)則、數(shù)據(jù)類型(varchar、nvarchar）的影響。

一般我們在window或window server上安裝sqlserver 2014，安裝后默認(rèn)排序規(guī)則是： Chinese_PRC_CI_AS 。

Chinese_PRC：針對大陸簡體字UNICODE的排序規(guī)則。
CI：CaseSensitivity，指定不區(qū)分大小寫。
AS：AccentSensitivity，指定區(qū)分重音。

sqlserver設(shè)置排序規(guī)則有四個級別：

服務(wù)器（示例級別）：

數(shù)據(jù)庫：

列級別：

表達(dá)式級別：

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

注意：Chinese_PRC_CI_AS不是存儲為UTF8，事實(shí)上，直到SqlServer2019才引入UTF-8的支持（Chinese_PRC_CI_AS_UTF8）。

參照：

《Introducing UTF-8 support for SQL Server》

《排序規(guī)則和 Unicode 支持》

附：查詢排序規(guī)則元數(shù)據(jù)

-- 查詢數(shù)據(jù)庫的排序規(guī)則
select serverproperty(N'Collation');
--查詢所有受支持的排序規(guī)則
select * from fn_helpcollations()
-- 查詢列的排序規(guī)則
select name,collation_name from sys.columns where collation_name is not null

三、排序規(guī)則對sql語句的一影響

觀察排序規(guī)則對sql語句影響的時候，我主要從以下兩個方面考慮：

• 全角/半角
• 大寫/小寫

至于其他的重音、假名則是很難用到，直接用默認(rèn)的即可。

分析其他數(shù)據(jù)庫的排序規(guī)則時，也可以從這兩個方面考慮，經(jīng)過綜合對比，sqlserver中的排序規(guī)則還是很貼近實(shí)際情況的，其他的數(shù)據(jù)庫或多或少都有問題。

全角/半角對查詢的影響：

我們期望的效果：當(dāng)使用 like 查詢或 = 比較符時，數(shù)據(jù)庫能忽略掉全角 “ａ” 和 半角 "a" ，將它們判定相等。sqlserver不負(fù)眾望，默認(rèn)情況下的比較是忽略全角/半角的，所以，sqlserver能做到判定它們相等。

看如下實(shí)驗(yàn)：

create table test(
	id int identity(1,1),
	name varchar(50)
);
insert into test values
('角ａ啊'),--全角ａ
('角a啊');--半角a
--測試like中的全角半角處理
select * from test where name like '%a%';--半角a
select * from test where name like '%ａ%';--全角ａ
--測試=中的全角半角處理
select * from test where name = '角a啊';--半角a
select * from test where name = '角ａ啊';--全角ａ

上面的查詢結(jié)果均顯示：

大小寫對查詢的影響：

我們期望的效果：當(dāng)使用 like 查詢或 = 比較符時，數(shù)據(jù)庫能忽略掉大寫和小寫的區(qū)別，將它們判定相等。

sqlserver不負(fù)眾望，默認(rèn)情況下的比較是忽略大小寫的，所以，sqlserver能做到判定它們相等。

看如下實(shí)驗(yàn)：

create table test(
	id int identity(1,1),
	name varchar(50)
)
insert into test values
('A'),('a');
select * from test where name like 'A';
select * from test where name like 'a';
select * from test where name = 'A';
select * from test where name = 'a';

上面的查詢結(jié)果均顯示：

四、sqlserver究竟會以何種編碼存儲字符

上面只說了sqlserver中的默認(rèn)排序規(guī)則：Chinese_PRC_CI_AS，但是sqlserver中究竟是以哪種編碼規(guī)則存儲的呢？

具體用什么編碼規(guī)則存儲不僅受排序規(guī)則的影響，還受數(shù)據(jù)類型的影響（nvarchar、varchar）。

以 Chinese_PRC_CI_AS 排序規(guī)則為例：

當(dāng)我們使用varchar類型時，存儲到表里面的數(shù)據(jù)其實(shí)就是GBK編碼，因?yàn)椋篊hinese_PRC對應(yīng)的是區(qū)域編碼（ANSI，活動代碼頁：936）是GBK?？梢酝ㄟ^sql查詢得知：

SELECT COLLATIONPROPERTY('Chinese_PRC_CI_AS', 'CodePage')

當(dāng)我們使用nvarchar類型時，存儲到表里面的是UTF-16的編碼。

驗(yàn)證不同數(shù)據(jù)類型對應(yīng)的編碼規(guī)則：

首先，我們數(shù)據(jù)庫的排序規(guī)則是： Chinese_PRC_CI_AS ，已知 漢字“王”的各種格式編碼如下：

參考：《細(xì)說ASCII、GB2312/GBK/GB18030、Unicode、UTF-8/UTF-16/UTF-32編碼》

準(zhǔn)備數(shù)據(jù)：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('王','王')
select 
	name,nname,
	convert (varbinary (20) , name) as name_binary,
	convert (varbinary (20) , nname) as nname_binary
from test

由此，可以看出，數(shù)據(jù)表中存儲使用的字符編碼和排序規(guī)則和數(shù)據(jù)類型都有關(guān)系。

具體可以參考：《nchar 和 nvarchar (Transact-SQL)》

五、sqlserver中數(shù)據(jù)類型varchar和nvarchar的區(qū)別、N’'的作用

其實(shí)從上面的實(shí)驗(yàn)中可以看得出來，對于 Chinese_PRC_CI_AS 排序規(guī)則來說：

• varchar類型的列使用ANSI編碼，也即GBK存儲數(shù)據(jù)（不能存儲emoji表情）；
• 而nvarchar類型的列使用UTF-16編碼存儲數(shù)據(jù)（能存儲所有Unicode字符，包含emoji表情）。

我們知道，UTF-16編碼規(guī)則最少使用2個字節(jié)存儲字符，即使對于英文字母“W”也要使用兩個字節(jié)，而GBK編碼則可以使用1個字節(jié)存儲英文字母“W”，所有當(dāng)只有英文字母時，varchar顯然要節(jié)省空間。

下面是存儲英文字母“W”的示例：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('W','W')
select 
	name,nname,
	convert (varbinary (20) , name) as name_binary,
	convert (varbinary (20) , nname) as nname_binary
from test

nvarchar(8000)和varchar(8000) 中的8000指的是字節(jié)數(shù)，而不是字符數(shù)，GBK中一個字符可以是1個字節(jié)或兩個字節(jié)，UTF-16中一個字符則是2個或4個字節(jié)，所以在計算最多存儲多少文字時不要搞錯了。

N’小明’ 的作用：

這個大N表示單引號中的字符串使用的是Unicode編碼，當(dāng)我們sqlserver引擎會用Unicode的方式去解析"小明"，而不是用GBK編碼的方式。

一般來說，我們感覺不到加不加大N的區(qū)別，那是因?yàn)槲覀兇鎯Φ臄?shù)據(jù)都在Unicode的常見字符區(qū)域內(nèi)，如果我們存儲一個emoji表情，那么加不加大N的就立馬看得出來了，看如下的實(shí)驗(yàn)：

create table test(
	name varchar(50),
	nname nvarchar(50)
)
insert into test values('王','王')
insert into test values(N'王',N'王')
insert into test values('W','W')
insert into test values(N'W',N'W')
insert into test values('??','??')
insert into test values(N'??',N'??')
select 
	name,nname,
	convert (varbinary (20) , name) as name_binary,
	convert (varbinary (20) , nname) as nname_binary
from test

到底該如何選用nvarchar和varchar？用不用以N’'形式編寫sql？

如果你的數(shù)據(jù)中不需要保存中英文以外的字符（如：emoji表情字符），那么你可以忽略nvarchar和N’’，如果你的數(shù)據(jù)庫中需要保存其他特殊字符（如：emoji表情字符），那么你就必須使用nvarchar數(shù)據(jù)類型，并且以N’'形式編寫sql語句。

六、關(guān)于nvarchar(10)個varchar(10)的最多能存多少個字符

首先，要明白字符和字節(jié)不是一個概念。英文字母“a”、漢字“啊”、emoji表情“??”都稱之為一個字符，但使用不同的字符集編碼的時候他們可能占用不同的字節(jié)。

• 英文字母“a”在GBK下占1個字節(jié)、在UTF-16下占2個字節(jié)、在UTF-8下占用1個字節(jié)；
• 漢字“啊”在GBK下和UTF-16下都占2個字節(jié)、在UTF-8下占三個字節(jié)；
• emoji表情“??”在UTF-16和UTF-8下都占4個字節(jié)，在GBK下無對應(yīng)編碼；

在 sqlserver2012以上 的 Chinese_PRC_CI_AS 排序規(guī)則下，nvarchar使用UTF-16編碼，varchar使用ANSI編碼（如果電腦的區(qū)域設(shè)置為中文的話，就是GBK編碼，在中國可認(rèn)為就是GBK編碼）。

• 對于nvarchar(10)來說，這一列將最多使用 10*2 個字節(jié)來存儲數(shù)據(jù)。又因?yàn)槭褂肬TF-16來編碼數(shù)據(jù)，所以最多存儲10個英文字母或漢字，這看起來capcity就像是字符數(shù)量一樣（但實(shí)際不是）。如果你存儲的只有英文字符和漢字的話，這么認(rèn)為也沒有錯，但如果你要存儲emoji表情的話（UTF-16下占4個字節(jié)），那么capcity可就不能這么認(rèn)為了。一會看下面的實(shí)驗(yàn)；
• 對于varchar(10)來說。這一列將最多使用 10 個字節(jié)來存儲數(shù)據(jù)。又因?yàn)槭褂肎BK（在中國這么認(rèn)為）編碼，所以最多存儲 10 個英文字母或 10/2 個漢字。注意：emoji表情存不進(jìn)去哦（GBK中沒有emoji，存進(jìn)去就是亂碼）。
• 另外，應(yīng)該微軟有意限制varchar或nvarchar占用的字節(jié)數(shù)，所以規(guī)定 nvarchar(capcity) 的 capcity 最大值為4000， varchar(capcity) 的 capcity 的最大值為8000。當(dāng)然，如果你用 nvarchar(max) 或 varchar(max) 就基本上可以忽略大小限制了，因?yàn)樗鼈冏畲罂烧加?G。

關(guān)于nvarchar和varchar的容量實(shí)驗(yàn)：

-- sqlserver2014
-- 排序規(guī)則: Chinese_PRC_CI_AS
--drop table t
create table t(
	name nvarchar(10),
	name2 varchar(10)
)
-- name: 最多存儲10*2=20個字節(jié)，對于英文字母和漢字(utf16編碼下都是兩個字節(jié))來說就是10個字符
-- name2 最多存儲10個字節(jié)，用GBK編碼，英文字母一個字節(jié)，漢字兩個字節(jié),最多存儲10個英文字母和5個漢字
insert into t(name) values('1234567890') --正常
insert into t(name) values('一二三四五六七八九十') --正常
insert into t(name) values('123456789??') -- 截斷
insert into t(name2) values('一二三四五') --正常
insert into t(name2) values('1234567890') --正常
insert into t(name2) values('一二三四五1') --截斷

到此這篇關(guān)于深入理解sqlserver中的字符編碼、排序規(guī)則、nvarchar和varchar的文章就介紹到這了,更多相關(guān)sqlserver 字符編碼、排序規(guī)則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論