数据类型和语法转换 | OceanBase 文档中心

数据类型和语法转换

更新时间：2023-06-08 10:49:05

本文为您介绍不同源端数据库和 OceanBase 数据库之间数据类型和语法的映射关系，以便您在迁移数据库前，了解迁移操作对业务带来的影响。

背景信息

在 OceanBase 迁移服务（OceanBase Migration Service，OMS）迁移任务中，通常源端数据库和目标端数据库的类型不一致。对于目标端不支持的数据类型或语法，OMS 会自动转换数据类型和语法。

Oracle 数据类型转换

通常 Oracle 数据库迁移至 OceanBase Oracle 时，OMS 支持大部分数据类型的数据迁移和增量同步。如果 OceanBase 数据库的版本早于 V2.2.30，OMS 会在数据迁移时，将其转换为 FLOAT(n)NUMBER(n*0.30103)，V2.2.30 之后的版本支持 FLOAT 类型的数据原样迁移。注意

CLOB 和 BLOB 类型的数据必须小于 48 MB。
不支持迁移 LONG、ROWID、BFILE、LONG RAW、XMLType 和 UDT 类型的数据。

Oracle 数据类型	OceanBase Oracle 数据类型
CHAR	CHAR
NCHAR	NCHAR
VARCHAR2	VARCHAR2
NVARCHAR2	NVARCHAR2
NUMBER	NUMBER
NUMBER (p, s)	NUMBER(p,s)
LONG	支持全量迁移和校检，暂不支持增量同步
RAW	RAW
CLOB	CLOB
NCLOB	NCLOB
BLOB	BLOB
FLOAT(n)	* OceanBase V2.2.30 之前的版本：NUMBER(n0.30103) OceanBase V2.2.30 及之后的版本：FLOAT
BINARY_FLOAT	BINARY_FLOAT
BINARY_DOUBLE	BINARY_DOUBLE
DATE	DATE
TIMESTAMP	TIMESTAMP
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR(p) TO MONTH	INTERVAL YEAR(p) TO MONTH
INTERVAL DAY(p) TO SECOND	INTERVAL DAY(p) TO SECOND
ROWID	不支持
BFILE	不支持
LONG RAW	支持全量迁移和校检，暂不支持增量同步
XMLType	不支持
UDT	不支持

Oracle 表分区转换

OMS 迁移 Oracle 类型的数据库时，会对业务 SQL 用法进行适配转换，OceanBase V2.2.30 和 V2.2.50 版本对部分 SQL 适配转换方式不同。

