一、前言

这里记录一次将MySQL数据库中的表数据迁移到Oracle数据库中的全过程 ,使用工具 Navicat,版本 12.0.11

操作环境及所用工具:
  1. mysql5.7
  2. oracle18c
  3. windows
  4. Navicat12.0.11
  5. idea
 
 

二、开始移植

点击 工具 -> 数据传输

 
 

左边 标识mysql数据库 , 右边 目标 标识要移植到的oracle数据库

 

高级选项中勾选大写

温馨小提示:
如果字段名和表名都为小写,oracle操作数据的时候将会出现找不到表或视图的错误,解决方法是必须加上双引号才能查询到, 这样的话我们通过程序操作数据的时候必须加上双引号,即大大加重了迁移数据库后的工作量,因此这里需勾选转换对象名为大写 ,同时在转换过程中如果字段名出现oracle关键字的话,它会自动给我们加上双引号解决关键字的困扰!!!
【 ex: user -> "USER" number -> "NUMBER" desc -> "DESC" level -> "LEVEL"

 
 

选择需要移植的表,这里我一把梭全选了~

 

然后等待数据传输完成

 

如果最后遇到如下情况,可暂不管,直接关闭即可~

 

然后查看oracle,如下,数据导入成功

 
 

温馨小提示:传输过程中可能会存在有部分几张表不成功,手动导一下就好了~~

三、问题

1、解决oracle自增主键

oracle设置自增主键的几种方式:
  1. 序列 触发器
  2. 序列 Hibernate配置 (注:此方式仅适用于通过Hibernate连接数据库的方式)
  3. oracle12c版本之后新增 自增列语法 GENERATED BY DEFAULT AS IDENTITY
解决思路:

在ddl 创建表sql中添加自增主键的命令,重新创建一次表结构,然后再将oracle中的数据单独导入
这里由于小编oracle版本为18c 因此在创建表的时候加上自增主键语法即可完成!

① 备份数据 -> 数据泵方式

数据泵 -> 数据泵导出

阿里云-推广AD
 
在这里插入图片描述
 
在这里插入图片描述
 
 
② idea中如下查看ddl
 
 

然后将ddl拷贝到一个txt文本文件中保存

 
在这里插入图片描述
③ ddl文件内容替换自增主键工具类

温馨小提示:
这里小编数据库中的时间类型为 DATE 类型 需 改为 TIMESTAMP 类型 ,这个问题可以直接使用idea的替换功能完成修改~ ,其余修改可根据个人实际情况来进行修改

 
 
public class MySQLToOracleTest {

    public static void main(String[] args) {
        try {
            replaceDDLContent("D:\\Users\\zq\\Desktop\\oracle测试\\oracle_ddl.txt"); // TODO 这里修改为自己的ddl文件存放位置
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 替换文本文件中的 自增主键ID
     *
     * @param path
     * @throws IOException
     */
    public static void replaceDDLContent(String path) throws IOException {
        // 原有的内容
        String srcStr = "not null";
        // 要替换的内容
        String replaceStr = "GENERATED BY DEFAULT AS IDENTITY";
        // 读
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line = null;
        // 需要替换的行
        Map< Integer, Object> lineMap = new HashMap< >(104);
        //定义顺序变量
        int count = 0;
        while ((line = bufIn.readLine()) != null) {
            count  ;
            if (line.contains("create table")) {
                lineMap.put(count   2, line);
            }

            //遍历map中的键
            for (Integer key : lineMap.keySet()) {
                if (count == key && !line.contains("NVARCHAR2")) {
                    // TODO 在这里这是自增主键哦
                    line = line.replaceAll(srcStr, replaceStr);
                    lineMap.put(count, line);
                }
            }

            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
        System.out.println("文件存放位置:"   path);
        System.out.println(lineMap);
    }

}
④ 将替换过后的ddl拷贝到idea中的一个新控制台中运行创建表

全选ddl,然后点击左上角运行创建表 【 注:这里需先清空该库下所有表,因此步骤①要先备份一下从mysql迁移到oracle后的表数据,不要忘记哦!!】

 
在这里插入图片描述

等待表全部创建成功,如下所示:

 
在这里插入图片描述
⑤ 导入备份数据

数据泵 -> 数据泵导入

 
在这里插入图片描述
 
在这里插入图片描述
 
 
⑥ 最后查看数据导入成功!
 
 

这时候,数据有了,自增主键也有了,但是存在一个问题就是插入数据的时候主键自增ID都是从1开始自增,如果表中没有数据都还ok,问题是如果表有数据,就会出现主键ID重复的问题!!!

2、解决自增主键ID无法从表数据ID最大值开始增值

思路:拼接出修改表自增ID从几开始的sql即可!
SELECT
    \\'SELECT \\'\\'ALTER TABLE SEWAGE_GY.\\' || t1.table_name || \\' MODIFY(\\' || t1.Column_Name || \\' Generated as Identity (START WITH \\'\\' || MAX( \\' || t1.Column_Name || \\' 1 ) || \\'\\'));\\'\\' FROM \\' || t1.table_name || \\' UNION ALL\\' AS FINAL_SQL
FROM cols t1
LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name
WHERE
    NOT EXISTS (
        SELECT t4.Object_Name
        FROM User_objects t4
        WHERE
            t4.Object_Type = \\'TABLE\\'
            AND t4.TEMPORARY = \\'Y\\'
            AND t4.Object_Name = t1.Table_Name
    )
    AND t1.IDENTITY_COLUMN = \\'YES\\'
ORDER BY t1.Table_Name, t1.Column_ID

命令解析:

# 设置表主键ID从多少开始自增  ex:下面标识从10000开始自增
ALTER TABLE 数据库名.表名 MODIFY(主键ID Generated as Identity (START WITH 10000));

# 查询该库下所有表名
SELECT table_name FROM user_tables;

# 查询出指定表的主键ID字段名
SELECT t1.table_name,t1.Column_Name
FROM cols t1
    LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
    LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name 
WHERE NOT EXISTS (
        SELECT t4.Object_Name 
        FROM User_objects t4 
        WHERE t4.Object_Type = \\'TABLE\\' 
            AND t4.TEMPORARY = \\'Y\\' 
            AND t4.Object_Name = t1.Table_Name 
    ) 
    AND t1.table_name = \\'表名\\' 
    AND t1.IDENTITY_COLUMN = \\'YES\\' 
ORDER BY t1.Table_Name, t1.Column_ID

# 查询该库下所有表名 表主键字段名
SELECT t1.table_name,t1.Column_Name
FROM cols t1
    LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_Name
    LEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name 
WHERE NOT EXISTS (
        SELECT t4.Object_Name 
        FROM User_objects t4 
        WHERE t4.Object_Type = \\'TABLE\\' 
            AND t4.TEMPORARY = \\'Y\\' 
            AND t4.Object_Name = t1.Table_Name 
    ) 
    AND t1.IDENTITY_COLUMN = \\'YES\\' 
ORDER BY t1.Table_Name, t1.Column_ID
 
 

拷贝到新的控制台后注意删除最后一个 UNION ALL 再运行哦!!!

 
 

最终完成自增主键ID从表数据最大值开始自增!

 
 

3、程序中的sql语句转换

这里结合个人语言实际操作…