navicat实现数据库迁移之oracle到mysql

• 简介：

Navicat Premium 非常强大，可以连接大部分主流关系型数据库，如oracle、mysql、sqlserver、sqlite等，操作方便、界面简洁。但它还有一个更强大的功能，可以跨库批量迁移数据表，有这种需求的朋友可以通过这个工具轻松实现。

我目前通过navicat只实现了从oracle到mysql库的批量迁移操作，其它库之间的批量迁移没试过，有操作过的朋友可以分享下相关经验。

• 环境：

windows 64 + Navicat Premium 11.1.13 + Oracle 11g

• 软件安装：

navicat和oracle的安装直接谷歌，这里不再赘述。

• 注意点：

1、由于mysql的date类型只支持存储日期，不能存储时间，navicat在转换时，如果是oracle的date，会自动转成mysql的date，这样会导致mysql的精度不够而报错，所以要提前将oracle的date类型转换成timestamp类型。如果表的数量比较多，手工改太麻烦了，我这里写了个存储过程，自动将oracle中所有表中的DATE类型转换成TIMESTAMP类型：

create or replace procedure convert_date_to_timestamp
/**************************
  function: 将数据库中所有的表中含DATE类型的字段改为TIMESTAMP类型；
  需要建立下列类型表：
  create table type_table_info (
    table_name varchar2(30),
    column_name varchar2(30),
    data_type varchar2(100)
  )  
***************************/
is
  v_query_base_sql varchar2(100) := 'SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name = ''';
  v_query_table_sql varchar2(150);
  v_alter_sql varchar2(100);

  type table_type is table of type_table_info%rowtype;
  table_array table_type;

begin
  for c_tabs in (
       select table_name from user_tables where table_name <> 'type_table_info' 
    )
    loop
      v_query_table_sql := v_query_base_sql || c_tabs.table_name || '''';
      execute immediate v_query_table_sql bulk collect into table_array;
      for i in table_array.first .. table_array.last
        loop
          --DBMS_OUTPUT.put_line(table_array(i).column_name || ':' || table_array(i).data_type);
          if table_array(i).data_type = 'DATE' then
            v_alter_sql := 'alter table ' || table_array(i).table_name || ' modify ' || table_array(i).column_name || ' timestamp';
            DBMS_OUTPUT.put_line(table_array(i).column_name || ': ' || v_alter_sql);
            execute immediate v_alter_sql;
          end if;
          
        end loop;
    end loop;
end convert_date_to_timestamp;

2、异常信息：Specified key was too long; max key length is 767 bytes

我这边是通过修改mysql字符集解决的，改为utf8

• 转换步骤：

工具-->数据传输，在“源”中选择待复制的数据库（oracle）信息，选中所有表，在“目标”中选择目标数据库（mysql），然后点击“开始”，即可实现批量从oracle库复制表到mysql库了，在“信息日志”栏中可以查看日志信息，如果有"unsuccessly"表示当前表未复制成功，可以查看异常信息作相应处理后再转换。

• 附

由于语法差异，函数、视图等不支持自动转换，需要一个一个的复制修改了。

这里简单介绍下oracle与mysql的一些异同点，在转换视图、函数时会用到：

1、mysql查询时，表名必须大写（别名也区分大小写）；
2、mysql的使用函数时，函数名和括号之间不能有空格，>= 之间也不能有空格（如，> =会报错）；
3、mysql视图中不支持子查询，必须使用视图嵌套；
4、mysql中的系统函数与oracle中的系统函数有很多差异，这里需要特别注意下。