运维日记|oracle字段长度引起的思考 – 作者:database

不知大家是否遇到过oracle字段长度引发的问题？在oracle中，同一个库，想往一个表里插入另一张表的值。都是vachar2()字段,用length()看，原表那些值的长度都在20以下，目标表是varchar2(22)，怎么会报错插入失败,超出最大值呢？

到底是什么原因呢？

其实，关于字段长度，有两个函数很容易混淆，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_varchr2如下：

可以看到，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，而且设置的特别大，导致同一个程序在测试库是跑通的，但在生产库报列最大值不足。这点需要注意。

总结

这样看下来，其实客户的问题十分简单，在学习数据库的过程中，最重要的是举一反三，一定要多思多想多试验，才能在遇到问题的时候不慌不忙。

 

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。

 

 

来源：freebuf.com 2020-07-22 10:01:26 by: database