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

数据类型和语法转换

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

本文为您介绍不同源端数据库和 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、UROWID、UNDEFINED 和 UDT 类型的数据。

Oracle 数据类型	OceanBase Oracle 数据类型
CHAR	CHAR
NCHAR	NCHAR
VARCHAR2	VARCHAR2
NVARCHAR2	NVARCHAR2
NUMBER	NUMBER
NUMBER (p, s)	NUMBER(p,s)
RAW	RAW
CLOB	CLOB
NCLOB	OceanBase 数据库 Oracle 租户 2.2.50 以下版本不支持转换 OceanBase 数据库 Oracle 租户 2.2.50 及以上版本支持转换为 NVARCHAR2
BLOB	BLOB
FLOAT(n)	OceanBase 数据库 Oracle 租户 2.2.30 以下版本：NUMBER(n*0.30103) OceanBase 数据库 Oracle 租户 2.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
LONG	支持结构迁移、全量迁移和全量校验
LONG RAW	支持结构迁移、全量迁移和全量校验

Oracle 表分区转换

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

说明：

本文的分区转换规则适用于所有分区类型。

原表定义	OceanBase 数据库 2.2.30 版本转换输出	OceanBase 数据库 2.2.50 及以上版本转换输出
`CREATE TABLE T_RANGE_0 (` `A INT,` `B INT,` `PRIMARY KEY (B)` `)PARTITION BY RANGE(A)(` `....` `);`	`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);` 主键列未包含分区列分区列是物理列加入至主键列，组成联合主键原始的主键列添加全局唯一索引	`CREATE TABLE "T_RANGE_0" (` `"A" NUMBER,` `"B" NUMBER NOT NULL,` `CONSTRAINT "T_RANGE_10_UK" UNIQUE ("B")` `)PARTITION BY RANGE ("A")(` `....` `);`
`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)(` `....` `);`	`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)(` `....` `);`	`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)(` `....` `);`
`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"	支持原表定义
`CREATE TABLE T_RANGE_2 (` `A INT,` `B INT NOT NULL,` `UNIQUE (B)` `)PARTITION BY RANGE(A)(` `partition P_MAX values less than (10)` `);`	`CREATE TABLE "T_RANGE_2" (` `"A" NUMBER,` `"B" NUMBER NOT NULL,` `PRIMARY KEY ("B", "A")` `)PARTITION BY RANGE ("A")(` `....` `);`	支持原表定义
`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"	支持原表定义
`CREATE TABLE T_RANGE_4 (` `A INT NOT NULL,` `B INT,` `UNIQUE (A)` `)PARTITION BY RANGE(A)(` `....` `);`	`CREATE TABLE "T_RANGE_4" (` `"A" NUMBER NOT NULL,` `"B" NUMBER,` `PRIMARY KEY ("A")` `)PARTITION BY RANGE ("A")(` `....` `);`	`CREATE TABLE "T_RANGE_4" (` `"A" NUMBER NOT NULL,` `"B" NUMBER,` `PRIMARY KEY ("A")` `)PARTITION BY RANGE ("A")(` `....` `);`
`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"	支持原表定义
`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"	支持原表定义
`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)` `);`	`CREATE TABLE "T_RANGE_7" (` `"A" NUMBER NOT NULL,` `"B" NUMBER NOT NULL,` `PRIMARY KEY ("A", "B")` `)PARTITION BY RANGE ("A")(` `....` `);`	`CREATE TABLE "T_RANGE_7" (` `"A" NUMBER NOT NULL,` `"B" NUMBER NOT NULL,` `PRIMARY KEY ("A", "B")` `)PARTITION BY RANGE ("A")(` `....` `);`
`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)` `);`	`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")(` `....` `);`	支持原表定义
`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)` `);`	`CREATE TABLE "T_RANGE_9" (` `"A" NUMBER,` `"B" NUMBER,` `"C" NUMBER NOT NULL,` `PRIMARY KEY ("C"),` `UNIQUE ("A"),` `UNIQUE ("B")` `)PARTITION BY RANGE ("C")(` `....` `);`	`CREATE TABLE "T_RANGE_9" (` `"A" NUMBER,` `"B" NUMBER,` `"C" NUMBER NOT NULL,` `PRIMARY KEY ("C"),` `UNIQUE ("A"),` `UNIQUE ("B")` `)PARTITION BY RANGE ("C")(` `....` `);`