![7ac08523b11f15fcd1de993d4731b0d4.png](https://img-blog.csdnimg.cn/img_convert/7ac08523b11f15fcd1de993d4731b0d4.png)
“小X,问个问题啊,我这里想往一个表里插入另一张表的值。都是vachar2()字段,用length()看,原表那些值的长度都在20以下,目标表是varchar2(22),怎么会报错插入失败,超出最大值呢?”
“主任,是同一个库吗?用什么判断值的长度呢?”
“是同一个库,用的length()”
听到这里,不知道各位是否明白原因了呢?
关于长度,有两个函数很容易混淆,lengthb()和length()。
length()表示字符个数。
lengthb()表示字节数。
比如,在varchar2()中,“abc”算3个字符,“艾尔法”也算三个字符。
“abc”有三个字节,但是”艾尔法“就不一定了。
例子如下:
select a.* ,length(a),lengthb(a) from test1 a;
![38cfada6258527882078e6f9aefd69d1.png](https://img-blog.csdnimg.cn/img_convert/38cfada6258527882078e6f9aefd69d1.png)
如上所示,字符数不等于字节数!
在上面的数据库中,一个英文字符为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如下:
![19b52733e6b660eccc01ff7a2dba9e2c.png](https://img-blog.csdnimg.cn/img_convert/19b52733e6b660eccc01ff7a2dba9e2c.png)
table_varchar2如下:
![496aebfbeef0b4a3761d323b20334051.png](https://img-blog.csdnimg.cn/img_convert/496aebfbeef0b4a3761d323b20334051.png)
可以看到,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 ('好');
![40555d6494210ccb3f852fe91e490e9d.png](https://img-blog.csdnimg.cn/img_convert/40555d6494210ccb3f852fe91e490e9d.png)
![b6e3858b2669ddac399d46dc96e08e89.png](https://img-blog.csdnimg.cn/img_convert/b6e3858b2669ddac399d46dc96e08e89.png)
对于两者,最大长度都是4000,即varchar2(4000 char)和varchar2(4000 byte),所以varchar2(4000 char)>=varchar2(4000)。
恰巧以前碰到过,一个客户在测试环境设置过一个参数NLS_LENGTH_SEMANTICS,如果设置为byte,那么varchar2()默认就是bytes,如果设置为char,那默认就变成了char,客户在测试环境设置的char,而且设置的特别大,导致同一个程序在测试库是跑通的,但在生产库报列最大值不足。这点需要注意。
- 总结
这样看下来,其实客户的问题十分简单,在学习数据库的过程中,最重要的是举一反三,一定要多思多想多试验,才能在遇到问题的时候不慌不忙。
美创科技结合多年来在数据中心运维领域的知识经验沉淀,自主研发数据中心运维一体机,并架构美创运维云,实现了对数据中心的全景监控和数据库智能化运维,有效保障生产业务的运行稳定和数据完整性。
本文转自杭州美创科技有限公司公众号(美创资讯),如需二次转载,请咨询marketing@mchz.com.cn。