informix Oracle 字符类型比较整理

 informix oracle字符数据类型

 

在informix中有如下几种对字符串操作的类型：
    char(character),varchar,nchar,nvarchar,lvarchar,text
在ORACLE中有如下几种对字符串操作的类型：
    char(character),varchar(varchar2),nchar,nvarchar2,long,long raw及LOB类型

一、基本概念：
1、字符存储单位：
       一般一个汉字是两个字节，一个字母或标点符号为一个字节。
       8个位为一个字节，一个字母或标点符号为8个位，一个汉字是16个位。
      
       存储字母字符的长度。
2、char:定长的字符串类型。当存放的数据没有达到设定的最大限度时，会以空格补齐。
    
   informix:
     当存放的数据超过了设定的最大限度时，会自动截断，不会报错。
     create table char_len (a char(3),b char(4));
     
     insert into char_len(a,b) values('1234','123');
     
     select * from char_len;
     
     #a   b
     #
     #123 123
     
     insert into char_len(a,b) values('亿阳','亿阳');
     
select * from char_len;

#a   b
#
#123 123
#亿?[C亿阳
     
     
   oracle:
       当存放的数据超过了设定的最大限度时，会报错。
create table char_len (a char(3),b char(4));

insert into char_len(a,b) values('1234','123');

#ERROR at line 1:
#ORA-12899: value too large for column "NIOSDB"."CHAR_LEN"."A" (actual: 4,
#maximum: 3)

insert into char_len(a,b) values('123','123');

#A   B
#--- ----
#123 123

3、varchar：描述变长字符串。
informix:
  当存放的数据超过了设定的最大限度时，会自动截断，不会报错。   

oracle:
    当存放的数据超过了设定的最大限度时，会报错。

 

二、INFORMIX ORACLES数据类型长度比较

                   informix                       Oracle
                     范围     默认长度     作为数据存储     变量
char               1~32767      1          1~2000         1~32767
varchar            1~255        1          1~4000         1~32767
nchar              1~32767      1          1~2000         1~32767
nvarchar/nvarchar2 1~255        1          1~4000         1~32767
lvarchar/long      1~32739     2048        1~2GB          1~32760
text/CLOB          1~2千兆字节             1~4GB          1~32760

三、字符比较：
1、char和varchar
    区别：
　　      CHAR的长度是固定的，而VARCHAR的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (20)，表示你存储的
      字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储
      的字符小于20时，按实际长度存储。
          CHAR的效率比VARCHAR2的效率稍高。

　　何时该用CHAR，何时该用varchar2:
　　    VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在
    数据库设计上常说的‘以空间换效率’。
    char是用空间换时间，而varchar2是用时间换空间，char类型虽然一定会填满设定的空间，但由于长度和顺序规则所以
    速度会比较快，varchar2则是以降低速度为代价节省空间。
    VARCHAR2虽然比CHAR节省空间，但是如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行
    迁移’(Row Migration)现象，降低数据库的速度，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情
    况下用CHAR代替VARCHAR会更好一些。

2、char和nchar，varchar和nvarchar
       NCHAR、NVARCHAR2，国家字符集，与环境变量NLS指定的语言集密切相关，使用方法和CHAR、VARCHAR相同。
       CHAR如果存放字母数字占1个字节，存放GBK编码的汉字存放2个字节，存放UTF-8编码的汉字占用3个字节；
       NCHAR根据所选字符集来定义存放字符的占用字节数，一般都为2个字节存放一个字符(不管字符或者汉字)。
       例如：char中一个简单字符（如：a）占一个字节，存放GBK编码的汉字存放2个字节，存放UTF-8编
   码的汉字占用3个字节，而nchar中根据所选字符集来定义存放字符的占用字节数，一般都为2个字节存放一
   个字符（无论是简单字符或是汉字），后者专门针对如：中文，韩文这样的字符。

oracle里字符类型比较:

1、VARCHAR VARCHAR2比较
    Oracle里目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后
这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存
储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

