資料庫char varchar nchar nvarchar，編碼Unicode，UTF8，GBK等，Sql語句中文前為什麼加N（一次線上資料儲存亂碼排查）

Spring2Sun發表於2020-06-24
資料庫UnicodeSQL

背景

公司有一個資料處理線，上面的資料經過不同環境處理，然後上線到正式庫。其中一個環節需要將資料進行處理然後匯入到另外一個庫(Sql Server)。這個處理的程式是老大用python寫的，處理完後進入另外一個庫後某些欄位出現了亂碼。
比如這個字串：1006⁃267X(2020)02⁃0548⁃10
另外一個庫變成：1006?267X(2020)02?0548?10
線上人員反饋回來後老大由於比較忙，一直沒有排查，然後我問了下估計是什麼原因。老大說他python裡面轉了utf8,可能是編碼問題。我當時問了下就沒下文了，因為我不會python，所以這個事情就擱置了。

排查過程

然後這個問題拖了很久，線上也不斷反饋。同時自己也負責這塊，空閒時間就主動去排查了下原因。當然這個排查過程還是比較曲折的，所以就把這個過程分享下，同時回顧下涉及到的知識點。

先說結果：最後經過排查是由於python處理後insert語句插入到Sql Server資料庫儲存欄位前沒有加N。

1.SQL Server資料型別

首先由於資料寫進去出現亂碼，所以第一步就是檢查寫入庫的欄位是否設定了正確的資料型別。因為有時候對char與varchar的區別或者varchar與nvarchar的區別不是很在意，所以有可能設定了錯誤的資料型別。至於這幾個字元的資料型別區別是什麼，這裡摘抄官方解釋。

字元資料型別 char（大小固定）或 varchar（大小可變） 。 從 SQL Server 2019 (15.x) 起，使用啟用了 UTF-8 的排序規則時，這些資料型別會儲存 Unicode 字元資料的整個範圍，並使用 UTF-8 字元編碼。 若指定了非 UTF-8 排序規則，則這些資料型別僅會儲存該排序規則的相應內碼表支援的字元子集。
引數

char [ ( n ) ]
固定大小字串資料 。 n 用於定義字串大小（以位元組為單位），並且它必須為 1 到 8,000 之間的值 。 對於單位元組編碼字符集（如拉丁文），儲存大小為 n 個位元組，並且可儲存的字元數也為 n。 對於多位元組編碼字符集，儲存大小仍為 n 個位元組，但可儲存的字元數可能小於 n。 char 的 ISO 同義詞是 character 。
varchar [ ( n | max ) ]
可變大小字串資料 。 使用 n 定義字串大小（以位元組為單位），可以是介於 1 和 8,000 之間的值；或使用 max 指明列約束大小上限為最大儲存 2^31-1 個位元組 (2GB)。 對於單位元組編碼字符集（如拉丁文），儲存大小為 n + 2 個位元組，並且可儲存的字元數也為 n。 對於多位元組編碼字符集，儲存大小仍為 n + 2 個位元組，但可儲存的字元數可能小於 n 。

字元資料型別 nchar（大小固定）或 nvarchar（大小可變） 。 從 SQL Server 2012 (11.x) 起，使用啟用了補充字元 (SC) 的排序規則時，這些資料型別會儲存 Unicode 字元資料的整個範圍，並使用 UTF-16 字元編碼。 若指定了非 SC 排序規則，則這些資料型別僅會儲存 UCS-2 字元編碼支援的字元資料子集。

nchar [ ( n ) ]
固定大小字串資料。 n 用於定義字串大小（以雙位元組為單位），並且它必須為 1 到 4,000 之間的值 。 儲存大小為 n 位元組的兩倍。 對於 UCS-2 編碼，儲存大小為 n 個位元組的兩倍，並且可儲存的字元數也為 n。 對於 UTF-16 編碼，儲存大小仍為 n 個位元組的兩倍，但可儲存的字元數可能小於 n，因為補充字元使用兩個雙位元組（也稱為代理項對）。 nchar 的 ISO 同義詞是 national char 和 national character 。
nvarchar [ ( n | max ) ]
可變大小字串資料。 n 用於定義字串大小（以雙位元組為單位），並且它可能為 1 到 4,000 之間的值 。 max 指示最大儲存大小是 2^30-1 個字元 (2 GB) 。 儲存大小為 n 位元組的兩倍 + 2 個位元組。 對於 UCS-2 編碼，儲存大小為 n 個位元組的兩倍 + 2 個位元組，並且可儲存的字元數也為 n。 對於 UTF-16 編碼，儲存大小仍為 n 個位元組的兩倍 + 2 個位元組，但可儲存的字元數可能小於 n，因為補充字元使用兩個雙位元組（也稱為代理項對）。 nvarchar 的 ISO 同義詞是 national char varying 和 national character varying 。

通過上面的描述我們可以總結：這幾種型別都是儲存字元資料，如果儲存單位元組的字串（比如英文）使用char、varchar,節約空間。如果儲存多位元組的字串(比如包含中文)使用nchar、nvarchar，相容更多的編碼。雙位元組比單位元組對應的多了一個n。
單位元組雙位元組中還有一個區別var，表示可變大小字串資料。可變是指如果某欄位插入的值超過了資料頁的長度，該行的欄位值將存放到ROW_OVERFLOW_DATA中。但是會造成多餘的I/O，比如一個VARCHAR列經常被修改，而且每次被修改的資料的長度不同，這會引起‘行遷移’(Row Migration)現象。這裡就不展開了，可以去了解下。
所以我們設計資料庫欄位的時候需要根據業務設計合理的資料型別，有利於節約空間和時間。而經過我檢查資料庫欄位確實設定的nvarchar,所以不存在儲存不了對應編碼問題。而且問了老大他說python裡面他轉了UTF8編碼，所以下一步就是排查是否轉編碼出了問題。

