SQL Server中Collation的作用是什么

SQL Server中Collation的作用是什么，針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

　　Collation的組成

　　在講完字符的存儲形式以后，我們就可以講什么是collation了。Collation描述了數(shù)據(jù)在數(shù)據(jù)庫中是按照什么規(guī)則來描述字符，以及字符時如何被排序和比較的。在SQL Server中，Collation由兩部分組成，比如中國的一個collation是 Chinese_PRC_CI_AI_WS ，前半部份是指的是所支持的字符集，與code page相對應(yīng)，如Chinese_PRC 對應(yīng)的代碼頁是936，在這個code page中定義了所有能夠使用的字符。后半部CI_AI_WS用于表示排序規(guī)則，比如：

　　_CI(CS)表示是否區(qū)分字母大小寫，CI不區(qū)分，CS區(qū)分。如果區(qū)分大小寫，那么排序的時候小寫字母的排在大寫的前面;如果不區(qū)分大小寫，那么排序的時候視大小寫字母相同。

　　_AI(AS) 表示是否區(qū)分重音，AI不區(qū)分，AS區(qū)分。如果不區(qū)分重音，那么排序的時候視“a”和“?”為相同字符

　　_KI(KS) 表示是否區(qū)分假名類型，KI不區(qū)分，KS區(qū)分。在日語中應(yīng)用。

　　_WI(WS) 表示是否區(qū)分全半角，WI不區(qū)分，WS區(qū)分。半角是單字節(jié)，全角是雙字節(jié)。

　　Collation的四個級別

　　Collation一共有四個級別，分別是server-level, database-level, column-level和expression-level。

　　Server-level Collations

　　服務(wù)器級別的collation是在安裝數(shù)據(jù)庫實例的時候指定的，如果沒有特別指定，那么就將windows collation作為server-level collation。Windows collation由操作系統(tǒng)中的區(qū)域語言來決定的。

　　因為我們選擇的是Chinese(Simplified,PRC)，那么我們默認的server-level collations就是：Chinese_PRC_。Server-level collation也是系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫的默認collation。一般情況下server-level collation一旦設(shè)定就不能更改，除非將所有數(shù)據(jù)庫中的對象以及數(shù)據(jù)全部導(dǎo)出，并創(chuàng)建master，再將數(shù)據(jù)導(dǎo)回才可完成。

　　Database-level collations

　　Database-level collations可以在create database…collate的時候指定，如果要修改database-level collations，可以通過alter database …collate來修改。一般情況是不能修改系統(tǒng)數(shù)據(jù)庫(master等)的collations的，除非使用前面提到的修改server-level collations的方法來修改系統(tǒng)數(shù)據(jù)庫。

　　Column-level collations

　　在創(chuàng)建或更改表時，可使用 COLLATE 子句指定每個字符串列的排序規(guī)則。當(dāng)然也可以修改column-level collations。

　　Expression-level collations

　　Expression-level是指在執(zhí)行sql語句的時候指定collations，比如：

　　SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

　　這一條查詢語句表示按照Latin1_General_CS_AI的排序規(guī)則來進行排序。Expression-level collations的一個好處就是非常靈活。

　　實驗1：解決collation不匹配導(dǎo)致的沖突

　　在對兩個collations級別不同的數(shù)據(jù)庫的表進行連接操作的時候，會報錯。這是可以通過expression-level collations來指定使用何種collations來解決問題。比如使用Collate Database_Default 則會將字段定義或轉(zhuǎn)換成當(dāng)前數(shù)據(jù)庫的默認排序規(guī)則，從而解決沖突。

　　Step1：

　　創(chuàng)建兩張表，第一張表使用默認的collation，第二張表在stuname列上指定collation。

　　create table student1

　　(

　　stuid int not null,

　　stuname nvarchar(20) not null,

　　);

　　create table student2

　　(

　　stuid int not null,

　　stuname nvarchar(20) COLLATE Latin1_General_CS_AI not null,

　　);

　　--求表連接Step2：

　　select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname

　　執(zhí)行上述查詢報錯如下所示：

　　Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "Chinese_PRC_CI_AS" in the equal to operation.

　　然后在expression-level使用Collate Database_Default

　　select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname Collate Database_Default

　　上述查詢執(zhí)行成功。

　　需要注意的是collation只能用在字符串類型的列上面，如果在int列上使用collate會報錯。

　　實驗2：變更collation對數(shù)據(jù)庫的影響。

　　目的1：

　　創(chuàng)建數(shù)據(jù)庫，查看數(shù)據(jù)的默認database collation與server collation是否一樣。

　　目的2：

