mysql表结构转postgresql mysql表转oracle

转载

mob64ca141a2a87 2023-09-21 19:02:09

文章标签 mysql表结构转postgresql oracle 主键 MySQL 建表 文章分类 MySQL 数据库

一、目的

将MySQL数据库的几百张表（表结构+索引）转换到Oracle数据库。

二、方法

1、使用Navicat工具的数据传输（放弃）
2、使用PowerDesigner工具的反向工程 （选择此方法）
3、使用Clougence cloudcanal工具的数据同步（没有试过该方法）

三、利弊

1、Navicat工具的数据传输
有一些无法跳过的问题，比如MySQL中的decimal(12,2) 字段类型转换到Oracle数据库的时候变成了number类型，精度丢失。直接放弃此法。

2、PowerDesigner（v16.6）工具的反向工程
测试发现如下一些问题：

（1）反向工程连接MySQL数据库时，生成的表索引，普通的组合索引丢失，只能生成主键、唯一索引、普通单列索引。
（2）change database为Oracle后，生成的Oracle建表语句中，默认值如果是非数字的字符，引号丢失，建表报错。并且发现组合约束（多列的主键和唯一索引）字段顺序有可能发生改变，比如原来的主键是（A,B），转换后变成了（B,A）。
（3）change database时Oracle版本最高只能选到12c，对象名最长为30个字节，超过30个字节的表名会自动截去。

决定选择此方法，PowerDesigner工具只转化表结构，不转约束和索引，主键、唯一索引、普通索引通过MySQL系统表找出来，一把创建到Oracle库。默认值问题在建表脚本中手工修复。超出30个字节的对象名，在MySQL系统表中找出来，根据Oracle版本和实际使用情况处理。

3、Clougence cloudcanal工具（没有试过该方法）
朋友公司使用此工具同步不同数据库之间的数据，据说有个在线版，不过我的数据库都在本机或公司内网，所以没有试过。
单机版安装详情：https://doc-cloudcanal.clougence.com/operation/install_windows

四、详解方法2：使用PowerDesigner工具的反向工程

1、安装配置MySQL的ODBC，Oracle的ODBC我没有配置，我是直接拿建表语句去Oracle库执行，而不是直接用此工具将表生成到Oracle库。（没有研究此工具，用了最简单的功能）

2、用PowerDesigner工具，反向工程连接到MySQL的ODBC，再改变数据库为Oracle，取转换后的Oracle建表语句。

step1：

mysql表结构转postgresql mysql表转oracle_mysql表结构转postgresql

step2：

mysql表结构转postgresql mysql表转oracle_MySQL_02

step3：选择配置好的MySQL的ODBC

mysql表结构转postgresql mysql表转oracle_建表_03

step4：选择需要转换的库和表，不需要转化keys和indexs，就都可以去掉勾选。

mysql表结构转postgresql mysql表转oracle_MySQL_04

step5：改变当前数据库

mysql表结构转postgresql mysql表转oracle_oracle_05

step6：选择Oracle版本（v16.6版本Oracle最高可以只能选到12c）

mysql表结构转postgresql mysql表转oracle_建表_06

step7：preview中查看整库的建表语句

mysql表结构转postgresql mysql表转oracle_oracle_07

3、建表语句手工处理后，执行到Oracle库。

去掉最前面的drop语句，去掉双引号，去掉库名，default值检查处理等。一些问题要碰到了才会发现，如字段属性长度超出等，都需要手工处理。

4、主键、唯一索引、普通索引通过MySQL系统表找出来，语句生成好，一把执行到Oracle库。

-- 五、初始化索引
-- 5.1 创建主键(主键名我以PK_表名来命名，建主键的写法可自行修改)
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ADD CONSTRAINT ',INDEX_NAME,' PRIMARY KEY (',index_details,') ;') FROM (
SELECT 'PRIMARY' AS INDEX_TYPE,TABLE_NAME,CONCAT('PK_',TABLE_NAME) AS INDEX_NAME,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS index_details FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'test' AND INDEX_NAME='PRIMARY'
  GROUP BY TABLE_NAME,INDEX_NAME
  ) T ;

-- 5.2 创建唯一索引
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ADD CONSTRAINT ',INDEX_NAME,' UNIQUE (',index_details,') ;') FROM (
SELECT TABLE_NAME,INDEX_NAME,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS index_details FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'test' AND INDEX_NAME<>'PRIMARY' 
AND INDEX_NAME IN (SELECT constraint_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t WHERE t.TABLE_SCHEMA = 'test' AND CONSTRAINT_TYPE = 'UNIQUE')
  GROUP BY TABLE_NAME,INDEX_NAME
) T ;  

-- 5.3 创建普通索引
SELECT CONCAT('CREATE INDEX ',INDEX_NAME,' ON ',TABLE_NAME,'(',index_details,') ;') FROM (
SELECT TABLE_NAME,INDEX_NAME,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS index_details FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'test' AND INDEX_NAME<>'PRIMARY' 
AND INDEX_NAME NOT IN (SELECT constraint_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t WHERE t.TABLE_SCHEMA = 'test' AND CONSTRAINT_TYPE = 'UNIQUE')
  GROUP BY TABLE_NAME,INDEX_NAME
  ) T;

五、Oracle库建表后检查

我主要检查MySQL和Oracle库的这几方面：
1、表数量是否一致，表名是否一致（导出对比）；
2、字段数量是否一致，按字段类型分组，各组的字段数量对比；
3、主键、唯一索引、普通索引的个数，以及对应的索引列是否一致（导出对比）。

主要检查语句：

-- 七、检查
-- 7.1 mysql
-- 7.1.1 字段属性分类检查
SELECT DATA_TYPE,COUNT(1) FROM information_schema.columns WHERE TABLE_SCHEMA = 'test' GROUP BY DATA_TYPE;

-- 7.1.2 表数量
SELECT * FROM information_schema.tables WHERE table_schema='test' AND TABLE_TYPE='BASE TABLE'; 

-- 7.1.3 索引
-- 主键
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND SEQ_IN_INDEX=1 AND INDEX_NAME='PRIMARY';
-- 非主键
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND SEQ_IN_INDEX=1 AND INDEX_NAME<>'PRIMARY';

-- 主键数量:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t WHERE t.TABLE_SCHEMA = 'test' AND CONSTRAINT_TYPE = 'PRIMARY KEY';
-- 唯一索引数量:
SELECT table_name,constraint_name,CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t WHERE t.TABLE_SCHEMA = 'test' AND CONSTRAINT_TYPE = 'UNIQUE';

-- 7.2 oracle（用户名也建成了test）
-- 7.2.1 字段属性分类检查
SELECT DATA_TYPE,COUNT(1) FROM ALL_TAB_COLUMNS WHERE OWNER='test' GROUP BY DATA_TYPE ; 

-- 7.2.2 表数量 
SELECT * FROM ALL_TABLES WHERE OWNER='test'  ; 

-- 7.2.3 索引
-- 主键 
SELECT * FROM user_constraints WHERE OWNER='test' AND constraint_type ='P'; 

-- 唯一索引 
SELECT * FROM user_constraints WHERE OWNER='test' AND constraint_type ='U'; 

-- 普通索引（可能含主键和唯一索引）
SELECT * FROM user_indexes WHERE TABLE_OWNER='test' AND index_type='NORMAL' AND uniqueness='NONUNIQUE';