概述

很多朋友对Oracle数据库中的一些类型会很容易混淆，其中最容易混淆的应该就是oracle数据库int、number和char、varchar、varchar2类型了，下面针对这两个做一下区分。

一、oracle中int与number的区别

oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。int类型只能存储整数;number可以存储浮点数，也可以存储整数；

number(8,1)存储小数位为1位，总长度为8的浮点数，如果小数位数不足，则用0补全；

number(8)存储总长度为8的整数；

int相当于number(22),存储总长度为22的整数。

实例：

1、环境模拟

--创建表结构create table tab(id0 int,id1 number,id2 number(8,1),id3 number(8));--插入测试数据insert into tab select 1,1.5,1.6,8 from dual;insert into tab select 1,1.55,1.6,8 from dual;insert into tab select 1,1.595,1,8 from dual;commit;select * from tab;

2、查询数据字典表dba_tab_columns

select table_name,column_name,data_type,data_length,data_precision,data_scale from dba_tab_columns a where table_name='TAB' and owner='GLOGOWNER' order by column_id;

说明：

• Data_type表示字段类型；
• Data_length表示字段类型的长度；
• Data_Precision表示字段类型的精度的总长度，如果为null,表示精度的总长度不固定，最长为Data_Length；
• Data_scale表示字段类型的精度范围，如果为0,表示只能存储为整数，如果为null,表示可以存储整数或者浮点数，浮点数位数不确定，如果为整数，表示存储的精度位数。

查询dba_tab_columns表，发现tab表中ID0字段类型int已经被转换为number(22)。

二、oracle中char、varchar、varchar2的区别

VARCHAR2是Oracle提供的特定数据类型，Oracle可以保证VARCHAR2在任何版本中该数据类型都可以向上和向下兼容。oracle中建议使用varchar2，VARCHAR不建议使用。

1.char占用固定的字节控件，例如char(20)，abc在该字段中存储为abc+17个空格；

2.varchar是标准sql的写法，可以存储空字符串；

3.varchar2是oracle特有的字段，varchar2(20)表示该字段最大长度为20，实际存储的是数据的长度；

具体到NVARCHAR2和VARCHAR2的区别，从使用角度来看区别在于：

NVARCHAR2在计算长度时和字符集相关的，例如数据库是中文字符集时以长度10为例，则

1、NVARCHAR2(10)是可以存进去10个汉字的，如果用来存英文也只能存10个字符。

2、而VARCHAR2(10)的话，则只能存进5个汉字，英文则可以存10个。

NVARCHAR2 DatatypeThe NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.The maximum length of the column is determined by the national character set definition. Width specifications of characterdatatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.VARCHAR2 DatatypeThe VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to givethe maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and thatyou use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consistsof well-formed strings. See Oracle Database Globalization Support Guide for more information on character set support.VARCHAR DatatypeDo not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length characterstrings compared with different comparison semantics.

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~