　　在database collation為Chinese_PRC_CI_AS的數(shù)據(jù)庫中插入中文，然后修改collation為Latin1_General_CS_AI，看看已保存的數(shù)據(jù)有沒有發(fā)生變化。如果再次把collation改回到Chinese_PRC_CI_AS，又有什么變化。

　　目的3：

　　在collation為Latin1_General_CS_AI的情況下，插入中文，會有什么情況，如何解決。

　　--實驗1：測試nvarchar和varchar的存儲長度

　　--創(chuàng)建一個默認collation為Chinese_PRC_CI_AS的數(shù)據(jù)庫TESTDB3

　　USE TESTDB1

　　CREATE TABLE test

　　(

　　lastname NVARCHAR(8) NOT NULL,--nvarchar類型，雙字節(jié)存儲

　　title VARCHAR(8) NOT NULL, --varchar類型，單字節(jié)存儲

　　);

　　insert into test values('姓名1','標(biāo)題1');

　　select * from test;

　　insert into test values('123456789','1');--String or binary data would be truncated.

　　insert into test values('12345678','1');

　　insert into test values('1','12345678');

　　insert into test values('一二三四五六七八','一二三四');

　　select * from test;

　　--總結(jié)：

　　/*

　　1.nvarchar(n)，按字符來存儲，不論是英文字符還是中文字符。最多能夠存儲n個中文或者是英文，但是所占用的存儲空間是2n+2個字節(jié)。

　　2.varchar(n)按字節(jié)存儲，最多能夠存儲n個英文字母，存儲n/2個中文字符。但是所占用的存儲空間是n個字節(jié)。

　　*/

　　--實驗2：collation的變更對數(shù)據(jù)的影響。

　　USE TESTDB1

　　select * from test;

　　--step1：修改數(shù)據(jù)庫的collation從默認的Chinese_PRC_CI_AS修改為Latin1_General_CS_AI，英語國家都是使用這個排序規(guī)則。

　　use master

　　alter database TESTDB1 collate Latin1_General_CS_AI

　　--step2：通過下面的語句可以查出實例中與默認collation不同的數(shù)據(jù)庫，查找到了我們之前的 TESTDB1。

　　use master

　　SELECT

　　NAME AS DATABASE_NAME

　　, DATABASEPROPERTYEX(NAME,'COLLATION') AS DBCOLLATION

　　, SERVERPROPERTY('COLLATION') AS SERVERCOLLATION

　　FROM SYS.DATABASES

　　WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')

　　--step3：在修改完collation以后查看表中的數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)沒有改變。

　　USE TESTDB1

　　select * from test;

　　--step4：在新的collation下面插入數(shù)據(jù)。

　　insert into test values('姓名1','標(biāo)題1');--插入以后發(fā)現(xiàn)中文都變成了亂碼"??"

　　select * from test;

　　--step5：因為lastname是nvarchar類型，我們在插入的時候指定出nvarchar。

　　insert into test values(N'姓名2','標(biāo)題2');--此時發(fā)現(xiàn)姓名沒有亂碼，標(biāo)題不用說還是亂碼??

　　select * from test;

　　--step6：試一試在varchar類型的字段插入nvarchar是數(shù)據(jù)。

　　insert into test values(N'姓名3',N'標(biāo)題3');--發(fā)現(xiàn)標(biāo)題也不是亂碼了。

　　select * from test;

　　------------------------------------

　　--step7：將collation從Latin1_General_CS_AI改回默認的Chinese_PRC_CI_AS。

　　use master

　　alter database TESTDB1 collate Chinese_PRC_CI_AS

　　--step8：在修改完collation以后查看表中的數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)沒有改變。

　　USE TESTDB1

　　select * from test;

　　--step9:插入字符，不指定unicode類型，查詢顯示的是中文，表明當(dāng)前collation默認使用unicode編碼。

　　insert into test values('姓名4','標(biāo)題4');

　　select * from test;

　　總結(jié)：

　　collation的變更不改變數(shù)據(jù)庫原先存儲的數(shù)據(jù)，原來是怎么樣，修改以后還是怎樣，沒有發(fā)生改變。

　　Latin1_General_CS_AI默認是的non-unicode的，所以在這個collation下插入中文變成亂碼，必須在插入數(shù)據(jù)的時候指明使用unicode形式插入，也就是添加關(guān)鍵字“N”，而Chinese_PRC_CI_AS這個collation使用的是double-byte code page，這里面定義了所有中文字符，所以在插入數(shù)據(jù)的時候不需要指定關(guān)鍵字“N”。

　　可以往varchar數(shù)據(jù)類型的列中插入nvarchar的數(shù)據(jù)，也就是使用varcha存儲unicode的數(shù)據(jù)。

關(guān)于SQL Server中Collation的作用是什么問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。