【茶包射手日記】資料表部署至新資料庫中文變問號
3 |
分享這幾天犯下的 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.
Comments
# by PanJ
So, 這個教訓學到的是, varchar對 拉丁語系的文字 是 節省空簡的, 但我們是活在必需用2bytes來保存文字的環境中, 強迫自己固定使用nvarchar還是必要的
# by Huang
當初DB Collation弄錯,更換比較實在。和varchar, nvarchar比較沒有關係
# by Jeffrey
to Huang, 我們慣用原則是純粹英數字的欄位才用VARCHAR,有中文字元一律要用NVARCHAR,靠Collation讓VARCHAR存中文不可靠(無法處理中文難字或其他語系字元)。但Collation會影響排序跟比對,也是該解決的問題,謝謝提醒。