我們在資料遷移或是往資料表中匯入中文字元時，經常會出現varchar型別超過字元長度的報錯，今天我們就來分析一下varchar資料型別長度的佔比。

在說varchar型別之前先來說說char資料型別。char型別是指定長的字串。在基表中，定義char型別的列時，其最大儲存長度由資料庫頁面大小決定，可以指定一個不超過其最大儲存長度的正整數作為字元長度，例如：char(100)。如果未指定長度，預設為1。char資料型別最大儲存長度和頁面大小有關，但是，在表示式計算中，該型別的長度上限不受頁面大小限制為32767。

char 最大儲存長度和頁面大小的對應關係
資料庫頁面大小	實際最大長度
4K	1900
8K	3900
16K	8000
32K	8188

這個限制長度只針對建表的情況，在定義變數的時候，可以不受這個限制長度的限制。

varchar 資料型別是指的變長字串，用法類似char資料型別，可以指定一個不超過8188的正整數作為字元長度，例如：varchar(100)。如果未指定長度，預設為8188。在基表中，當沒有指定USINGLONG ROW 儲存選項時，插入varchar資料型別的實際最大儲存長度由資料庫頁面大小決定。

在使用DMINIT初始化資料庫的時候，有兩個引數CHARSET/UNICODE_FLAG和LENGTH_IN_CHAR跟字符集相關。

Ø CHARSET/UNICODE_FLAG ：此參數列示了資料庫中所有資料的字符集，包括資料字典的字符集。需要注意的是，資料庫一旦初始化完成，字符集就將無法修改。我們可以使用select unicode來查詢當前資料庫的字符集種類，0代表gb18030，1代表UTF8。

Ø LENGTH_IN_CHAR ：此引數決定了，資料庫中的varchar型別物件的長度是否以字元為單位。取值為1則設定為以字元為單位，將儲存長度值按照理論字元長度進行放大。取值為0則所有varchar型別物件的長度以位元組為單位。

測試環境

作業系統：Red Hat Enterprise Linux Server release 7.9 (Maipo)

資料庫系統：DM Database Server 64 V8-4-2-38-21.07.02-142948-10018-ENT

1. 當LENGTH_IN_CHAR=0，UNICODE_FLAG=0時 (字符集是gb18030,長度以位元組為單位)

SQL> create table test (name varchar(3));

executed successfully

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

affect rows 1

SQL> insert into test values (' 測a');

affect rows 1

SQL> insert into test values (' 測試');

insert into test values (' 測試');

[-6169]:Column [NAME] out of length.

SQL> commit;

executed successfully

SQL> select name,length(name),lengthb(name) from test;

NAME LENGTH(NAME) LENGTHB(NAME)

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

測 1 2

測a 2 3

當字符集是gb18030,長度以位元組為單位時，一個漢字佔兩個位元組，一個英文字元佔一個位元組。Varchar（1）表示1個位元組，因編碼為gb18030，一個漢字佔用兩個位元組。所以varchar長度為3時，實際可以錄入1個漢字（1*3=3）,3個英文字元。

2. 當LENGTH_IN_CHAR=1，UNICODE_FLAG=0時 (字符集是gb18030,長度以字元為單位)

SQL> create table test (name varchar(3));

executed successfully

SQL> insert into test values (' 測試');

affect rows 1

SQL> insert into test values (' 測試姓');

affect rows 1

SQL> insert into test values (' 測試姓名');

insert into test values (' 測試姓名');

[-6169]:Column [NAME] out of length.

SQL> commit;

executed successfully

SQL> select name,length(name),lengthb(name) from test;

NAME LENGTH(NAME) LENGTHB(NAME)

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

測試 2 4

測試姓 3 6

當字符集是gb18030,長度以字元為單位時，一個漢字佔兩個位元組，一個英文字元佔一個位元組。varchar表示2個位元組，因編碼為gb18030，一個漢字佔用兩個位元組。所以varchar長度為3時，實際可以錄入2個漢字（2*3=6）,6個英文字元。

3. 當LENGTH_IN_CHAR=0，UNICODE_FLAG=1時(字符集是utf-8,長度以位元組為單位)

SQL> create table test (name varchar(3));

executed successfully

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

affect rows 1

SQL> insert into test values (' 測試');

insert into test values (' 測試');

[-6169]:Column [NAME] out of length.

SQL> insert into test values (' 測a');

insert into test values (' 測a');

[-6169]:Column [NAME] out of length.

SQL> commit;

executed successfully

SQL> select name,length(name),lengthb(name) from test;

NAME LENGTH(NAME) LENGTHB(NAME)

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

測 1 3

當引數UNICODE_FLAG=1、LENGTH_IN_CHAR=0時。Utf8一個漢字佔用三個位元組，一個英文佔用一個位元組。varchar以位元組為單位，一個varchar等於一個位元組。

4. 當LENGTH_IN_CHAR=1，UNICODE_FLAG=1時(字符集是utf-8,長度以字元為單位)

SQL> create table test (name varchar(3));

executed successfully

SQL> insert into test values (' 測試姓');

affect rows 1

SQL> insert into test values (' 測試姓名');

affect rows 1

SQL> insert into test values (' 測試姓名a');

insert into test values (' 測試姓名a');

[-6169]:Column [NAME] out of length.

SQL> commit;

executed successfully

SQL> select name,length(name),lengthb(name) from test;

NAME LENGTH(NAME) LENGTHB(NAME)

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

測試姓 3 9

測試姓名 4 12

這裡我們會發現一個奇怪的情況，明明設定是varchar(3)，為什麼可以插入4個漢字呢。這是因為在DM8中資料庫實際儲存資料是以位元組為單位。在lengtg_in_char=1且字符集為utf-8的時候，VARCHAR型別物件的實際存放的最大長度是VARCHAR型別定義的長度*4位元組。

也就是說，這裡一個varchar(3)的結構可以存放的資料為3*4=12個位元組。然而事實上UTF-8中的一個漢字一般只用佔用3個位元組，所以這裡我們可以插入12/3=4個漢字。

當引數UNICODE_FLAG=1、LENGTH_IN_CHAR=1時，varchar表示4個位元組，因編碼為uft8，一個漢字佔用三個位元組。所以varchar長度為3時，實際可以錄入4個漢字（3*4=12）,12個英文字元。

5. 總結

當LENGTH_IN_CHAR=0的情況下，varchar()的長度是以位元組數為單位。這時我們只需要考慮漢字和全形字元所佔用位元組數，其中gb18030的一個漢字是兩個位元組，utf-8的一個漢字一般是三個位元組。如果插入資料的總位元組數大於varchar定義的長度，則會插入失敗。

當LENGTH_IN_CHAR=1的情況下，varchar()所能儲存的位元組數將會按照一定比例擴充套件。字符集為gb18030時varchar的位元組數等於定義長度*2，字符集為utf-8時varchar的位元組數為定義長度*4。

彙總表

varchar(1) 分類	所佔位元組數	1 個漢字佔用位元組數	1 個英文佔用位元組數	儲存漢字	儲存英文
字符集是gb18030，長度以位元組為單位	1 個位元組	2 個位元組	1 個位元組	0 個漢字	1 個英文
字符集是gb18030，長度以字元為單位	2 個位元組	2 個位元組	1 個位元組	1 個漢字	2 個英文
字符集是uft8，長度以位元組為單位	1 個位元組	3 個位元組	1 個位元組	0 個漢字	1 個英文
字符集是uft8，長度以字元為單位	4 個位元組	3 個位元組	1 個位元組	1 個漢字	4 個英文

DM8 varchar型別長度

相關文章