我们在数据迁移或是往数据表中导入中文字符时，经常会出现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 个英文 |