原表定义	OceanBase 数据库 V2.2.30 转换输出	OceanBase 数据库 V2.2.50 及后续版本转换输出
`unknow CREATE TABLE T_RANGE_0 ( A INT, B INT, PRIMARY KEY (B) )PARTITION BY RANGE(A)( .... );`	`unknow CREATE TABLE "T_RANGE_0" ( "A" NUMBER, "B" NUMBER NOT NULL, PRIMARY KEY ("B", "A") )PARTITION BY RANGE ("A")( .... ); CREATE UNIQUE INDEX ON "T_RANGE_0"(B);` * 主键列未包含分区列 * 分区列是物理列 * 加入至主键列，组成联合主键 * 原始的主键列添加全局唯一索引	`unknow CREATE TABLE "T_RANGE_0" ( "A" NUMBER, "B" NUMBER NOT NULL, CONSTRAINT "T_RANGE_10_UK" UNIQUE ("B") )PARTITION BY RANGE ("A")( .... );`
`unknow CREATE TABLE T_RANGE_10 ( "A" INT, "B" INT, "C" DATE, "D" NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR("C",'dd'))) VIRTUAL, CONSTRAINT "T_RANGE_10_PK" PRIMARY KEY (A) )PARTITION BY RANGE(D)( .... );`	`unknow CREATE TABLE T_RANGE_10 ( "A" INT NOT NULL, "B" INT, "C" DATE, "D" NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR("C",'dd'))) VIRTUAL, CONSTRAINT "T_RANGE_10_PK" PRIMARY KEY (A, C) )PARTITION BY RANGE(D)( .... );`	`unknow CREATE TABLE T_RANGE_10 ( "A" INT NOT NULL, "B" INT, "C" DATE, "D" NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR("C",'dd'))) VIRTUAL, CONSTRAINT "T_RANGE_10_PK" UNIQUE (A) )PARTITION BY RANGE(D)( .... );`
`unknow CREATE TABLE T_RANGE_1 ( A INT, B INT, UNIQUE (B) )PARTITION BY RANGE(A)( partition P_MAX values less than (10) );`	-- [WARNING] Create global index on no primary key table is unsupported. Object: "GUYUE"."T_RANGE_1"	支持原表定义
`unknow CREATE TABLE T_RANGE_2 ( A INT, B INT NOT NULL, UNIQUE (B) )PARTITION BY RANGE(A)( partition P_MAX values less than (10) );`	`unknow CREATE TABLE "T_RANGE_2" ( "A" NUMBER, "B" NUMBER NOT NULL, PRIMARY KEY ("B", "A") )PARTITION BY RANGE ("A")( .... );`	支持原表定义
`unknow CREATE TABLE T_RANGE_3 ( A INT, B INT, UNIQUE (A) )PARTITION BY RANGE(A)( .... );`	-- [WARNING] Create global index on no primary key table is unsupported. Object: "GUYUE"."T_RANGE_2"	支持原表定义
`unknow CREATE TABLE T_RANGE_4 ( A INT NOT NULL, B INT, UNIQUE (A) )PARTITION BY RANGE(A)( .... );`	`unknow CREATE TABLE "T_RANGE_4" ( "A" NUMBER NOT NULL, "B" NUMBER, PRIMARY KEY ("A") )PARTITION BY RANGE ("A")( .... );`	`unknow CREATE TABLE "T_RANGE_4" ( "A" NUMBER NOT NULL, "B" NUMBER, PRIMARY KEY ("A") )PARTITION BY RANGE ("A")( .... );`
`unknow CREATE TABLE T_RANGE_5 ( A INT, B INT, UNIQUE (A, B) )PARTITION BY RANGE(A)( partition P_MAX values less than (10) );`	-- [WARNING] Create global index on no primary key table is unsupported. Object: "GUYUE"."T_RANGE_5"	支持原表定义
`unknow CREATE TABLE T_RANGE_6 ( A INT NOT NULL, B INT, UNIQUE (A, B) )PARTITION BY RANGE(A)( partition P_MAX values less than (10) );`	-- [WARNING] Create global index on no primary key table is unsupported. Object: "GUYUE"."T_RANGE_5"	支持原表定义
`unknow CREATE TABLE T_RANGE_7 ( A INT NOT NULL, B INT NOT NULL, UNIQUE (A, B) )PARTITION BY RANGE(A)( partition P_MAX values less than (10) );`	`unknow CREATE TABLE "T_RANGE_7" ( "A" NUMBER NOT NULL, "B" NUMBER NOT NULL, PRIMARY KEY ("A", "B") )PARTITION BY RANGE ("A")( .... );`	`unknow CREATE TABLE "T_RANGE_7" ( "A" NUMBER NOT NULL, "B" NUMBER NOT NULL, PRIMARY KEY ("A", "B") )PARTITION BY RANGE ("A")( .... );`
`unknow CREATE TABLE T_RANGE_8 ( "A" INT, "B" INT, "C" INT NOT NULL, UNIQUE (A), UNIQUE (B), UNIQUE (C) )PARTITION BY RANGE(B)( partition P_MAX values less than (10) );`	`unknow CREATE TABLE "T_RANGE_8" ( "A" NUMBER, "B" NUMBER, "C" NUMBER NOT NULL, PRIMARY KEY ("C", "B"), UNIQUE ("A"), UNIQUE ("B"), UNIQUE ("C") )PARTITION BY RANGE ("B")( .... );`	支持原表定义
`unknow CREATE TABLE T_RANGE_9 ( "A" INT, "B" INT, "C" INT NOT NULL, UNIQUE(A), UNIQUE(B), UNIQUE (C) )PARTITION BY RANGE(C)( partition P_MAX values less than (10) );`	`unknow CREATE TABLE "T_RANGE_9" ( "A" NUMBER, "B" NUMBER, "C" NUMBER NOT NULL, PRIMARY KEY ("C"), UNIQUE ("A"), UNIQUE ("B") )PARTITION BY RANGE ("C")( .... );`	`unknow CREATE TABLE "T_RANGE_9" ( "A" NUMBER, "B" NUMBER, "C" NUMBER NOT NULL, PRIMARY KEY ("C"), UNIQUE ("A"), UNIQUE ("B") )PARTITION BY RANGE ("C")( .... );`