Oracle的資料型別：char/varchar2

char

http://blog.csdn.net/NinGoo/archive/2006/12/16/1445922.aspx

char型別儲存定長字串，最小長度為1位元組(或字元數)，最長2000位元組(或字元數)，如果不指定，則預設為1。

SQL> create table t(name char);

表已建立。

SQL> desc t;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
NAME CHAR(1)

SQL> create table t(name char(2001));
create table t(name char(2001))
*
第 1 行出現錯誤:
ORA-00910: 指定的長度對於資料型別而言過長

如果插入的字串長度不足，會以空格填充。如果超過長度，則會報錯。

SQL> insert into t values('aa');
insert into t values('aa')
*
第 1 行出現錯誤:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (實際值: 2, 最大值: 1)

下面是例子展示了關於char型別的插入後續空格值的問題：
SQL> create table t(name char(20));

表已建立。

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

已建立 1 行。

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

已建立 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a "
"a "

SQL> select '"'||name||'"' from t where name='a ';

'"'||NAME||'"'
----------------------
"a "
"a "

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

已建立 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a "
"a "

SQL> select '"'||name||'"' from t where name=' a';

'"'||NAME||'"'
----------------------
" a "

可以看到，如果插入的字串最後帶有空格，在查詢的時候是會將右邊的空格刪除後再來比較的，這一點在開發時需要注意。

varchar2和varchar

varchar2和varchar是同義詞，都是指變長字串型別。但是由於某種原因，varchar可能在後續版本中改變varchar的意思還是怎麼的，oracle建議使用varchar2。

在使用varchar2型別時必須指定其長度(1~2000位元組或字元數)，不能像char一樣省略，雖然concepts文件上說其預設為1。這裡不知道oracle為什麼會這樣實現，我認為這應該算是一個缺陷。

SQL> drop table t;

表已刪除。

SQL> create table t(name varchar);
create table t(name varchar)
*
第 1 行出現錯誤:
ORA-00906: 缺失左括號

SQL> create table t(name varchar2);
create table t(name varchar2)
*
第 1 行出現錯誤:
ORA-00906: 缺失左括號

由於varchar2不會在短於指定長度的字串後面補齊空格，所以char型別中的後續空格問題不會在varchar2型別中發生。

SQL> create table t(name varchar2(20));

表已建立。

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

已建立 1 行。

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

已建立 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a"

SQL> select '"'||name||'"' from t where name='a ';

'"'||NAME||'"'
----------------------
"a "

關於字元型資料的長度問題

前面在說到char/varchar2型別時，指定其長度可能是1位元組或者1個字元數。在不同的編碼方式中，一個字元可能佔1位元組，2位元組，3位元組甚至4位元組。

SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';

NAME VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET ZHS16GBK

SQL> create table t(name char(1 byte));

表已建立。

SQL> insert into t values('測');
insert into t values('測')
*
第 1 行出現錯誤:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (實際值: 2, 最大值: 1)

SQL> create table t(name char(1 char));

表已建立。

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

已建立 1 行。

SQL> create table t(name char(1));

表已建立。

SQL> insert into t values('測');
insert into t values('測')
*
第 1 行出現錯誤:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (實際值: 2, 最大值: 1)

可以看到，由於一個漢字佔兩個byte，如果char/varchar2的長度是按byte計算的，那麼就無法插入，如果是按字元數計算的，則可以插入。而在預設情況下，oracle是按byte計算長度的，這是由於引數nls_length_semantics的預設值是byte。

SQL> show parameter nls_length

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE

在資料字典中，oracle還是會將按字元數計算的長度，按照系統當前的字符集選擇，轉換成按位元組計算的長度。由於我目前的測試系統中使用的ZHS16GBK字符集是定長字符集，每個字元佔兩個位元組，所以這裡data length為2。在一些變長字符集中，比如UTF8，則是4（UTF8中一個字元最長佔4個位元組）
SQL> drop table t;

表已刪除。

SQL> create table t(name char(1 char));

表已建立。

SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where