2、char(20),varchar(20),varchar2(20),nchar(20),nvarchar2(20)测试。
  
   首先，提供一个测量数据长度的函数DUMP()，此函数的输出格式如下：
         类型 <[长度]>，符号/指数位 [数字1，数字2，数字3，......，数字20]
 
  类型代码是oracle中对每种类型定义的ID名，类型代码可以在oracle文档中找到，图"oracle常用数据类型代码"
  中列出了几种常见的类型代码。冒号后面的部分叫"符号/指数位"。
 
   我们借用此函数来确定上述几种字符串类型的长度和区别，另外大对象操作的数据类型(比如：long)无法使用DUMP()函数。
   
    1、建立张测试表名为typetest，在其中设定5种类型的数据分别为，
    char(20),varchar(20),varchar2(20),nchar(20),nvarchar2(20).
    2、在5个字段中都插入字符'a'
    3、用DUMP函数查询出结果

  
   create table typetest(
   cha char(20),
   var1 varchar(20),
   var2 varchar2(20),
   ncha nchar(20),
   nvar nvarchar2(20)
   );
  
   insert into typetest values('a','a','a','a','a');
  
   select
   dump(cha) "char(20)",
   dump(var1) "varchar(20)",
   dump(var2) "varchar2(20)",
   dump(ncha) "nchar(20)",
   dump(nvar) "nvarchar(20)"
   from typetest;
  
  
   结果显示char和nchar的类型代码为96，其他3个代码为1，
  
   char(20):
   Typ=96 Len=20: 97,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
   char的长度为20，说明'a'占住第一个字节，省下19个字节则用空格补齐。
  
   nchar(20):
   Typ=96 Len=40: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
   nchar也是用空格补齐剩下的字节，之所以长度是40，是因为在建表的时候nchar(20)括号中的20是指20个字符，在nchar非特殊情况
   下每个字符都占了2字节，所以上限为40字节。
  
   varchar(20):
   Typ=1 Len=1: 97
  
   varchar2(20):
   Typ=1 Len=1: 97
   varchar与varchar2的结果完全一样，说明2种类型现在还没有什么区别，并且长度为1，说明只有'a'所占的一个字节的空间。
  
   nvarchar(20):
   Typ=1 Len=2: 0,97
   nvarchar2则同varchar2一样为可变长，并且由于也是一个字符占2字节，所以a所占的长度为'2'

 

四、oracle里的数据类型:
1、char,character,varchar,varchar2,nchar,nvarchar2的共同特点：
当插入空字符串时，则都会被转变为null

1、long:
    LONG 数据类型是一个遗留下来的而且在将来不会被支持的数据类型。它将被LOB(Large Object)数据类型所代替。特点如下：

    1、LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。
　　2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。
　　3、LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。
　　4、很多工具，包括SQL*Plus，处理LONG 数据类型都是很困难的。
　　5、LONG 数据类型的使用中，要受限于磁盘的大小。
　　能够操作 LONG 的 SQL 语句：
　　1、Select语句
　　2、Update语句中的SET语句
　　3、Insert语句中的VALUES语句
　　限制：
　　1、一个表中只能包含一个 LONG 类型的列。
　　2、不能索引LONG类型列。
　　3、不能将含有LONG类型列的表作聚簇。
　　4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。
　　5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。
　　6、不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。
　　7、LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。
　　8、LONG类型列不能用于分布查询。

3、long raw 无可变长二进制数据，最大长度是2GB。Oracle用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft
   Word文档，以及音频、视频等非文本文件。在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，
   LOB数据类型即能存储结构化数据外，也能存储非结构化数据，最大长度为4GB，比long和long raw更加灵活，
   Oracle公司建议，对大对象的操作尽量使用LOB，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

2、LOB:Large Object的简写，
    是专门用于处理大对象的数据类型，一个表中可以有多个LOB字段，每个字段可以存储达4GB的数据，LOB主要分为以下两类
    1、BLOB(Binary Large Object):适合于存放程序、图片、影音等非结构化文件。
    2、CLOB(Character Large Object):适用于存储文本型数据，例如文章、新闻、记录、日志等。

五、informix里的字符:

1、lvarchar在9.4版本之后才可以用带指定长度的方式，如lvarchar(3000)，不带长度则默认是2048字节，最大长度是32,739字节。
   所以对于9.4之前的版本，不能用带长度的表达式，数据库会报语法错误。
   缺省情况下，数据库服务器把加引号的字符串，认为lvarchar类型。

2、text与oracle中的long类型一样，用来应对超长字符串的类型，最大长度为2GB
   可以存储、检索、更新、删除text列中的值，但是不能在算术或字符串中使用text操作数，也不能使用update语句的set自居将
   文字指定给text,不可以用以下任何方法使用text值：
   聚集函数、in子句、matches或like子句，group by组合一、order by子句。
   不能使用带引号的文字字符串、数字或其它实际值来插入或更新TEXT列。
  
   仅当正在使用is null或is not null运算符测试null时，可以在布尔表达式中使用test操作数，可以用下列方式将数据插入到text：
   dbload,onload实用程序，load（dbaccess），ESQL从TEXT主变量

六、oracle和informix表的限制
1、在informix的一个表中对以上字符串类型定义的总长度不能超过32767(不包括对大字段处理的text类型)
   但是遇到varchar与nvarchar会再减1(为该列设置索引会留一位)，而遇到lvarchar则需要减3。
   create table test (a char(32767))---success
  
   create table test (a char(32766),b varchar(1))---error
   create table test (a char(32765),b varchar(1))---success
  
   create table test (a char(32764),b lvarchar(1))---error
   create table test (a char(32763),b lvarchar(1))---success

   每个表的最大列数 32767
   每个索引的最大列数 16
   键值的最大长度是 390 字节。构成键的 VARCHAR 列的组合大小必须小于 390 减去每个 VARCHAR 列的额外字节。
   例如：数据库服务器构建用于以下语句的索引键长度等于 390 或 ((255+1) + (133+1))

2、oracle限制的每个表的列的总数不能超过1000(此处没有测试),然后每个列的长度由其数据类型决定。
每个索引的最大列数 32
键值的最大长度 749

七、行迁移与行链接

    当一行的数据过长而不能插入一个单个数据块中时，可能发生两种事情：行链接(row chaining)或行迁移(row migration)。

    1、行链接：
    当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。在这种情况下，oracle会使用与该块链接
    的一块或多块数据块来容纳该行的数据。行连接经常在插入比较大的行时才会发生，如包含long, long row, lob等类型的
    数据。在这些情况下行链接是不可避免的。

    2、行迁移
    当修改不是行链接的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳
    该行的数据时，就会发生行迁移。在这种情况下，Oracle会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只
    放一个指针，指向该行的新的位置，并且该行原先空间的剩余空间不再被数据库使用，这些剩余的空间我们将其称之为空洞，
    这就是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是我们可以将其降到一个我们可以接受的程度。

注意，即使发生了行迁移，发生了行迁移的行的rowid 还是不会变化，这也是行迁移会引起数据库I/O性能降低的原因。其实行迁
移是行链接的一种特殊形式，但是它的起因与行为跟行链接有很大不同，所以一般把它从行链接中独立出来，单独进行处理。

 

八、ORACLE－在设计数据库时如何选择正确的数据类型
1、Char
定长格式字符串，在数据库中存储时不足位数填补空格，不建议使用，会带来不必要的麻烦
a、字符串比较的时候，如果不注意（char不足位补空格）会带来错误
b、字符串比较的时候，如果用trim函数，这样该字段上的索引就失效（有时候会带来严重性能问题）
c、浪费存储空间

2、Varchar2/Varchar
不定长格式字符串，对于4000字节以内的字符串，建议都用该类型
a、网上有说char比varchar2性能好，但是如果你有兴趣做测试的话，会发现没有区别（如果发生行迁移，可以通过pctfree来调整）
b、充分利用存储空间

3、Long/long raw
Oracle已经废弃，只是为了向下兼容保留着，应该全部升级到lob
Long类型有很多限制
a、表中只能有一列long类型
b、Long类型不支持分布式事务
c、太多的查询不能在long上使用了

informix字符串函数:
1、lower,将字符串中每个大写字母转换为小写字母

SELECT manu_code, LOWER(manu_code)
FROM items
WHERE order_num = 1018

