search-icon search-icon

#元数据转换规则

本文档统计YMP对源端的DDL进行兼容性转换所涉及的转换规则。

# 元数据转换规则

编号 转换规则 影响对象 说明
1 手动修改 -- 用户在评估界面上手动修改或者批量修改DDL
2 移除not null约束 表 MySQL与DM的空字符串和null在YashanDB中都是null，迁移空字符串如果违反非空约束，迁移过程中删除非空约束
3 数据类型转换 表 YashanDB没有该数据类型，需要转换
4 自增列转序列 表 MySQL的auto_increment（或者Oracle与DM的自增列）转换成YashanDB的序列
5 on update current_timestamp转触发器 表 MySQL的on update current_timestamp转换成YashanDB的触发器
6 衍生对象 序列、触发器 自增列转序列，MySQL的on update current_timestamp语法转触发器
7 序列起始值修改 序列 YMP界面批量修改序列起始值
8 临时表转全局临时表 表 MySQL临时表转换成YashanDB的public TEMPORARY
9 移除版本注释 表 移除MySQL表DDL的版本注释，如/*!50500 */，可见样例
10 移除表的参数 表 移除表DDL中的YashanDB不支持的表参数语法
11 移除列的参数 表 移除表DDL中的YashanDB不支持的列参数语法
12 移除分区信息 表 移除分区表DDL中的YashanDB不支持的分区参数语法
13 移除子分区 表 仅支持YashanDB22.2版本，YashanDB22.2版本不支持分区表的子分区，DM无此规则
14 约束提取为单独对象 表 表DDL中的约束（非空约束、主键约束、唯一约束、检查约束，外键约束）提取出作为单独对象
15 注释提取为单独语句 表 表DDL中的注释提取出作为单独的DDL，DM无此规则
16 移除视图创建前缀信息 视图 MySQL的视图创建前缀信息[SQL SECURITY { DEFINER \
17 移除视图创建后缀信息 视图 MySQL的视图创建后缀信息[WITH [CASCADED \
18 EVENT转JOB EVENT MySQL的EVENT转换成YashanDB的JOB
19 移除用户信息 视图、触发器、存储过程、函数 MySQL的初始的DDL包含ALGORITHM=UNDEFINED DEFINER=root@%，需要移除
20 移除NOLOGGING 表、索引 YMP的创建任务界面中可设置是否忽略NOLOGGING参数，设置为true，将移除Oracle的表和索引中的NOLOGGING参数
21 移除索引参数 索引 Oracle与DM的索引的部分参数在Yashandb中不支持，需要移除进行兼容
22 移除序列参数 序列 Oracle的序列参数（NOKEEP、NOSCALE、GLOBAL），DM的序列参数（LOCAL）在YashanDB中不支持，需要移除进行兼容

# 全部示例

# 示例3

-- MySQL
CREATE TABLE `data_type_test` (
  `id` mediumint DEFAULT NULL,
  `id01` DECIMAL(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- YashanDB
CREATE TABLE "DATA_TYPE_TEST" (
	"ID" INT DEFAULT NULL,
	"ID01" NUMBER DEFAULT NULL
);
-- 注意：mediumint转换成INT类型，decimal(10,0)转换成NUMBER类型

# 示例4

-- MySQL
CREATE TABLE `auto_increment_tab` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- YashanDB
CREATE TABLE "AUTO_INCREMENT_TAB" (
	"ID" INT DEFAULT "AUTO_INCREMENT_TAB_ID_SEQ".NEXTVAL 
);
-- 注意：AUTO_INCREMENT 转换成了 "AUTO_INCREMENT_TAB_ID_SEQ".NEXTVAL

-- Oracle
CREATE TABLE "TEST"."TB_TEST" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
	"NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
  
-- YashanDB
CREATE TABLE "TEST"."TB_TEST" (
	"ID" NUMBER DEFAULT "TEST"."TB_TEST_ID_SEQ".NEXTVAL ,
	"NAME" VARCHAR(100)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
NOCOMPRESS
LOGGING
TABLESPACE "USERS";
-- 注意：GENERATED ALWAYS AS IDENTITY 转换成了 "TEST"."TB_TEST_ID_SEQ".NEXTVAL

# 示例5

-- MySQL
CREATE TABLE `on_update_test` (
  `t1` TIMESTAMP(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- YashanDB
CREATE TABLE "ON_UPDATE_TEST" (
	"T1" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP()
) ;
-- 注意：ON UPDATE CURRENT_TIMESTAMP(3) 转换成了衍生对象触发器

# 示例8

-- MySQL
CREATE TEMPORARY TABLE `temp_table` (
  `id` INT DEFAULT NULL,
  `name` VARCHAR(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- YashanDB
CREATE GLOBAL TEMPORARY TABLE "TEMP_TABLE" (
	"ID" INT DEFAULT NULL,
	"NAME" VARCHAR(20 CHAR) DEFAULT NULL
);
-- 注意：TEMPORARY 转换成了 GLOBAL TEMPORARY

# 示例9

-- MySQL
CREATE TABLE `partition_option` (
  `empno` VARCHAR(20) NOT NULL,
  `empname` VARCHAR(20) DEFAULT NULL,
  `deptno` INT DEFAULT NULL,
  `birthdate` year NOT NULL,
  `salary` INT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=1 MAX_ROWS=100
/*!50100 PARTITION BY RANGE (`birthdate`)
(PARTITION p1 VALUES LESS THAN (1980) MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1990) MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB) */
 
-- YashanDB
CREATE TABLE "PARTITION_OPTION" (
	"EMPNO" VARCHAR(20 CHAR),
	"EMPNAME" VARCHAR(20 CHAR) DEFAULT NULL,
	"DEPTNO" INT DEFAULT NULL,
	"BIRTHDATE" SMALLINT,
	"SALARY" INT DEFAULT NULL
)
PARTITION BY RANGE ("BIRTHDATE")
 ( PARTITION "P1" VALUES LESS THAN (1980) , PARTITION "P2" VALUES LESS THAN (1990) , PARTITION "P3" VALUES LESS THAN (MAXVALUE) );
-- 注意：移除/*!50100 */

# 示例10

-- MySQL
CREATE TABLE `auto_increment_tab` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- YashanDB
CREATE TABLE "AUTO_INCREMENT_TAB" (
	"ID" INT DEFAULT "AUTO_INCREMENT_TAB_ID_SEQ".NEXTVAL 
)
;
-- 注意：移除ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

# 示例11

-- MySQL
CREATE TABLE `remove_col_option` (
  `id` INT NOT NULL /*!50606 COLUMN_FORMAT FIXED */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- YashanDB
CREATE TABLE "REMOVE_COL_OPTION" (
	"ID" INT
)
;
-- 注意：移除COLUMN_FORMAT FIXED

# 示例12

-- MySQL
CREATE TABLE `partition_option` (
  `empno` VARCHAR(20) NOT NULL,
  `empname` VARCHAR(20) DEFAULT NULL,
  `deptno` INT DEFAULT NULL,
  `birthdate` year NOT NULL,
  `salary` INT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=1 MAX_ROWS=100
/*!50100 PARTITION BY RANGE (`birthdate`)
(PARTITION p1 VALUES LESS THAN (1980) MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1990) MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB) */

-- YashanDB
CREATE TABLE "PARTITION_OPTION" (
	"EMPNO" VARCHAR(20 CHAR),
	"EMPNAME" VARCHAR(20 CHAR) DEFAULT NULL,
	"DEPTNO" INT DEFAULT NULL,
	"BIRTHDATE" SMALLINT,
	"SALARY" INT DEFAULT NULL
)
PARTITION BY RANGE ("BIRTHDATE")
 ( PARTITION "P1" VALUES LESS THAN (1980) , PARTITION "P2" VALUES LESS THAN (1990) , PARTITION "P3" VALUES LESS THAN (MAXVALUE) )
;
-- 注意：移除分区的参数MAX_ROWS = 100 MIN_ROWS = 1 ENGINE = InnoDB

# 示例13

-- MySQL
CREATE TABLE `tb_sub` (
  `id` INT DEFAULT NULL,
  `purchased` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`purchased`)
SUBPARTITION BY HASH (`purchased`)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
 
-- YashanDB 22.2
 CREATE TABLE "TB_SUB" (
	"ID" INT DEFAULT NULL,
	"PURCHASED" SMALLINT DEFAULT NULL
)
PARTITION BY RANGE ("PURCHASED")
 ( PARTITION p0 VALUES LESS THAN (1990) , PARTITION p1 VALUES LESS THAN (2000) , PARTITION p2 VALUES LESS THAN (MAXVALUE) )
;
-- 注意：移除子分区SUBPARTITION

# 示例14

-- MySQL
CREATE TABLE `remove_tab_option` (
  `id` INT NOT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1231 DEFAULT CHARSET=utf8mb3 COMMENT='xxx'

-- YashanDB
CREATE TABLE "REMOVE_TAB_OPTION" (
	"ID" INT 
);
-- 注意：提取非空约束为单独对象

# 示例15

-- MySQL
CREATE TABLE `remove_tab_option` (
  `id` INT COMMENT 'xxx',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1231 DEFAULT CHARSET=utf8mb3 COMMENT='xxx'

-- YashanDB
CREATE TABLE "REMOVE_TAB_OPTION" (
	"ID" INT 
)
;
COMMENT ON COLUMN "REMOVE_TAB_OPTION"."ID" IS 'xxx';
COMMENT ON TABLE "REMOVE_TAB_OPTION" IS 'xxx';
-- 注意：提取列注释、表注释为单独语句

# 示例16

-- MySQL
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test1702`.`test_view` AS SELECT `test1702`.`partition_option`.`empno` AS `empno`,`test1702`.`partition_option`.`empname` AS `empname`,`test1702`.`partition_option`.`deptno` AS `deptno`,`test1702`.`partition_option`.`birthdate` AS `birthdate`,`test1702`.`partition_option`.`salary` AS `salary` FROM `test1702`.`partition_option`

-- YashanDB
CREATE VIEW "TEST1702"."TEST_VIEW"
AS
SELECT "TEST1702"."PARTITION_OPTION"."EMPNO" AS "EMPNO", "TEST1702"."PARTITION_OPTION"."EMPNAME" AS "EMPNAME", "TEST1702"."PARTITION_OPTION"."DEPTNO" AS "DEPTNO", "TEST1702"."PARTITION_OPTION"."BIRTHDATE" AS "BIRTHDATE", "TEST1702"."PARTITION_OPTION"."SALARY" AS "SALARY"
FROM "TEST1702"."PARTITION_OPTION";
-- 注意：移除SQL SECURITY DEFINER

# 示例17

-- MySQL
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test1702`.`test_view1` AS SELECT `test1702`.`partition_option`.`empno` AS `empno`,`test1702`.`partition_option`.`empname` AS `empname`,`test1702`.`partition_option`.`deptno` AS `deptno`,`test1702`.`partition_option`.`birthdate` AS `birthdate`,`test1702`.`partition_option`.`salary` AS `salary` FROM `test1702`.`partition_option` WITH CASCADED CHECK OPTION

-- YashanDB
CREATE VIEW "TEST1702"."TEST_VIEW1"
AS
SELECT "TEST1702"."PARTITION_OPTION"."EMPNO" AS "EMPNO", "TEST1702"."PARTITION_OPTION"."EMPNAME" AS "EMPNAME", "TEST1702"."PARTITION_OPTION"."DEPTNO" AS "DEPTNO", "TEST1702"."PARTITION_OPTION"."BIRTHDATE" AS "BIRTHDATE", "TEST1702"."PARTITION_OPTION"."SALARY" AS "SALARY"
FROM "TEST1702"."PARTITION_OPTION";
-- 注意：移除WITH CASCADED CHECK OPTION

# 示例18

-- MySQL
CREATE DEFINER=`root`@`%` EVENT `myevent` ON SCHEDULE AT '2024-02-22 02:44:02' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE myschema.mytable SET mycol = mycol + 1

-- YashanDB
EXEC DBMS_SCHEDULER.CREATE_JOB('test1702.myevent',
'PLSQL_BLOCK', 
'BEGIN UPDATE myschema.mytable SET mycol = mycol + 1 ;  END ;' ,
0,
TO_TIMESTAMP('2024-02-22 02:44:02', 'YYYY-MM-DD HH24:MI:SS'),
null,
null,
'DEFAULT_JOB_CLASS',
false,
true,
'')
-- 注意：MySQL的EVENT转换成Yashandb JOB

# 示例19

-- MySQL
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test1702`.`test_view1` AS SELECT `test1702`.`partition_option`.`empno` AS `empno`,`test1702`.`partition_option`.`empname` AS `empname`,`test1702`.`partition_option`.`deptno` AS `deptno`,`test1702`.`partition_option`.`birthdate` AS `birthdate`,`test1702`.`partition_option`.`salary` AS `salary` FROM `test1702`.`partition_option` WITH CASCADED CHECK OPTION

-- YashanDB
CREATE VIEW "TEST1702"."TEST_VIEW1"
AS
SELECT "TEST1702"."PARTITION_OPTION"."EMPNO" AS "EMPNO", "TEST1702"."PARTITION_OPTION"."EMPNAME" AS "EMPNAME", "TEST1702"."PARTITION_OPTION"."DEPTNO" AS "DEPTNO", "TEST1702"."PARTITION_OPTION"."BIRTHDATE" AS "BIRTHDATE", "TEST1702"."PARTITION_OPTION"."SALARY" AS "SALARY"
FROM "TEST1702"."PARTITION_OPTION";
-- 注意：移除ALGORITHM=UNDEFINED DEFINER=`root`@`%`

# 示例20

-- Oracle
CREATE TABLE "TEST"."TAB004" 
   (	"ID" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  TABLESPACE "USERS" ;
  
 CREATE INDEX "TEST"."IDX05" ON "TEST"."TAB004" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  TABLESPACE "USERS" ;

-- YashanDB
CREATE TABLE "TEST"."TAB004" 
   (	"ID" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS  TABLESPACE "USERS" ;
 
CREATE INDEX "TEST"."IDX05" ON "TEST"."TAB004" ( "ID" ) 
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE "USERS";

-- 注意：移除NOLOGGING

# 示例21

-- Oracle
CREATE INDEX "TEST"."IDX01" ON "TEST"."PT_HASH_TEST" ("PID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "USERS" ;

-- YashanDB
CREATE INDEX "TEST"."IDX01" ON "TEST"."PT_HASH_TEST" ( "PID" ) 
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE "USERS";

-- 注意：移除COMPUTE STATISTICS

# 示例22

-- Oracle
CREATE SEQUENCE  "TEST"."SEQ001"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

-- YashanDB
CREATE SEQUENCE "TEST"."SEQ001" START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

-- 注意：移除NOKEEP  NOSCALE  GLOBAL
pdf-btn 下载文档
copy-btn 复制链接
段落导航
元数据转换规则
全部示例
示例3
示例4
示例5
示例8
示例9
示例10
示例11
示例12
示例13
示例14
示例15
示例16
示例17
示例18
示例19
示例20
示例21
示例22