table_name='T' a
nd wner=user;

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH
---------- ---------- ---------- -----------
T NAME CHAR 2

但是，oracle在判斷插入的時候不是以dba_tab_columns中記錄的data_length來做為限制條件的。看看UTF8下的試驗結果：

SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';

NAME VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL> drop table t;

表已刪除。

SQL> create table t(name char(1 char));

表已建立。

SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where
2 table_name='T' and wner=user;

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH
---------- ---------- ---------- -----------
T NAME CHAR 4

SQL> insert into t values('aa');
insert into t values('aa')
*
第 1 行出現錯誤:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (實際值: 2, 最大值: 1)

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

已建立 1 行。

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

已建立 1 行。

SQL> select dump(name) from t;

DUMP(NAME)
--------------------------------------------------------------------------------
Typ=96 Len=1: 97
Typ=96 Len=3: 230,181,139

可以看到，一個英文字元佔一個位元組，那麼'aa'只佔兩個位元組，卻無法插入。'測'佔3個位元組，卻能插入。所以在判斷插入的資料是否符合長度要求時，oracle會根據定義的資料型別是按位元組還是按字元數，然後參考系統當前的字符集設定來做出正確的判斷。

實際上，oracle提供的求字串長度的函式，返回的也是字元個數，而非位元組數：
SQL> select length('a') from dual;

LENGTH('A')
-----------
1

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

LENGTH('測')
------------
1

而lengthb返回的才是位元組數：

SQL> select lengthb('a') from dual;

LENGTHB('A')
------------
1

SQL> select lengthb('測') from dual;

LENGTHB('測')
-------------
3

Oracle裡Varchar、varchar2的引數(byte和char)的區別測試

Oracle裡Varchar、varchar2的引數(byte和char)的區別測試 

1 看一下語法圖 

2 下面進行sqlplus的測試，使用scott/tiger帳號。 

SQL> create table test(name varchar(5 byte),namec varchar(5 char));表已建立。 SQL> insert into test(name) values('12345'); 

已建立 1 行。 

SQL> insert into test(name) values('123456'); 

insert into test(name) values('123456') 

第 1 行出現錯誤: 

ORA-12899: 列 "SCOTT"."TEST"."NAME" 的值太大 (實際值: 6, 最大值: 5) 

SQL> insert into test(name) values('天津'); 

已建立 1 行。 

SQL> insert into test(name) values('天津市'); 

insert into test(name) values('天津市') 

第 1 行出現錯誤: 

ORA-12899: 列 "SCOTT"."TEST"."NAME" 的值太大 (實際值: 6, 最大值: 5) 

SQL> insert into test(namec) values('12345'); 

已建立 1 行。 

SQL> insert into test(namec) values('123456'); 

insert into test(namec) values('123456') 

第 1 行出現錯誤: 

ORA-12899: 列 "SCOTT"."TEST"."NAMEC" 的值太大 (實際值: 6, 最大值: 5) 

SQL> insert into test(namec) values('天津'); 

已建立 1 行。 

SQL> insert into test(namec) values('天津市'); 

已建立 1 行。 

SQL> insert into test(namec) values('天津市北京'); 

已建立 1 行。 

SQL> insert into test(namec) values('天津市北京市'); 

insert into test(namec) values('天津市北京市') 

第 1 行出現錯誤: 

ORA-12899: 列 "SCOTT"."TEST"."NAMEC" 的值太大 (實際值: 6, 最大值: 5) 

SQL> select * from test; 

NAME  NAMEC 

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

12345 

天津 

      12345 

     天津 

      天津市 

      天津市北京 

已選擇6行。 

SQL> 

3 分析 

由於一個漢字在我的GBK系統裡佔用2個位元組，所以byte的只能插入2個漢字，而char的可以插入5個漢字。 

所以byte 就是位元組數，對於漢字，GBK佔用2個位元組，如果是UTF-8則佔用3個位元組。 

為了充分利用4000個的上限，給自己減少麻煩，建議儲存含有中文文字的欄位時，採用char型別。