美創科技運維日記|Oracle欄位長度引起的思考length()和lengthb()

“小X，問個問題啊，我這裡想往一個表裡插入另一張表的值。都是vachar2()欄位,用length()看，原表那些值的長度都在20以下，目標表是varchar2(22)，怎麼會報錯插入失敗,超出最大值呢？”

“主任，是同一個庫嗎？用什麼判斷值的長度呢？”

“是同一個庫，用的length()”

 

聽到這裡，不知道各位是否明白原因了呢？

關於長度，有兩個函式很容易混淆，lengthb()和length()。

length() 表示字元個數。

lengthb() 表示位元組數。

比如，在varchar2()中，“abc”算3個字元，“艾爾法”也算三個字元。

“abc”有三個位元組，但是”艾爾法“就不一定了。

 

例子如下：

select a.* ,length(a),lengthb(a) from test1 a;

如上所示， 字元數不等於位元組數！

在上面的資料庫中，一個英文字元為1個位元組，一箇中文為3個位元組。在客戶取的值中，是有不少中文的，這就是超出最大值的原因了。

 

擴充1

不同的字符集，中文的字元長度是不一樣的，甚至英文的長度也會不同。

比如以常見的UTF16，UTF8,GBK為例，英文長度分別為4位元組，1位元組，1位元組，而中文長度為4位元組，3位元組，2位元組。需要注意的是，位元組數越大，說明儲存同樣的資料，佔用的空間越多，造成的浪費也越多。

 

擴充2

在Oracle中，有兩個總是成對出現的字符集，varchar2()和char()，這兩個的區別就是， char()自動補全，定長；varchar2()長度可變。例子如下：

 

create table table_char(a char(20));

create table table_varchar2(a varchar2(20));

insert into table_char values ('hello');

insert into table_char values (' 你好');

insert into table_char values (' 你好你好');

insert into table_varchar2 values ('hello');

insert into table_varchar2 values (' 你好');

insert into table_varchar2 values (' 你好你好');

 

table_char 如下：

table_varchar2 如下：

 

可以看到，char的位元組長度總是20，即便真實的長度不足，後面會以空格補全。所以，varchar2()一般來說是比char()節約空間的。但是char也有他的優勢，一般來說，char()的效率比vharchar2()的要高，這就是常說的以空間換時間。

 

除此之外，varchar2()由於長度可變，可能在修改的時候發生行遷移現象，影響資料庫的IO，所以，一般來說，在不修改或者修改特別少的列，且長度比較統一的列，都是建議在業務設計期間設定為char()，比如身份證號碼，銀行卡號等。

 

小思考

不知道大家發現沒有，在table_char的截圖中，length(a)分別為20,16,12,這是為什麼呢？這裡就不公佈答案了，大家可以小小的思考下~~~

 

擴充3

 

實際上就varchar2()而言，本身是可以指定以字元數量或者位元組大小來定義的，看如下例子：

 

預設不變的情況下，是varchar2(X byte),這個byte可以省略。可以看到，指定byte的時候，插入一箇中文是失敗的，而指定char的時候，插入一箇中文是成功的。說實話，目前挺少看到用指定char的。

 

create table varchar_byte(a varchar2(1 byte));

create table varchar_char(a varchar2(1 char));

insert into varchar_byte values (' 好');

insert into varchar_char values (' 好');  

 

 

對於兩者，最大長度都是4000，即varchar2(4000 char)和varchar2(4000 byte)，所以varchar2(4000 char)>=varchar2(4000）。

 

恰巧以前碰到過，一個客戶在測試環境設定過一個引數NLS_LENGTH_SEMANTICS，如果設定為byte，那麼varchar2()預設就是bytes,如果設定為char，那預設就變成了char,客戶在測試環境設定的char，而且設定的特別大，導致同一個程式在測試庫是跑通的，但在生產庫報列最大值不足。這點需要注意。

 

總結

這樣看下來，其實客戶的問題十分簡單，在學習資料庫的過程中，最重要的是舉一反三，一定要多思多想多試驗，才能在遇到問題的時候不慌不忙。

 

 

美創科技結合多年來在資料中心運維領域的知識經驗沉澱，自主研發資料中心運維一體機，並架構美創運維雲，實現了對資料中心的全景監控和資料庫智慧化運維，有效保障生產業務的執行穩定和資料完整性。

 

本文轉自杭州美創科技有限公司公眾號（新運維新資料），如需二次轉載，請諮詢：