【茶包射手日記】資料表部署至新資料庫中文變問號

分享這幾天犯下的 SQL 低級錯誤。

有個新功能上線，資料庫需要配合新增資料表。該功能已在測試台測試多時，將當初建立 TABLE 的 SQL 腳本送交部署人員在正式資料庫建立資料表，不料程式執行出錯：

System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'ConrolFlag\_0\_Key'. 
Cannot insert duplicate key in object 'dbo.ControlFlag'. 
The duplicate key value is (1#????, 12345, A).
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)

SQL 貼心地在錯誤訊中列出重複的 Primary Key 值，我注意到第一個參數(假設欄位名稱為 DataKey)為"1#????"，但依預期邏輯應該要是"1#通訊資料"，意味著中文字元都成了問號。起初懷疑是網頁 URL 參數中文編碼有誤，但網頁有正確顯示"1#通訊資料"字樣，代表 C# 解析正確，所以是 Dapper 傳數處理中文字串出錯？網站有很多地使用 Dapper INSERT 中文字串參數均運作正常，只有此處變亂碼說不過去。

回頭檢查資料表定義發現異常點 - DataKey 橺位資料型別被設成 VarChar(32)，它需要寫入中文字元理應使用 NVarChar(32)，當初不察設成 VarChar，應是中文變問題的原因。推敲問題發生原因 - 新增資料時程式會先用 WHERE DataKey = @currDataKey 查詢相同內容是否已存在，不存在才 INSERT。同一 DataKey 第一次更新時資料表是空的，故能 INSERT 成功(但 DataKey 中文變問號)；之後查詢 WHERE DataKey = N'1#通訊資料' (因為剛才寫入的 DataKey 成了'1#????'，比對不符)判定相同內容不存在，故第二次更新仍會 INSERT，DataKey 再次變成 '1#????' 與前次寫入內容相同，Primary Key 重複錯誤得到合理解釋。

接下來的問題是，為什麼此一資料型別錯誤在測試台沒被發現？懷疑跟資料庫定序(Collation)有關，檢查測試台 DB 的 Collation 為 Chinese_Taiwan_Stroke_CI_AS：

再查了正式台資料庫，Collation 則是 SQL_Latin1_General_CP1_CI_AS，真相大白!

測試台寫入中文到 VarChar 欄位用的是 BIG5 編碼，故中文能被正確解析(但若遇到 Unicode 難字也會變問號)，但正式台寫中文到 VarChar 時則被轉成英文拉丁語系字元，於是中文變問號。

再學到一次經驗。將資料表的 DataKey 欄位由 VarChar(32) 改成 NVarChar(32)，結案!

A case of inserting Chinese characters into VarChar column on different collation SQL databases.