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

Posted on 2020-08-282020-08-31 by admin

背景

公司有一個數據處理線，上面的數據經過不同環境處理，然後上線到正式庫。其中一個環節需要將數據進行處理然後導入到另外一個庫(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。

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※超省錢租車方案

※別再煩惱如何寫文案,掌握八大原則!

※回頭車貨運收費標準

※教你寫出一流的銷售文案?

背景

排查過程

總結

標籤

近期文章