manu_code (expression)
PRC prc
KAR kar
PRC prc
SMT smt
HRO hro

2、upper，将字符串中每个小写字母转换为大写字母

select a,upper(a) from test;

a                       (expression)

aBcDe                   ABCDE
aBcDe                   ABCDE

3、initcap，将字符串中每个词的首写字母转换成大写
select a,initcap(a) from test;

a                       (expression)

3bcde                   3Bcde
3bcde dbv adf 45df      3Bcde Dbv Adf 45Df

4、replace，将字符串中的某一组字符转换成其他字符，例replace(col,”each”,”eve”)

select a,replace(a,'3b','4b') from test;

a                       (expression)

3bcde                   4bcde
3bcde dbv adf 45df      4bcde dbv adf 45df

5 substr，substring
返回字符串中的某一部分，指定开始位置和长度，例substr(col,1,2) substring(col,from 1to 4)

还有一种写法是将字段名后面加上方括号，里面是起始到结束的位置，例：col[1,2]

select a,
substr(a,2,3) as  substr       ,
a[2,3]  as a2     ,
substring(a from 2 for 3)  as substring
from test;

a                       substr a2 substring

3bcde                   bcd    bc bcd
3bcde dbv adf 45df      bcd    bc bcd

注意：substr，substring如果没有长度，默认到最后一个字符：
      字段名后面加上方括号，如果没有结束位置，就认为只取起始位置的字符

select a,
substr(a,2) as  substr       ,
a[2]  as a2     ,
substring(a from 2)  as substring
from test;

a                       substr                  a2 substring

3bcde                   bcde                    b  bcde
3bcde dbv adf 45df      bcde dbv adf 45df       b  bcde dbv adf 45df

注意：substr，substring的起始位置为负值，表示从字符串末尾向后数，
      substring的起始位置为负值，表示从字符串开始位置向后几位。
      字段名后面加上方括号，里的起始、结束位置都不能用负值

select a,
substr(a,-2,5 ) as  substr ,
substring(a from -2 for 5)  as substring
from test;

a                       substr substring

3bcde                   de     3b
3bcde dbv adf 45df      df     3b

6、lpad，使用lpad函数已用重复次数达到必要次数的字符序列在左边填充或截断的字符串的副本，这取决于字符串中填充
部分的指定长度。

select a,lpad(a,21,'_')
from test;

a                       lpad(a,21,'_')

3bcde                   ________________3bcde
3bcde dbv adf 45df      ___3bcde dbv adf 45df

7、Rpad，使用rpad函数已用重复次数达到必要次数的字符序列在右边填充或截断的字符串的副本，这取决于字符串中填充部
分的指定长度。

select a,rpad(a,21,'_')
from test;

a                       rpad(a,21,'_')

3bcde                   3bcde________________
3bcde dbv adf 45df      3bcde dbv adf 45df___

 

8、LENGTH

select a,length(a)
from test;

a                       length(a)

3bcde                   5
3bcde dbv adf 45df      18

oracle字符串函数：
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32

2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(’smith’) upp from dual;
UPP
-----
Smith

5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的次数,默认为1
SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual;
INSTRING
---------
9

6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7

7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower(’AaBbCcDd’)AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd

8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper(’AaBbCcDd’) upper from dual;
UPPER
--------
AABBCCDD

9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad(’gao’,10,’*’),17,’*’)from dual;
LPAD(RPAD(’GAO’,1
-----------------
*******gao*******
不够字符则用*来填满

10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual;
LTRIM(RTRIM(’
-------------
gao qian jing

11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(’13088888888’,3,8) from dual;
SUBSTR(’
--------
08888888

12.REPLACE(’string’,’s1’,’s2’)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace(’he love you’,’he’,’i’) from dual;
REPLACE(’H
----------
i love you

13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values(’weather’);
SQL> insert into table1 values(’wether’);
SQL> insert into table1 values(’gao’);
SQL> select xm from table1 where soundex(xm)=soundex(’weather’);
XM
--------
weather
wether

14.TRIM(’s’ from ’string’)
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为去掉空格符