Oracle資料庫中漢字長度的問題

問題：

　　varchar2（4000） abc；

　　intert into table_name（abc） values（'這裡有1500個漢字……'）；

　　報錯：插入字元過長。經過測試 ，發現一個漢字佔3個字 節，所以會報錯。

　　問題癥結：

　　使用的字元集是UTF8，就會出現此錯誤。

　　你可以使用命令查看：

 

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; PARAMETER ------------------------------------------------ VALUE ------------------------------------------------ NLS_CHARACTERSET AL32UTF8

　　解決方案：

　　建議使用ZHS16GBK字元集。

　　執行此操作後，問題即可得以解決。

 

SQL> SHUTDOWN IMMEDIATE;  
SQL> STARTUP MOUNT; 
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
SQL> ALTER DATABASE OPEN; 
SQL> ALTER DATABASE CHARACTER SET AL32UTF8/ZHS16GBK;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

漢字在oracle中佔用位元組數

一，漢字在oracle中佔用位元組數

一定要在建庫的時候就選擇好字元集，否則可能給後續的開發或者遷移帶來問題。在開發中字元集問題通常會導致應用程式層面上的字元越界或者亂碼問題。

先來看看2個使用不同字元集的資料庫：

先來看server1：

SQL> select * from v$nls_parameters a ;

 

PARAMETER                                                       VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_LANGUAGE                                                    SIMPLIFIED CHINESE

NLS_TERRITORY                                                   CHINA

NLS_CURRENCY                                                    RMB

NLS_ISO_CURRENCY                                                CHINA

NLS_NUMERIC_CHARACTERS                                          .,

NLS_CALENDAR                                                    GREGORIAN

NLS_DATE_FORMAT                                                 DD-MON-RR

NLS_DATE_LANGUAGE                                               SIMPLIFIED CHINESE

NLS_CHARACTERSET                                                WE8ISO8859P1

NLS_SORT                                                        BINARY

NLS_TIME_FORMAT                                                 HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                                            DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT                                              HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT                                         DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY                                               RMB

NLS_NCHAR_CHARACTERSET                                          AL16UTF16

NLS_COMP                                                        BINARY

NLS_LENGTH_SEMANTICS                                            BYTE

NLS_NCHAR_CONV_EXCP                                             FALSE

 

19 rows selected

 

SQL> select '漢字' from dual;

 

'。。'

------

。

 

再看看server2：

SQL> select * from v$nls_parameters a ;

 

PARAMETER                                                       VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_LANGUAGE                                                    SIMPLIFIED CHINESE

NLS_TERRITORY                                                   CHINA

NLS_CURRENCY                                                    RMB

NLS_ISO_CURRENCY                                                CHINA

NLS_NUMERIC_CHARACTERS                                          .,

NLS_CALENDAR                                                    GREGORIAN

NLS_DATE_FORMAT                                                 DD-MON-RR

NLS_DATE_LANGUAGE                                               SIMPLIFIED CHINESE

NLS_CHARACTERSET                                                AL32UTF8

NLS_SORT                                                        BINARY

NLS_TIME_FORMAT                                                 HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                                            DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT                                              HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT                                         DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY                                               RMB

NLS_NCHAR_CHARACTERSET                                          AL16UTF16

NLS_COMP                                                        BINARY

NLS_LENGTH_SEMANTICS                                            BYTE

NLS_NCHAR_CONV_EXCP                                             FALSE

 

19 rows selected

 

SQL> select '漢字' from dual;

 

'漢字'

------

漢字

 

通過比較上述兩個資料庫的區別，發現他們的NLS_CHARACTERSET不同，一個是WE8ISO8859P1，而另外一個是AL32UTF8。WE8ISO8859P1是單位元組8位字元集，AL32UTF8是變長多位元組編碼。

現在的用戶端的字元集為：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

WE8ISO8859P1沒有漢字編碼，一般來講，有中文字元就不應該使用這個字元集，雖然修改用戶端和伺服器端相同時可以解決亂碼問題。

有關漢字在oracle中佔用的位元組數問題：

在WE8ISO8859P1字元集下，一個漢字佔了1個位元組。

在AL32UTF8字元集下面，一個漢字佔了3個位元組。

在ZHS16GBK是佔用了2個位元組。

Varchar2，varchar，nvarchar2均為變長字元類型，char則是固定長度。

