一個UTF8字符集相關問題的解決

一般設計資料庫的時候，我們都考慮一個漢字佔用兩個位元組。所以設計資料庫的時候，如果認為某欄位最長要存四個漢字，該欄位都會定義為varchar2(8)。[@more@]

SQL> create table t1 (col1 varchar2(8));
Table created.

但是測試插入三個漢字的時候就報錯了。
SQL> insert into t1 values('一二三');
insert into t1 values('一二三')
*
ERROR at line 1:
ORA-12899: value too large for column "NC31"."T1"."COL1" (actual: 9, maximum:8)

檢查相關引數及環境變數。
SQL> select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET';
PARAMETER VALUE
------------------------- --------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

客戶端NLS_LANG=AMERICAN_AMERICA.ZHS16GBK。

原來資料庫使用的是UTF8字符集，難怪一個漢字佔用3個位元組。這樣一來原先按一個漢字佔兩個位元組設計的資料庫，應用的時候很多欄位都會因長度不夠，出現ORA-12899錯誤。
不能把資料庫的字符集改成ZHS16GBK。因為系統要求不僅能支援中文，還要能支援其他亞洲字元。這樣CHARACTER SET就只能設這成UTF8或AL32UTF8，這兩種字符集每個漢字佔用的位元組數分別是3和4，都不是2。
更改所有CHAR/VARCHAR欄位的長度也不現實。整套系統是從其他公司買的產品，內含上千張表，逐個去修改欄位不太可能。

檢視引數NLS_LENGTH_SEMANTICS。
Oracl文件中的說明：
Syntax: NLS_LENGTH_SEMANTICS = string
Range of values: BYTE | CHAR
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

檢視資料庫中該引數的設定。
SQL> select * from nls_database_parameters where parameter like 'NLS%SEMANTICS';
PARAMETER VALUE
------------------------- --------------------
NLS_LENGTH_SEMANTICS BYTE

SQL> show parameter nls_length
NAME TYPE VALUE
------------------------------------ ---------- -------
nls_length_semantics string BYTE

把該引數改成CHAR
SQL>alter system set NLS_LENGTH_SEMANTICS=BYTE scope=BOTH;

修改後檢視引數。
SQL> select * from nls_database_parameters where parameter like 'NLS%SEMANTICS';
PARAMETER VALUE
------------------------- --------------------
NLS_LENGTH_SEMANTICS BYTE (還是byte？這裡還搞不懂)

SQL> show parameter nls_length
NAME TYPE VALUE
------------------------------------ ---------- -------
nls_length_semantics string CHAR

從新執行insert操作。
SQL> insert into t1 values('一二三');
insert into t1 values('一二三')
*
ERROR at line 1:
ORA-12899: value too large for column "NC31"."T1"."COL1" (actual: 9, maximum:8)
還是出錯!

新建立一張表，再測試。
SQL> create table t2 (col1 varchar2(8));
表已建立。

SQL> insert into t2 values('一二三')
已建立 1 行。

SQL> select * from t2;
COL1
----------------
一二三

可以了！
原來文件中'Existing columns are not affected'開始被錯誤理解成列中已有的值不受影響。再檢視t1和t2的表結構，可以發現一些差別。
SQL> desc t1
名稱 是否為空? 型別
----------------------------------------- -------- -------------------------

COL1 VARCHAR2(8)

SQL> desc t2
名稱 是否為空? 型別
----------------------------------------- -------- -------------------------
COL1 VARCHAR2(8 CHAR)


引數NLS_LENGTH_SEMANTICS改成CHAR以後，t2.col1列可以儲存8個漢字，英文字元也只能儲存8個。
SQL> insert into t2 values('一二三四五六七八');
已建立 1 行。

SQL> insert into t2 values('abcdefgh');
已建立 1 行。

SQL> insert into t2 values('abcdefghi');
insert into t2 values('abcdefghi')
*
ERROR 位於第 1 行:
ORA-12899: value too large for column "NC31"."T2"."COL1" (actual: 9, maximum:8)

在尋找解決辦法的時候，發現一篇有關字符集的好文章，雖然和這個問題關係不大，還是把url貼上來，以供日後參考。http://www.itpub.net/showthread.php?threadid=276524&pagenumber=