驗證ORACLE不同字元集中漢字占用的byte及NLS_LENGTH_SEMANTICS參數的影響

官方文檔中關于NLS_LENGTH_SEMANTICS參數介紹：

可選值是BYTE | CHAR，預設是BYTE，可以在資料庫和會話級動态修改。也可以在用戶端環境變量中進行設定(不适合JDBC瘦用戶端)

NCHAR, NVARCHAR2, CLOB, and NCLOB 始終是基于BYTE。

以SYS使用者登陸時不考慮NLS_LENGTH_SEMANTICS參數統一使用BYTE；除非在建立對象等DDL語句中明确定義。

ORACLE強烈建議不能在資料庫執行個體級别設定此參數。

----

AL32UTF8字元集時一個漢字占三個byte

ZHS16GBK字元集時一個漢字占兩個byte

本實驗結論：

1.建立表時指定字段類型為 varchar2(10)時--即不指定使用的類型，字段使用的dba_tab_columns視圖的char_used字段值等于建立表時會話級的nls_length_semantics=BYTE

2.在AL32UTF8字元集時一個漢字是3個BYTE，ZHS16GBK字元集時一個漢字占兩個byte。

3.如果字段類型為 varchar2(10)為BYTE(dba_tab_columns視圖char_used字段是B(BYTE))，能存放三個漢字--->一個漢字是3個BYTE

4.如果字段類型為 varchar2(10)為char(dba_tab_columns視圖char_used字段是C(CHAR))，能存放十個漢字--->一個漢字是3個BYTE

5.以SYS使用者登陸時不考慮NLS_LENGTH_SEMANTICS參數統一使用BYTE；除非在建立對象等DDL語句中明确定義。

6.在系統或會話級修改nls_length_semantics=char參數值，不會對已有表中varchar2字段的char_used進行修改

實驗的步驟：

實驗1-4使用AL32UTF8的資料庫進行測試。

實驗1：預設nls_length_semantics=BYTE且指定字段類型為 varchar2(10)

此時從dba_tab_columns視圖的char_used字段可以發現使用的是B(BYTE)，在AL32UTF8字元集下一個漢字是3個BYTE。

實驗2：修改nls_length_semantics=CHAR且指定字段類型為 varchar2(10)

此時從dba_tab_columns視圖的char_used字段可以發現使用的是C(CHAR)，此時可以插入10個字元。

實驗3：alter system set nls_length_semantics=char;參數不會對已有的表中varchar2字段的char_used進行修改

實驗4：測試以SYS使用者登陸時不考慮NLS_LENGTH_SEMANTICS參數統一使用BYTE；除非在建立對象等DDL語句中明确定義。

實驗5：測試ZHS16GBK字元集時一個漢字占的字元數

具體實驗過程

#################################################################

實驗1：

預設nls_length_semantics=BYTE且指定字段類型為 varchar2(10)

此時從dba_tab_columns視圖的char_used字段可以發現使用的是B(BYTE)，在AL32UTF8字元集下一個漢字是3個BYTE。

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> select lengthb('白') from dual;
 LENGTHB('白')
-------------
            3
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE
SQL> create table t1(id number,name varchar2(10));
Table created.
SQL> create table t4(id number,name varchar2(10 char));
Table created.

SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T4                             NAME                           VARCHAR2   C
T4                             ID                             NUMBER
T1                             NAME                           VARCHAR2   B
T1                             ID                             NUMBER
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)

在SQL*PLUS視窗輸入時--經過了字元轉換（猜測是主機的）顯示是5個漢字30個字元
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
                        *
ERROR at line 1:
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 30, maximum: 10)
SQL> insert into t1 values(1,'浙江杭州');

在PL/SQL DEVELOPER工具視窗：
SQL> insert into t1 values(1,'浙江省杭州');
 
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (實際值: 15, 最大值: 10)
 
SQL> insert into t1 values(1,'浙江杭州');
 
