正確設定與使用SQL Server的字符集(Collation,即排序規則)

目錄

正確認識SQL Server的字符集

SQL Server作為一個國際化產品,支援多語言環境。在SQL Server中,字符集被稱為排序規則(即Collation)。排序規則不僅影響記錄行的sort順序,還影響中文顯示是否亂碼等。在SQL Server中,排序規則可在3處地方設定:

伺服器級別 =>instances
db庫級別 =>databases
表列級別 =>columns

Created with Raphaël 2.1.0instancesdatabasescolumns

上圖所示為SQL Server預設情況下對Collation繼承的方式。

即在SQL Server軟體安裝時我們可設定伺服器級別的排序規則,也就是instances的排序規則。如下所示是使用T-SQL查詢當前instances的排序規則資訊:

--查詢當前SQL Server伺服器的排序規則
SELECT SERVERPROPERTY(N'Collation')
--------------------------------------------------
Chinese_PRC_CI_AS

當然你也可以使用SSMS檢視伺服器屬性:
伺服器排序規則

若在建立databases時我們未指定排序規則,databases則會使用instances的排序規則。如下所示是通過sql程式碼查詢或修改SQL Server中databases的排序規則資訊:

--在建立database時指定排序規則:SQL_Latin1_General_CP1_CI_AS
USE master;
GO
CREATE DATABASE mydb
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
--通過目錄檢視sys.databases查詢databases的排序規則
SELECT
name,
collation_name
FROM sys.databases
WHERE name = N'mydb';
-------------------------------------------------
mydb    SQL_Latin1_General_CP1_CI_AS
--修改現有databases的排序規則
ALTER DATABASE mydb
COLLATE Chinese_PRC_CI_AS

而表中的列(columns)預設情況是繼承databases的排序規則(除非在建立表時對列的排序規則進行指定),我們可通過目錄檢視sys.columns查詢表中columns的排序規則資訊。

這裡需注意:SQL Server的排序規則隻影響字元型的列,如char, varchar, text, nchar, nvarchar, ntext,因此目錄檢視sys.columns中非字元型的排序規則顯示為NULL

--注意:只有字元型的列才會顯示排序規則資訊,非字元型的顯示為NULL
SELECT name, collation_name
FROM sys.columns
where collation_name is NOT NULL

選擇合適的SQL Server字符集

在安裝SQL Server時,你可能困惑應該選擇哪種字符集,SQL Server或者Windows的。官方推薦使用SQL Server的字符集,而非Windows的字符集。原因是,SQL Server字符集是基於Windows衍生出來的,同時保證SQL Server版本間的相容性, 如SQL Server 2014可使用的字符集比2008多

--檢視當前SQL Server支援的排序規則
SELECT * from ::fn_helpcollations()

注意字符集的名字縮寫與對應的的描述,如CI表示不區分大小寫、
支援的排序規則

錯誤使用SQL Server的字符集

我們不難理解:只需保持SQL Server中3處字符集設定的地方:instances、databases、columns設定一致即是正確的使用方式。

那麼當SQL Server中instances與databases對排序規則設定不一致時,將直接導致臨時表#或##不能正常使用(臨時表的列預設繼承tempdb的排序規則,而tempdb則繼承了instances的排序規則)。

/*
注意:
這裡mydb的字符集是SQL_Latin1_General_CP1_CI_AS,
而instance的字符集是Chinese_PRC_CI_AS
*/
USE mydb;
GO
--在mydb中建立一張表collation_test
CREATE TABLE collation_test (hyper varchar(10));
GO
--建立臨時表collation_temp
CREATE TABLE #collation_temp (hyper varchar(10));
GO

連線查詢上述臨時表的內容時,將出現如下報錯資訊:

--查詢報錯
SELECT  *
FROM collation_test l
LEFT JOIN #collation_temp c
ON l.hyper = c.hyper;
--------------------------------------------
Msg 468, Level 16, State 9, Line 4
無法解決 equal to 運算中 "Chinese_PRC_CI_AS" 和 "SQL_Latin1_General_CP1_CI_AS" 之間的排序規則衝突。

其根本原因是由於表collation_test與臨時表#collation_temp中列的排序規則不一致。雖然可以通過以下2種方式繼續使用臨時表,但並不推薦。如下通過指定select表中列的排序規則,繼續使用上述兩張表。

--方式1:
--注意指定表collation_test使用排序規則COLLATE Chinese_PRC_CI_AS
SELECT  *
FROM collation_test l 
LEFT JOIN #collation_temp c 
ON l.hyper COLLATE Chinese_PRC_CI_AS = c.hyper
------------------------------------------------------- 

第二種解決方法則是在建立表時指定列的排序規則

--方式2
USE mydb;
GO
--注意指定了列的排序規則:COLLATE Chinese_PRC_CI_AS
CREATE TABLE collation_Wang
(hyper varchar(10) COLLATE Chinese_PRC_CI_AS);
--保持列的排序規則一致即可正常使用臨時表#collation_temp
SELECT  *
FROM collation_wang w
LEFT JOIN #collation_temp c 
ON w.hyper = c.hyper
-------------------------------------------------------

同時instances的排序規則設定會影響SQL Server資料的匯入匯出功能。

通常我們遇到的另一個問題是:通過SSMS(即SQL Server Management Studio)插入(insert)的中文,在查詢時顯示亂碼(即問號?)。

--在上述表collation_test插入中文
INSERT INTO collation_test VALUES ('東') 
--查詢表collation_test的記錄
select * from collation_test

查詢顯示亂碼:
中文亂碼

這當然是由於表collation_test上hyper列的字符集設定不正確所導致的。但若你有幸在表上使用了nvarchar等型別,那麼當出現上述亂碼時,也許你還可以使用如下臨時方式補救:

/* 
注意:
資料庫mydb依舊是使用錯誤的排序規則:SQL_Latin1_General_CP1_CI_AS,
但是表collation_nvarchar使用了nvarchar型別,而非varchar
*/
USE mydb;
GO
CREATE TABLE collation_nvarchar (hyper nvarchar(10));
GO
--臨時處理方式
INSERT INTO collation_nvarchar VALUES (N'東'); 
--錯誤插入方式
INSERT INTO collation_nvarchar VALUES ('東'); 
--查詢表collation_nvarchar的記錄
select * from  collation_nvarchar

nvarchar

綜上述,我們應儘可能的正確設定SQL Server排序規則:
1. 正確的設定SQL Server排序規則 ,保持instances、databases、columns中3處排序規則一致,推薦使用Chinese_PRC_CI_AS
2. 儘可能使用nvarchar等Unicode型別,而非varchar型別

參考資料

  1. Setting and Changing the Database Collation
    https://msdn.microsoft.com/en-us/library/ms175835(v=sql.105).aspx

  2. sys.columns (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/ms176106(v=sql.120).aspx

  3. Collation and International Terminology
    https://msdn.microsoft.com/en-us/library/ms143726(v=sql.105).aspx