同事分享在 Oracle 踩到 NVarChar2 中文字數上限的地雷,一句話點醒我夢中人,嚇得我屁滾尿流失了魂,原來我也搞錯多年。

不囉嗦,直接看圖。

我們都知道,NVarChar2 的長度上限是 4000,而 NVarChar2 支援 Unicode,不管是中文或英數字,一個字元都算1,所以 NVarChar2(4000) 可以儲存 4000 個中文字? 錯了! 是 1,333 個,如果你試圖塞入 1,334 個中文字元,將會得到「ORA-01401 插入值過大」錯誤。

用 LEGNTH()、LENGTHB() 觀察就很清楚了。當資料庫使用 UTF8 編碼,一個中文字元算 3 個 Byte,1333 個中文耗用 3999 Byte,1334 就會破表。

官方文件有提到這點:(參見Table 6-5 Character Set Advantages and Disadvantages for a Unicode Datatype Solution)

The maximum lengths for the NCHARand NVARCHAR2 columns are 2000 and 4000 characters respectively, which is more than those for NCHAR(1000) and NVARCHAR2 (2000) in AL16UTF16. Although the maximum lengths of the NCHAR and NVARCHAR2columns are larger in UTF8, the actual storage size is still bound by the byte limits of 2000 and 4000 bytes, respectively. For example, you can store 4000 UTF8 characters in an NVARCHAR2 column if all the characters are single byte, but only 4000/3 characters if all the characters are three bytes.

總之,這是 Oracle 的設計始然,只是我誤解了十多年,用 SQL Server NVarChar 最大長度行為想當然爾。

改用 SQL Server 驗證:

NVarChar(4000) 可以放 4000 中文字沒問題,SQL Server 內部使用 UCS-2 編碼,實際儲存 Byte 數為 8000。

總論:Oracle NVarChar2 跟 SQL Server NVarChar 的最大長度都是 4000,只是 Oracle 要抓字串轉為 byte[] 後的長度,依編碼而異,放中文或英數字的字數上限不同,若為 UTF8 純中文可放 1333 個字元;SQL Server 則是抓字串字數,可放足 4000 個中文字元。


Comments

# by 陳喬依

印象中… 型態有nvarchar2(byte) 跟 nvarchar2(char) 好像char 就可以放到4000?

# by Jeffrey

to 陳喬依,我沒找到 nvarchar2(char) 的相關資料,能再提供一些線索嗎?

# by 陳喬依

抱歉,我記錯了… 是varchar2才有兩種型態 (未經查證就回覆了,不好意思)

# by Redd

Hi Jeffrey, 所以Oracle的NVarChar2若為 UTF8 純中文可放 1333 個字元, 英數字的話還是4000字元,我這樣理解沒錯吧?

# by Jeffrey

to Redd, 是的,你可以用Encoding.UTF8.GetBytes("..").Length 來判斷。

# by Oldsand

剛看了你的文章, 一開始我也以為NVARCHAR2(2000)只接受1333個字元, 經過半日的測試後確定, 應該是可以真的可以放2000個字元 然而你的測試其實有誤哦

# by Jeffrey

to Oldstand, 除了實驗驗證過,UTF8 中文字元數上限1333的說法也有官方文件支持,對你的測試結果很感興趣,能提供多一點測試方式,資料庫版本等資訊讓大家參考嗎?

# by redshoe

11.2.0.1.0 MEMO , NVARCHAR2(10) 測試手動測試手動測試手動 SQL Error [12899] [72000]: ORA-12899: value too large for column "MEMO" (actual: 12, maximum: 10) abcdefghijkl SQL Error [12899] [72000]: ORA-12899: value too large for column "MEMO" (actual: 12, maximum: 10) 測試起來跟Oldsand結果比較像

# by Jeffrey

to redshoe, 你的NVARCHAR2寫入字元數的上限是2000還是4000? 若是 2000,我懷疑是因為你跟 Oldsand 的資料庫 National Character Set 被設成 AL16UTF16,導致測試結果不同。

# by Hank

使用 Oracle 21c XE 發現測試過後 varchar2 (4000) 不管設定 char,Byte 都只能放入中文1333個字 但 nvarchar(2000) 確定可以放入2000個中文字

# by Suming

在 ZHT16MSWIN950 or TRADITIONAL CHINESE_TAIWAN.AL32UTF8下 NVARCHAR2最多只能宣告 2000 超過 2001 就不能宣告了, NVARCHAR2(2000) 真的可以放2000個中文字 VARCHAR2最多只能宣告 4000 超過 4001 就不能宣告了, VARCHAR2(4000) 真的可以放2000個中文字

# by Suming

更正 在TRADITIONAL CHINESE_TAIWAN.AL32UTF8 下 VARCHAR2宣告 4000 , VARCHAR2(4000) 真的只能放1333個中文字 用 alter table NVCMaxLen modify (NT Nvarchar2(2000)); 後還是只能放1333字 在ZHT16MSWIN950 下 VARCHAR2宣告 4000 , VARCHAR2(4000) 能放2000個中文字 用 alter table NVCMaxLen modify (NT Nvarchar2(2000)); 後可以放到2000字中文字

Post a comment


73 + 24 =