openGauss可替代Oracle吗?从字段类型说起……

洪烨 2020-09-27 14:25:12

作者介绍

洪烨,openGauss Contributor,多年银行业系统架构设计及DBA实战经验,《DB2数据库内部解析与性能调优》作者。

 

背景

 

随着疫情及国际局势的不确定性增加,数据库作为系统运行中的关键组件,对于国产化数据库替换Oracle的呼声也越发高涨。国产数据库中GaussDB一直备受关注,去年华为宣布放弃私有云和数据库也引发了诸多争议;但另一方面,华为云在6月30日将原GaussDB 100开源为openGauss,采用木兰开源协议,并与华为公有云GaussDB for openGauss采取共同迭代,为国内开源数据库市场带来了新的血液。

 

正文

 

作为GaussDB 100的延伸,openGauss是否可以在OLTP场景替代Oracle?从此篇开始,笔者会按照字段类型、表及索引类型、SQL语法、函数、PLSQL、连接方式等方面逐一进行兼容性评估与相关阐述,可作为替换Oracle的借鉴与参考。

 

Oracle数据库的字段总共包含6类:字符、数字、日期、大对象、ROWID、RAW。本次验证环境为centos 7.6,基于openGauss 1.0.0版本对Oracle字段类型进行验证。

 

 
字符

 

Oracle中包含6种字符类型:char、nchar、varchar、varchar2、nvarchar2、long。其中char、varchar为ANSI编码,用于单字节来存储数据,适合英文,中文会不兼容,nchar及nvarchar2采用UNICODE编码,用两个字节来存储,适合存放汉字;char,nchar为定长字段;varchar、varchar2、nvarchar2、LONG为可变长字段,LONG最大长度限制为2GB,用于不需要作字符串搜索的长串数据,是一个遗留下来的而且将来不会被支持的数据类型,逐渐被BLOB,CLOB,NCLOB等大的数据类型所取代。varchar是SQL标准里面规范的类型,varchar2是Oracle提供的独有的数据类型,主要区别在于:

 

  • varchar对于汉字占两个字节,对于数字,英文等是一个字节;varchar2对数字、英文、汉字都占两个字节;

  • varchar对空串不处理,varchar2将空串当做null来处理;

  • openGauss除了LONG类型外,对Oracle字符类型全部可以兼容,LONG类型可用CLOB替代(见大对象兼容性验证)。验证结果如下:

 

 
  • CHAR

postgres=# create table t_char(col CHAR(20));

CREATE TABLE

 

  • NCHAR

postgres=# create table t_nchar(col NCHAR(20));

CREATE TABLE

 

  • VARCHAR

postgres=# create table t_varchar(col VARCHAR(20));

CREATE TABLE

 

  • VARCHAR2

postgres=# create table t_varchar2(col VARCHAR2(20));

CREATE TABLE

 

  • NVARCHAR2

postgres=# create table t_nvarchar2(col NVARCHAR2(20));

CREATE TABLE

 

  • LONG

postgres=# create table t_long(col LONG);

ERROR:  type "long" does not exist

LINE 1: create table t_long(col LONG);

 

 
数字

 

Oracle包含5种数字类型:INT、NUMBER、FLOAT、BINARY_FLOAT、BINARY_DOUBLE。INT为整型,NUMBER为双精度浮点数,精度可以高达38位,它有两个限定符,如:column NUMBER(precision,scale)。precision表示数字中的有效位。如果没有指定precision的话,Oracle将使用38作为精度。scale表示小数点右边的位数,scale默认设置为0。如果把scale设成负数,Oracle将把该数字取舍到小数点左边的指定位数。Oracle只是在语法上支持decimal类型,但是在底层实际上它就是number类型,支持decimal类型是为了能把数据从Oracle数据库移到其他数据库中。

 

BINARY_FLOAT与BINARY_DOUBLE采用二进制精度,而NUMBER采用十进制精度,采用二进制精度能够减少占用的存储空间,提供更快的数学运算速度。BINARY_FLOAT是32位的单精度浮点数字数据类型。每个值需要5字节存储空间,其中1字节用于存储数据值的长度。BINARY_DOUBLE是64位的双精度浮点数字数据类型。每个值需要9字节存储空间,其中1字节用于存储数据值的长度。

 

