Oracle NVarChar2 可存中文字數上限問題

同事分享在 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 個中文字元。

歡迎推文分享:
Published 11 June 2018 07:59 AM 由 Jeffrey
Filed under:
Views: 4,296



意見

# 陳喬依 said on 11 June, 2018 10:41 AM

印象中…

型態有nvarchar2(byte) 跟 nvarchar2(char)

好像char 就可以放到4000?

# Jeffrey said on 13 June, 2018 12:43 AM

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

# 陳喬依 said on 13 June, 2018 11:13 AM

抱歉,我記錯了…

是varchar2才有兩種型態

(未經查證就回覆了,不好意思)

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<June 2018>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication