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

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

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

# 数据类型和语法转换

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

 上一篇 下一篇 ![有帮助](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) 咨询热线