除BINARY_FLOAT外,openGauss兼容Oracle其他数字类型,BINARY_FLOAT 可以用BINARY_DOUBLE替代。验证结果如下:

 

 
  • int

postgres=# create table t_int(col int);

CREATE TABLE

 

  • number

postgres=# create table t_number(col NUMBER);

CREATE TABLE

 

  • float

postgres=# create table t_float(col float);

CREATE TABLE

 

  • BINARY_FLOAT

postgres=# create table t_bfloat(col BINARY_FLOAT);

ERROR:  type "binary_float" does not exist

LINE 1: create table t_bfloat(col BINARY_FLOAT);

 

  • BINARY_DOUBLE

postgres=# create table t_bdouble(col BINARY_DOUBLE);

CREATE TABLE

 

 
时间&日期

 

Oracle包含6种时间及日期类型:DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND。

 

DATE是Oracle最常用的日期类型,常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日。DATE类型在数据库中的存储固定为7个字节, 第1字节:世纪+100、 第2字节:年、第3字节:月、第4字节:天、第5字节:小时+1、第6字节:分+1、第7字节:秒+1。

 

TIMESTAMP与DATE的区别是不仅可以保存日期和时间,还能保存小数秒,小数位数可以指定为0-9,默认为6位,所以最高精度 可以到ns(纳秒),数据库内部用7或者11个字节存储,如果精度为0,则用7字节存储,与date类型功能相同,如果精度大于0则用11字节存储。第1字节:世纪+100、第2字节:年、第3字节:月、第4字节:天、 第5字节:小时+1、第6字节:分+1、第7字节:秒+1、 第8-11字节:纳秒,采用4个字节存储,内部运算类型为整型。

 

TIMESTAMP WITH TIME ZONE对TIMESTAMP进行了扩展,用于存储时区。时间戳以及时区位移值,其中fractional_seconds_precision是数字在第二日期时间字段的小数部分数字的所有值。可接受的值是0到9。默认是6。默认格式是确定明确的NLS_DATE_FORMAT参数或隐式的NLS_TERRITORY参数。大小固定为13字节。此数据类型包含日期时间字段YEAR,MONTH,日,小时,分钟,秒TIMEZONE_HOUR和TIMEZONE_MINUTE。它有一个明确的分数秒和时区。

 

TIMESTAMP WITH LOCAL TIME ZONE在用户提交时间给数据库时,该类型会转换成数据库的时区来保存数据。默认格式是确定明确的NLS_DATE_FORMAT参数或隐式的NLS_TERRITORY参数。的大小不同的7至11个字节,取决于精度。

 

INTERVAL YEAR TO MONTH存储期间年数和月的时间,其中year_precision是数字的年份日期时间字段的数量。可接受的值是0到9。默认是2。大小固定为5个字节。

 

Oracle语法:INTERVAL YEAR [(year_precision )] TO MONTH,用来表示一段时间差, 只精确到年和月. year_precision是数字年的时间段,接受的值为0到9。默认值是2。大小固定为5个字节。

 

INTERVAL DAY TO SECOND格式为:INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)],是存储一段时间以天,小时,分钟和秒,其中day_precision是数字在DAY日期时间字段的最大数量。可接受的值是0到9。默认是2。fractional_seconds_precision是数字中的第二个字段的小数部分的数量。可接受的值是0到9。默认是6。大小固定为11个字节。

 

除TIMESTAMP WITH LOCAL TIME ZONE外,openGauss兼容其他日期及时间类型,TIMESTAMP WITH LOCAL TIME ZONE可用TIMESTAMP WITH TIME ZONE替代,验证结果如下:

 

 
  • DATE

postgres=# create table t_date(col DATE);

CREATE TABLE

 

  • TIMESTAMP

postgres=# create table t_timestamp(col TIMESTAMP);

CREATE TABLE

 

  • TIMESTAMP WITH TIME ZONE

postgres=# create table t_timezone(col TIMESTAMP WITH TIME ZONE);

CREATE TABLE

 

  • TIMESTAMP WITH LOCAL TIME ZONE

postgres=# create table t_localtimezone(col TIMESTAMP WITH LOCAL TIME ZONE);

ERROR:  syntax error at or near "WITH LOCAL"