insert into t1 values(1,'浙江杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (實際值: 12, 最大值: 10)
 
SQL> insert into t1 values(1,'浙江省');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
        ID NAME
---------- ----------
         1 浙江省
SQL> insert into t4 values(1,'浙江省杭州市西湖區西湖');
insert into t4 values(1,'浙江省杭州市西湖區西湖')
ORA-12899: 列 "BYS"."T4"."NAME" 的值太大 (實際值: 11, 最大值: 10)
SQL> insert into t4 values(1,'浙江省杭州市西湖區西');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t4;
        ID NAME
---------- ----------------------------------------
         1 浙江省杭州
         1 浙江省杭州市西湖區西

#####################################################################

實驗2：

修改nls_length_semantics=CHAR且指定字段類型為 varchar2(10)

此時從dba_tab_columns視圖的char_used字段可以發現使用的是C(CHAR)，此時可以插入10個字元。

SQL> alter session set nls_length_semantics=CHAR;
Session altered.
SQL> show parameter nls_length_semantics;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL> create table t2(id number,name varchar2(10));
Table created.
SQL> create table t3(id number,name varchar2(10 char));
Table created.
SQL> create table t6(id number,name varchar2(10 byte));
Table created.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T3' or table_name='T2' or table_name='T6' ;

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T6                             NAME                           VARCHAR2   B
T6                             ID                             NUMBER
T3                             NAME                           VARCHAR2   C
T3                             ID                             NUMBER
T2                             NAME                           VARCHAR2   C
T2                             ID                             NUMBER

在PL/SQL DEVELOPER工具視窗：
SQL> insert into t2 values(1,'浙江省杭州市江幹區下沙');
 
insert into t2 values(1,'浙江省杭州市江幹區下沙')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (實際值: 11, 最大值: 10)
 
SQL> insert into t2 values(1,'浙江省杭州市江幹區下');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市江幹區');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t2;
        ID NAME
---------- ----------------------------------------
         1 浙江省杭州市江幹區下
         1 浙江省杭州市江幹區

################################################################

實驗3：alter system set nls_length_semantics=char;參數不會對原有的表中varchar2字段的char_used進行修改

SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T4                             NAME                           VARCHAR2   C
T4                             ID                             NUMBER
T1                             NAME                           VARCHAR2   B
T1                             ID                             NUMBER

SQL> alter system set nls_length_semantics=char;
System altered.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T1                             ID                             NUMBER
T1                             NAME                           VARCHAR2   B
T4                             ID                             NUMBER
T4                             NAME                           VARCHAR2   C
此時對T1進行插入操作，超過3個漢字會報錯
SQL> insert into t1 values(11,'浙江省美');
 
insert into t1 values(11,'浙江省美')
 
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 12, maximum: 10)

實驗4：測試以SYS使用者登陸時不考慮NLS_LENGTH_SEMANTICS參數統一使用BYTE；除非在建立對象等DDL語句中明确定義。
SQL> show user
USER is "SYS"
SQL>  show parameter nls_length_semantics;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE
SQL> SQL> alter system set nls_length_semantics=char;
System altered.
SQL>  show parameter nls_length_semantics;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
SQL> create table bys.t8(id number,name varchar2(10));
Table created.
SQL> create table bys.t9(id number,name varchar2(10 char));
Table created.
SQL> col data_type for a10
set linesize 160
SQL> SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T8' or table_name='T9' ;

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T8                             ID                             NUMBER
T8                             NAME                           VARCHAR2   B
T9                             ID                             NUMBER
T9                             NAME                           VARCHAR2   C

##########################################################

實驗5：測試ZHS16GBK字元集時一個漢字占的字元數

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE
SQL> create table t1(id number,name varchar2(8));
Table created.
SQL> create table t2(id number,name varchar2(8 char));
Table created.

SQL>  select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T2' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE  C
------------------------------ ------------------------------ ---------- -
T2                             NAME                           VARCHAR2   C
T2                             ID                             NUMBER
T1                             NAME                           VARCHAR2   B
T1                             ID                             NUMBER

SQL> select lengthb('白') from dual;
 LENGTHB('白')
-------------
            2
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (實際值: 10, 最大值: 8)
SQL> insert into t1 values(1,'浙江杭州');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市西湖區');
insert into t2 values(1,'浙江省杭州市西湖區')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (實際值: 9, 最大值: 8)
SQL> insert into t2 values(1,'浙江省杭州市西湖');
1 row inserted
SQL> commit;
Commit complete
 
SQL> select * from t1;
        ID NAME
---------- --------
         1 浙江杭州
SQL> select * from t2;
        ID NAME
---------- ----------------
         1 浙江省杭州市西湖

驗證ORACLE不同字元集中漢字占用的byte及NLS_LENGTH_SEMANTICS參數的影響

繼續閱讀

SQLPLUS中HELP幫助的安裝與删除

用戶端字元集的作用總結

在LINUX的指令提示符及CMD指令提示符中顯示時間

解決sqlplus中輸錯指令時的删除、光标鍵不能使用問題

開啟歸檔模式及其相關參數