用vsize函數來看的話很明顯：

Server1:

SQL> select vsize('漢') from dual;

 

 VSIZE('。')

-----------

         1

Server2:

SQL> select vsize('漢') from dual;

 

 VSIZE('漢')

-----------

         3

而用length來看，則均為1：

Server1：

SQL> select length('漢') from dual;

 

 LENGTH('。')

------------

          1

Server2：

SQL> select length('漢') from dual;

 

 LENGTH('漢')

------------

          1

 

下面具體來看個對比：

Server1和server2中均建立下面的表：

create table t_test_var

(

v_char2 char(2),

v_char3 char(3),

v_varchar22 varchar2(2),

v_varchar23 varchar2(3),

v_varchar2 varchar(2),

v_varchar3 varchar(3),

v_nvarchar22 nvarchar2(2),

v_nvarchar23 nvarchar2(3)

);

然後分別在兩個資料庫中插入測試資料：

Server1:

SQL> insert into t_test_var values ('我','我','我','我','我','我','我','我');

 

1 row inserted

SQL> insert into t_test_var values ('我們','我們','我們','我們','我們','我們','我們','我們');

 

1 row inserted

SQL> insert into t_test_var values ('我們是','我們是','我們是','我們是','我們是','我們是','我們是','我們是');

 

insert into t_test_var values ('我們是','我們是','我們是','我們是','我們是','我們是','我們是','我們是')

 

ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_CHAR2" (actual: 3, maximum: 2)

 

說明server1中char用一個位元組來儲存一個漢字。

修改一下最後一條插入語句，使得char類型不越界：

 

SQL> insert into t_test_var values ('我們','我們是','我們是','我們是','我們是','我們是','我們是','我們是');

 

insert into t_test_var values ('我們','我們是','我們是','我們是','我們是','我們是','我們是','我們是')

 

ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_VARCHAR22" (actual: 3, maximum: 2)

 

SQL>

則說明了server1中的varchar2類型也是一個位元組儲存一個漢字，再次修改：

SQL> insert into t_test_var values ('我們','我們是','我們','我們是','我們是','我們是','我們是','我們是');

 

insert into t_test_var values ('我們','我們是','我們','我們是','我們是','我們是','我們是','我們是')

 

ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_VARCHAR2" (actual: 3, maximum: 2)

則說明varchar也是一個漢字佔一個位元組儲存空間，繼續修改：

 

SQL> insert into t_test_var values ('我們','我們是','我們','我們是','我們','我們是','我們是','我們是');

 

insert into t_test_var values ('我們','我們是','我們','我們是','我們','我們是','我們是','我們是')

 

ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_NVARCHAR22" (actual: 3, maximum: 2)

則說明nvarchar2也是一個漢字佔一個位元組儲存空間。

這是因為在WE8ISO8859P1字元中，根本沒有漢字編碼。所以得出以上的實驗結果。

 

再來看看Server2:

SQL> insert into t_test_var values ('我','我','我','我','我','我','我','我');

 

insert into t_test_var values ('我','我','我','我','我','我','我','我')

 

ORA-01401: inserted value too large for column

SQL> insert into t_test_var values ('我們','我們','我們','我們','我們','我們','我們','我們');

 

insert into t_test_var values ('我們','我們','我們','我們','我們','我們','我們','我們')

 

ORA-01401: inserted value too large for column

SQL> insert into t_test_var values ('我們是','我們是','我們是','我們是','我們是','我們是','我們是','我們是');

 

insert into t_test_var values ('我們是','我們是','我們是','我們是','我們是','我們是','我們是','我們是')

 

ORA-01401: inserted value too large for column

 

SQL> select cast('漢' as char(1)) from dual;

 

select cast('漢' as char(1)) from dual

 

ORA-25137: Data value out of range

 

SQL> select cast('漢' as char(2)) from dual;

 

select cast('漢' as char(2)) from dual

 

ORA-25137: Data value out of range

 

SQL> select cast('漢' as char(3)) from dual;

 

CAST('漢'ASCHAR(3))

-------------------

漢

SQL> insert into t_test_var (v_varchar22) values ('漢');

 

insert into t_test_var (v_varchar22) values ('漢')

 

ORA-01401: inserted value too large for column

SQL> insert into t_test_var (v_varchar23) values ('漢');

 

1 row inserted