LINE 1: create table t_localtimezone(col TIMESTAMP WITH LOCAL TIME Z...

 

  • INTERVAL YEAR TO MONTH

postgres=# create table t_interval_ym(col INTERVAL YEAR TO MONTH);

CREATE TABLE

 

  • INTERVAL DAY TO SECOND

postgres=# create table t_interval_ds(col INTERVAL DAY TO SECOND);

CREATE TABLE

 

 
大对象

 

Oracle包含4种大对象类型BLOB、CLOB、NCLOB、BFILE,存储长度都为4G。CLOB与NCLOB适用于存储超长文本,CLOB主要存储单字节字符型数据,NCLOB存储多字节国家字符型数据。BLOB存储二进制数据。适用于存储图像、视频、音频等。BFILE在数据库外部保存的大型二进制对象文件,这种外部的LOB类型,通过数据库记录变化情况,但是数据的具体保存是在数据库外部进行的,Oracle可以读取、查询BFILE,但是不能写入,不参与事务。

 

openGasus可以兼容BLOB及CLOB类型,无法兼容NCLOB及BIFLE类型,NCLOB和BFILE可用CLOB及BLOB替代,验证结果如下:

 

 
  • BLOB

postgres=# create table t_blob(col BLOB);

CREATE TABLE

 

  • CLOB

postgres=# create table t_clob(col CLOB);

CREATE TABLE

 

  • NCLOB

postgres=# create table t_nclob(col NCLOB);

ERROR:  type "nclob" does not exist

LINE 1: create table t_nclob(col NCLOB);

 

  •  BFILE

postgres=# create table t_bfile(col BFILE);

ERROR:  type "bfile" does not exist

LINE 1: create table t_bfile(col BFILE);

 

 
ROWID

 

ROWID类型及UROWID类型为Oracle特有类型,ROWID为该表行的唯一标识,是一个伪列。在表中创建ROWID数据类型的情况下,Oracle不保证该值是合法的ROWID,用户必须确保该ROWID值是真实合法的,UROWID支持逻辑和物理的ROWID。

 

openGauss无法兼容ROWID,由于ROWID字段Oracle并无验证合法机制,实际使用中可用char(20)替代,ROWID兼容性的验证结果如下:

 

 
  • ROWID

postgres=# create table t_rowid(col ROWID);

ERROR:  type "rowid" does not exist

LINE 1: create table t_rowid(col ROWID);

 

  • UROWID

postgres=# create table t_urowid(col UROWID);

ERROR:  type "urowid" does not exist

LINE 1: create table t_urowid(col UROWID);

 

 
RAW

 

RAW与LONG RAW中存储的为可变长二进制数据,是一种较老的数据类型,用这种格式来保存的图形文件或带格式的文本文件,如Miceosoft Word文档。RAW支持的最大长度为2000,LONG RAW最大长度是2GB。将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

 

openGauss可支持RAW,但不支持LONG RAW,遇到LONG RAW场景可用BLOB替代,验证结果如下:

 

postgres=# create table t_raw(col RAW(10));

CREATE TABLE

 

postgres=# create table t_longraw(col LONG RAW);

ERROR:  syntax error at or near "RAW"

LINE 1: create table t_longraw(col LONG RAW);

 

总结

 

总体而言,openGauss可全面兼容Oracle所有数据类型,对于常见数据类型无需进行改造,对于少数非常用数据类型,需要进行少量代码改造,可采取下列替代方案进行替换。

 

分类

字段类型

是否兼容

替代方案

字符

CHAR

 

NCHAR

 

VARCHAR

 

VARCHAR2

 

NVARCHAR2

 

LONG

×

CLOB

数字

INT

 

FLOAT

 

NUMBER

 

BINARY_FLOAT

×

BINARY_DOUBLE

BINARY_DOUBLE

 

时间&日期

DATE

 

TIMESTAMP

 

TIMESTAMP WITH TIME ZONE

 

TIMESTAMP WITH LOCAL TIME ZONE

×

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR TO MONTH

 

INTERVAL DAY TO SECOND

 

大对象

BLOB

 

CLOB

 

NCLOB

×

CLOB

BFILE

×

BLOB

ROWID

ROWID

×

CHAR(20)

UROWID

×

CHAR(40)

RAW

RAW

 

LONG RAW

×

BLOB

 
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告