2.編碼
因為我經常寫C#,C#裡面的字串是Unicode的，當然對於程式設計師來說這個編碼是透明的，因為是Unicode編碼可以轉換成其它任何編碼，所以我們日常開發的時候並不需要時刻去關注編碼的問題，其底層已經幫我們進行了處理。既然說是python轉了utf8那麼我就去大概看了下python的基礎並試驗了一把。
先找了一條出現亂碼的資料，在原庫取出來然後進行utf8轉碼，然後再解碼。講道理同一個編碼解碼出來儲存應該還是原來的字串，所以我才會好奇去試驗。試驗後發現果然沒有什麼問題。

關於編碼可以看下這個講解：編碼，因為講的比較形象而且容易理解，所以我這裡就不累述了。
排除python程式編碼問題，那接下來就是要排查從程式插入到資料庫這一段的問題了。

3.SQL Server排序規則
首先插入這一階段我想到的還是編碼問題，所以去查詢了資料庫編碼。使用sql語句查詢資料庫排序規則

SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage')

對應的字符集編碼
936 ：簡體中文GBK
950 ：繁體中文BIG5
437 ：美國/加拿大英語
932 ：日文
949 ：韓文
866 ：俄文
65001 ：unicode UTF-8
查詢了資料排序規則，匯入資料庫是預設排序規則，也就是936 GBK編碼。為什麼要看資料庫排序規則，第1點中可見“資料型別僅會儲存該排序規則的相應內碼表支援的字元子集”。
排序規則微軟解釋：排序規則

SQL Server 中的排序規則可為您的資料提供排序規則、區分大小寫屬性和區分重音屬性。 與諸如 char 和 varchar 等字元資料型別一起使用的排序規則規定可表示該資料型別的內碼表和對應字元 。
無論你是要安裝 SQL Server 的新例項、還原資料庫備份，還是將伺服器連線到客戶端資料庫，都必須瞭解正在處理的資料的區域設定要求、排序順序以及是否區分大小寫和重音。

所以通過檢視排序規則知道，預設編碼是GBK。然後我就猜測到是GBK編碼問題，因為在python3裡面字串的預設編碼也是Unicode，測試下把1006⁃267X(2020)02⁃0548⁃10轉成GBK。

可以看到是無法轉碼的，gbk識別不了那個短橫槓，然後我編碼成GB18030能夠編碼。說明短橫槓是更高位的編碼，當然unicode是能儲存的。那為什麼在資料庫裡面就成了亂碼呢？而且欄位型別是設定的nvarchar啊。

4、大寫字母“N”作為字首
通過3點的分析，說明了本該儲存成Unicode的編碼被儲存成了預設編碼。所以我們只要把儲存成Unicode編碼就行了，所以到此已經和python程式沒什麼關係了，帶著懷疑的態度，我將這段字元直接拿到Sql Sever裡面執行，果然也是亂碼。

最後就是在引數前加N執行

這下結果就正常了。細心的你是否發發現v1欄位還是亂碼，因為我為了測試varchar單位元組，即使我加了N一樣的是亂碼。所以記得儲存中文最好選nvarchar,原因麼請看第一點char和varchar的說明中這樣一句話：若指定了非 UTF-8 排序規則，則這些資料型別僅會儲存該排序規則的相應內碼表支援的字元子集。也就是它只會儲存我當前資料庫的GBK編碼。
最後我還在python裡面插入的sql語句加了N,同樣可以插入成功。

關於加N的解釋，微軟t-sql文件關於insert說明：連結

5.為什麼我們平時很少加N
既然有這樣的問題為什麼我們平時基本沒加過N?原因有幾點：

  • 沒有遇到高位的編碼（直接拼接sql）。
  • 用SqlParameter 引數執行sql會自動加N。
  • 平時使用ORM框架已經幫我規避了這個問題。
    所以我們平時如果是直接使用sql時最好使用引數形式，既能幫我們解決sql注入攻擊,還能幫我們規避不加N導致的編碼問題。

SqlParameter會自動加N？帶著懷疑的態度測試下。
首先寫一個測試程式，然後開啟SQL server跟蹤來檢視執行的sql。

       static void Test()
        {
            string server = "127.0.0.1";
            string database = "TestDB";
            string user = "sa";
            string password = "******";
            string connectionString = $"server={server};database={database};User ID={user};Password={password}";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.CommandText = "insert into Test1 values('1006⁃267X(2020)02⁃0548⁃10','1006⁃267X(2020)02⁃0548⁃10')";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "insert into Test1 values(@v1,@v2)";
                    cmd.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "v1",
                        Value = "1006⁃267X(2020)02⁃0548⁃10"
                    });
                    cmd.Parameters.Add(new SqlParameter
                    {
                        ParameterName = "v2",
                        Value = "1006⁃267X(2020)02⁃0548⁃10"
                    });
                    cmd.ExecuteNonQuery();
                }
            }
        }

檢視跟蹤執行的sql，一個是直接傳入拼接sql執行，一個是使用引數方式執行。

總結

通過一次排查亂碼問題，又回顧或者學習了關於資料型別和編碼，以及sql儲存如何避免亂碼問題。平時設計的時候如果是帶中文的欄位首先考慮帶n的char型別。同時在直接使用sql進行insert、update的時候注意在要儲存為Unicode編碼字串前面加N。

相關文章