字串是我們設計資料庫經常用到的型別，從傳統的ASCII格式到UTF-8格式，不同應用需求對應不同的字元型別和長度配置。針對Oracle而言，最常用的型別無外乎char和varchar2兩個基本型別。

對於一些中文應用，設計人員就需要重點關注資料表中字串長度問題。因為在不同的字串編碼方式下，一箇中文字元對應的字元byte長度是不同的。比如，一個欄位長度設定為10，如果是英文字元就可以容納10位長度，如果是中文字元就只能容納最多5位長度。如果採用如UTF-8類寬泛字符集型別，也就3-4箇中文字元。

字串型別的長度定義，這個含義是什麼？在Oracle中，我們定義varchar2(10)其實是有兩層理解，一則是10個byte位長度，另一則是10個輸入字元長度。兩種理解前者是技術派，後者理解是從業務應用角度看問題。其實，在Oracle實現層面，兩種策略都是支援的。本篇主要說明一下字串長度問題，供日後待查。

1、環境說明

筆者選擇Oracle 11gR2版本進行測試實驗，具體版本號為11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

當前字符集為AL32UTF8。

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE

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

NLS_CHARACTERSET AL32UTF8

2、預設引數設定

首先實驗一下預設條件下，Oracle字串行為方式。建立實驗表t。

SQL> create table t (v_char varchar2(10));

Table created

SQL> desc t;

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10) Y

SQL> insert into t values ('ttt');

1 row inserted

SQL> insert into t values ('tttttttttt');

1 row inserted

SQL> commit;

Commit complete

預設情況下，對於varchar2(10)型別，英文字元可以容納下10個字元。下面測試中文字元情況。

SQL> insert into t values ('保護');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t values ('保護模式');

insert into t values ('保護模式')

ORA-12899: 列 "TEST"."T"."V_CHAR" 的值太大 (實際值: 12, 最大值: 10)

SQL> insert into t values ('保護模');

1 row inserted

SQL> commit;

Commit complete

對於長度varchar2(10)的欄位型別，三位中文似乎是一個上限。從四位中文輸入報錯的情況看，當前資料庫將一箇中文識別為三個長度進行計量。潛含的意思是在UTF-8編碼情況下，varchar2(10)表示的是10位byte長度。

那麼，控制長度單位是引數是什麼呢？

3、Char & Byte

從Oracle語法上看，varchar2(10)這個10後面其實是包括單位的。預設情況下，我們都不需要去管理這個配置。如果我們不顯示的進行指定，Oracle會取自引數nls_length_semantics。

SQL> show parameter nls_length

NAME TYPE VALUE

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

nls_length_semantics string BYTE

在建立資料表和定義欄位過程中，我們是可以明確指定長度單位的。Oracle提供了兩種選擇，一個是byte，另一個是char型別。

SQL> create table t_char (v_char varchar2(10 char), v_byte varchar2(10));

Table created

SQL> desc t_char;

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10 CHAR) Y

V_BYTE VARCHAR2(10) Y

當前是byte預設單位模式下（注意這個前提），v_char欄位明顯顯示10位字元長度。

SQL> insert into t_char(v_char) values ('tttttttttt');

1 row inserted

SQL> insert into t_char(v_char) values ('ttttttttttt');

insert into t_char(v_char) values ('ttttttttttt')

ORA-12899: 列 "TEST"."T_CHAR"."V_CHAR" 的值太大 (實際值: 11, 最大值: 10)

SQL> commit;

Commit complete

10位英文字元測試透過，下面試驗一下中文字元。

SQL> insert into t_char(v_char) values ('實驗實驗實驗實驗實驗');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t_char(v_char) values ('實驗實驗實驗實驗實驗實驗');

insert into t_char(v_char) values ('實驗實驗實驗實驗實驗實驗')

ORA-12899: 列 "TEST"."T_CHAR"."V_CHAR" 的值太大 (實際值: 12, 最大值: 10)

SQL> select * from t_char;

V_CHAR V_BYTE

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

tttttttttt

實驗實驗實驗實驗實驗

十個中文字元可以容納，使用dump檢視儲存結構編碼。

SQL> select dump(v_char, 1016) as a from t_char;

A

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

Typ=1 Len=10 CharacterSet=AL32UTF8: 74,74,74,74,74,74,74,74,74,74

Typ=1 Len=30 CharacterSet=AL32UTF8: e5,ae,9e,e9,aa,8c,e5,ae,9e,e9,aa,8c,e5,ae,9e,e9,aa,8c,e5,ae,9e,e9,aa,8c,e5,ae,9e,e9,aa,8c

說明：在char計量單位下，資料庫不會按照儲存技術結構進行長度選取，而是根據實際存放長度進行計量。

那麼，預設情況下的取值，與顯示進行byte相對應。

SQL> create table t_byte (v_char varchar2(10 byte));

Table created

SQL> desc t_byte;

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10) Y

4、引數變更實驗

如果進行引數變化，資料庫行為是如何呢？

SQL> conn sys/oracle@sicsdb as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> show parameter nls_leng

NAME TYPE VALUE

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

nls_length_semantics string BYTE

修改引數取值。

SQL> alter system set nls_length_semantics=char scope=both;

System altered

SQL> show parameter nls_leng

NAME TYPE VALUE

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

nls_length_semantics string CHAR

注意，此時已經修改了引數，進行實驗。

SQL> create table t_test (v_char varchar2(10));

Table created

SQL> desc t_test;

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10) Y

SQL> insert into t_test values ('實驗實驗實驗');

insert into t_test values ('實驗實驗實驗')

ORA-12899: 列 "TEST"."T_TEST"."V_CHAR" 的值太大 (實際值: 18, 最大值: 10)

長度配置預設單位沒有變化。依然是byte的效果。如果需要引數生效，需要重新啟動資料庫例項。

SQL> startup force

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2288080 bytes

Variable Size 939525680 bytes

Database Buffers 3321888768 bytes

Redo Buffers 12079104 bytes

Database mounted.

Database opened.

SQL> show parameter nls_leng

NAME TYPE VALUE

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

nls_length_semantics string CHAR

此時檢視預設建立行為。

SQL> conn test/test@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as test

SQL> create table t_test (v_char varchar2(10));

Table created

SQL> desc t_test

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10) Y

SQL> insert into t_test values ('實驗實驗實驗實驗實驗');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from t_test;

V_CHAR

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

實驗實驗實驗實驗實驗

同時，過去定義為byte計量的單元欄位也被明顯顯示出來。

SQL> desc t_char

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10) Y

V_BYTE VARCHAR2(10 BYTE) Y

SQL> desc t_byte

Name Type Nullable Default Comments

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

V_CHAR VARCHAR2(10 BYTE) Y

5、結論

資料庫欄位長度在傳統的應用開發領域是一個比較重要的問題。傳統觀點認為這部分應該體現業務邏輯思想，也就是將欄位的長度與業務邏輯對應，甚至可以透過資料庫來控制使用者操作。但是，由於字串長度因素、軟體設計思想的變化影響，這部分邏輯越來越被推到了介面和應用邏輯層次來完成。

對Oracle而言，提供char方式定義欄位是一種備選的策略。在確實需要資料庫嚴格反應業務邏輯的情況下，是一種不錯的選擇。

聊聊字串資料長度和nls_length_semantics引數

相關文章