---
title: "数据类型和语法转换 | OceanBase 文档中心"
description: 数据类型和语法转换 本文为您介绍不同源端数据库和 OceanBase 数据库之间数据类型和语法的映射关系，以便您在迁移数据库前，了解迁移操作对业务带来的影响。 背景信息 在 OceanBase 迁移服务（OceanBase Migration Service，OMS）迁移任务中，通常源端数据库和目标端数据库的类型不一…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*L03BS6f-o40AAAAAAAAAAAAADiGDAQ/original) 迁移服务 OMSV 3.1.0 企业版

# 数据类型和语法转换

更新时间：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")(`   `....`   `);` |

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线