需求背景

最近有个项目需要将Oracle的数据库转换为MySQL,并且要求把Oracle的数据也要迁移到MySQL中。ORM框架用的是Hibernate。
试了多种办法:

Navicat Premium 数据传输功能

传输失败

DB Convert Studio 主从复制功能

基本可以成功,问题很多:

  • 速度非常慢;
  • 需要定制化,处理相互依赖的外键
  • 特殊字段类型:Oracle的Number(19)会转换为MySQL的Decimal类型,其实在Java实体类中是Long类型,后续新的实体类自动创建会对应MySQL的BigInt。此外还有Java中的Boolean类型。

关于DB Convert的使用,可以参考博主的另一篇文章。

自定制脚本转换

在折腾了很久之后,决定自己写脚本完成这个工作。因为Hibernate可以自动根据实体类更新数据库表字段/索引/外键,不支持自动加主键。
因此,脚本只需要生成表的创建语句、添加主键语句、数据插入语句即可,Java项目启动后,Hibernate会自动更新外键/索引等信息。自己写的脚本,定制化程度高,结合项目特点可以持续更新。

DECLARE
    l_user                VARCHAR(255) := 'YOUR_USER_HERE'; --指定用户
    row_data_cur          SYS_REFCURSOR; --数据行游标
    l_cur                 NUMBER;
    l_ret                 NUMBER;
    l_col_cnt             NUMBER;
    l_rec_tab             dbms_sql.desc_tab;
    cons_varchar2_code    NUMBER := 1;
    cons_number_code      NUMBER := 2;
    cons_date_code        NUMBER := 12;
    cons_clob_code        NUMBER := 112;
    cons_blob_code        NUMBER := 113;
    cons_timestamp_code   NUMBER := 180;
    l_varchar2_col        VARCHAR2(32767); --1
    l_number_col          NUMBER; --2
    l_date_col            DATE; --12
    l_clob_col            CLOB; --112
    l_blob_col            BLOB; --113
    l_timestamp_col       TIMESTAMP(9); --180
    cons_timestamp_frm    VARCHAR2(32) := 'YYYY-MM-DD HH24:MI:SS';
    CURSOR tabcur IS --游标-表
      SELECT table_name,
             owner,
             tablespace_name,
             initial_extent,
             next_extent,
             pct_used,
             pct_free,
             pct_increase,
             degree
      FROM   sys.dba_tables
      WHERE  owner = Upper(l_user)
      --              AND table_name IN( 'USER', 'ORG' )
      ;
    --
    colcount              NUMBER(5); --列循环次数
    maxcol                NUMBER(5); --字段总个数
    fillspace             NUMBER(5); --空格数(字段+空格=40)
    collen                NUMBER(5); --字段字符串长度
    pk_column             VARCHAR(255); --主键字段(拼接)
    pk_column_count       NUMBER(2); --主键字段个数
    row_count             NUMBER(2); --数据行数
    row_count_sql         VARCHAR(255); --查找数据行数的sql
    insert_into_sql_start VARCHAR(2000); --插入数据的sql
    insert_into_sql_full  VARCHAR(32767); --插入数据的sql
    select_sql            VARCHAR(2000); --查询原表数据的sql
    col_name_append_quot1 VARCHAR(2000); --列字段拼接,带有`
    col_name_append_quot2 VARCHAR(2000); --列字段拼接,带有"
    col_array             dbms_sql.varchar2_table; --列数组
--
BEGIN
    dbms_output.ENABLE(buffer_size => NULL);

    maxcol := 0;

    --
    FOR tabrec IN tabcur LOOP
        --循环表
        SELECT Count(column_id)
        INTO   maxcol
        FROM   sys.dba_tab_columns
        WHERE  table_name = tabrec.table_name
               AND data_type <> 'RAW'
               AND owner = tabrec.owner;

        --
        dbms_output.Put_line('CREATE TABLE '
                             || tabrec.table_name);

        dbms_output.Put_line('( ');

        --
        colcount := 0;

        row_count := 0;

        col_name_append_quot1 := '';

        col_name_append_quot2 := '';

        insert_into_sql_start := 'insert into '
                                 || tabrec.table_name
                                 || '(';

        FOR item IN (SELECT column_name
                            col1,
                            Decode(data_type, 'BLOB', 'longblob   ',
                                              'CLOB', 'longtext ',
                                              'DATE', 'datetime ',
                                              'FLOAT', 'DECIMAL(10, 2) ',
                                              'TIMESTAMP(6)', 'datetime  ',
                                              'NVARCHAR2', 'VARCHAR'
                                                           || '('
                                                           || char_length
                                                           || ') ',
                                              'VARCHAR2', 'VARCHAR'
                                                          || '('
                                                          || char_length
                                                          || ') ',
                                              'NUMBER', 'DECIMAL'
                                                        ||
                            Decode(Nvl(data_precision, 0), 0, ' ',
                                                           ' ('
                                                           ||
                            data_precision
                                                           ||
                            Decode(Nvl(data_scale, 0), 0, ') ',
                                                       ','
                                                       || data_scale
                                                       || ') ')))
                                        col2
                     FROM   sys.dba_tab_columns
                     WHERE  owner = Upper(l_user)
                            AND table_name = tabrec.table_name
                            AND data_type <> 'RAW'
                     ORDER  BY column_id) LOOP
            collen := Length(item.col1);

            fillspace := 40 - collen;

            dbms_output.Put('`'
                            || item.col1
                            || '`');

            --
            FOR i IN 1 .. fillspace LOOP
                dbms_output.Put(' ');
            END LOOP;

            --            
            IF item.col2 LIKE 'DECIMAL (19) ' THEN --一般为主键id
              dbms_output.Put('bigint ');
            ELSIF item.col2 = 'DECIMAL (1) ' THEN --一般为bool
              dbms_output.Put('tinyint ');
            ELSE
              dbms_output.Put(item.col2);
            END IF;

            colcount := colcount + 1;

            Col_array(colcount) := item.col1;

            --
            IF ( colcount < maxcol ) THEN
              dbms_output.Put_line(',');

              col_name_append_quot1 := col_name_append_quot1
                                       || '`'
                                       || item.col1
                                       || '`'
                                       || ', ';

              col_name_append_quot2 := col_name_append_quot2
                                       || '"'
                                       || item.col1
                                       || '"'
                                       || ', ';
            ELSE
              dbms_output.Put_line(') ;');

              col_name_append_quot1 := col_name_append_quot1
                                       || '`'
                                       || item.col1
                                       || '`';

              col_name_append_quot2 := col_name_append_quot2
                                       || '"'
                                       || item.col1
                                       || '"';
            END IF;
        END LOOP;

        pk_column_count := 0;

        pk_column := '';

        SELECT Count(cols.column_name)
        INTO   pk_column_count
        FROM   all_constraints cons,
               all_cons_columns cols
        WHERE  cons.constraint_type = 'P'
               AND cols.table_name = tabrec.table_name
               AND cons.constraint_name = cols.constraint_name
               AND cons.owner = cols.owner
               AND cons.owner = tabrec.owner;

        IF pk_column_count > 0 THEN
          FOR pk_name IN (SELECT cols.column_name
                          INTO   pk_column
                          FROM   all_constraints cons,
                                 all_cons_columns cols
                          WHERE  cons.constraint_type = 'P'
                                 AND cols.table_name = tabrec.table_name
                                 AND cons.constraint_name = cols.constraint_name
                                 AND cons.owner = cols.owner
                                 AND cons.owner = tabrec.owner) LOOP
              pk_column := pk_column
                           || pk_name.column_name
                           || ',';
          END LOOP;
        END IF;

        IF pk_column_count > 0 THEN
          pk_column := Substr(pk_column, 1, Length(pk_column) - 1);

          dbms_output.Put_line('ALTER TABLE '
                               || tabrec.table_name
                               || ' ADD CONSTRAINT '
                               || tabrec.table_name
                               || '_PK_'
                               || ' PRIMARY KEY ('
                               || pk_column
                               ||');');
        END IF;

        select_sql := 'select '
                      || col_name_append_quot2
                      || ' from '
                      || l_user
                      || '.'
                      || tabrec.table_name
                      || '';

        --        dbms_output.Put_line(select_sql);
        insert_into_sql_start := insert_into_sql_start
                                 || col_name_append_quot1
                                 || ') values(';

        l_cur := dbms_sql.open_cursor;

        dbms_sql.Parse(l_cur, select_sql, dbms_sql.native);

        dbms_sql.Describe_columns(l_cur, l_col_cnt, l_rec_tab);

        FOR i IN 1..l_rec_tab.count LOOP
            IF L_rec_tab(i).col_type = cons_varchar2_code THEN --字符类型
              dbms_sql.Define_column(l_cur, i, l_varchar2_col,
              L_rec_tab(i).col_max_len);
            ELSIF L_rec_tab(i).col_type = cons_number_code THEN --数字类型
              dbms_sql.Define_column(l_cur, i, l_number_col);
            ELSIF L_rec_tab(i).col_type = cons_date_code THEN --date
              dbms_sql.Define_column(l_cur, i, l_date_col);
            ELSIF L_rec_tab(i).col_type = cons_clob_code THEN --clob
              dbms_sql.Define_column(l_cur, i, l_clob_col);
            ELSIF L_rec_tab(i).col_type = cons_blob_code THEN --clob
              dbms_sql.Define_column(l_cur, i, l_blob_col);
            ELSIF L_rec_tab(i).col_type = cons_timestamp_code THEN --timestamp
              dbms_sql.Define_column(l_cur, i, l_timestamp_col);
            ELSE
              Raise_application_error(-20001, 'Column: '
                                              ||L_rec_tab(i).col_name
                                              ||'Type not supported: '
                                              ||L_rec_tab(i).col_type);
            END IF;
        END LOOP;

        l_ret := dbms_sql.EXECUTE(l_cur);

        LOOP
            insert_into_sql_full := insert_into_sql_start;

            l_ret := dbms_sql.Fetch_rows(l_cur);

            exit WHEN l_ret = 0;

            FOR i IN 1..l_rec_tab.count LOOP
                IF L_rec_tab(i).col_type = cons_varchar2_code THEN
                  --字符类型 
                  dbms_sql.Column_value(l_cur, i, l_varchar2_col);

                  IF l_varchar2_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    insert_into_sql_full := insert_into_sql_full
                                            || ''''
                                            || Replace(To_char(l_varchar2_col),
                                               ''''
                                               ,
                                               '\''')
                                            || ''','; --单引号转义
                  END IF;
                ELSIF L_rec_tab(i).col_type = cons_number_code THEN
                  --数字类型
                  dbms_sql.Column_value(l_cur, i, l_number_col);

                  IF l_number_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    insert_into_sql_full := insert_into_sql_full
                                            || To_char(l_number_col)
                                            || ',';
                  END IF;
                ELSIF L_rec_tab(i).col_type = cons_date_code THEN --date
                  dbms_sql.Column_value(l_cur, i, l_date_col);

                  IF l_date_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    insert_into_sql_full := insert_into_sql_full
                                            || ''''
                                            ||
                    To_char(l_date_col, cons_timestamp_frm)
                                            || ''',';
                  END IF;
                ELSIF L_rec_tab(i).col_type = cons_clob_code THEN --Clob
                  dbms_sql.Column_value(l_cur, i, l_clob_col);

                  IF l_clob_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    --                insert_into_sql_full := insert_into_sql_full || '''' || dbms_lob.substr(l_clob_col) || ''',';
                    insert_into_sql_full := insert_into_sql_full
                                            || ''''
                                            || 'CLOB HERE, SKIP'
                                            || ''',';
                  END IF;
                ELSIF L_rec_tab(i).col_type = cons_blob_code THEN --Blob
                  dbms_sql.Column_value(l_cur, i, l_blob_col);

                  IF l_blob_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    insert_into_sql_full := insert_into_sql_full
                                            || '0x'
                                            || dbms_lob.Substr(l_blob_col)
                                            || ',';
                  END IF;
                ELSIF L_rec_tab(i).col_type = cons_timestamp_code THEN
                  --timestamp
                  dbms_sql.Column_value(l_cur, i, l_timestamp_col);

                  IF l_timestamp_col IS NULL THEN
                    insert_into_sql_full := insert_into_sql_full
                                            || 'NULL,';
                  ELSE
                    insert_into_sql_full := insert_into_sql_full
                                            || ''''
                                            ||
                    To_char(l_timestamp_col, cons_timestamp_frm)
                                            || ''',';
                  END IF;
                ELSE
                  Raise_application_error(-20001, 'Column: '
                                                  ||L_rec_tab(i).col_name
                                                  ||'Type not supported: '
                                                  ||L_rec_tab(i).col_type);
                END IF;
            END LOOP;

            insert_into_sql_full := Substr(insert_into_sql_full, 1, Length(
                                    insert_into_sql_full) - 1);
            --删除最后一个,

            insert_into_sql_full := insert_into_sql_full
                                    || ');';

            dbms_output.Put_line(insert_into_sql_full);
        END LOOP;
    END LOOP;
END;

脚本踩坑说明

在我的项目中,已经完全满足了数据库转换了需求。其它项目使用的话,需要注意以下几点

支持的数据类型

脚本239-259行,仅支持了项目中有出现过的字段类型。可以通过以下脚本,确定项目数据库的所有类型:

SELECT
	DATA_TYPE, DATA_SCALE, COUNT(*) QTY
FROM
	sys.dba_tab_columns 
WHERE
	owner = UPPER( 'YOUR_USER_HERE' ) 
	and table_name not in (select view_name from all_views where owner = 'YOUR_USER_HERE')
	GROUP BY DATA_TYPE,  DATA_SCALE
ORDER BY DATA_TYPE ASC, QTY DESC;

关于类型代码,可以参考Oracle文档:https://docs.oracle.com/cd/E11882_01/server.112/e41085/sqlqr06002.htm#SQLQR959

脚本执行速度

脚本中使用dbms_output.Put_line来输出生成的脚本,如果库数据量大,执行会非常慢,可以通过UTL_FILE.put_line的方式输出到文件中,执行速度会快很多

Blob和Clob

脚本320、332中处理了Blob和Clob类型,因为Put_line最多支持32767个字符,如果超出这个限制,语句执行会报错。
关于这个问题,网上有提到说定义函数进行循环打印,这个我没试过,不过应该可以,只是循环打印会引起多余的换行,但是可以在执行完后通过正则删除掉多余的换行即可。

dbms_sql.Describe_columns ORA-06502异常问题

这个是因为Oracle表中字段超出32个字符引起的,具体参考我的另一篇文章:
建议和DBA/Java开发人员沟通,将这个字段名长度减少到32位以内即可。

其它数据库支持

如果需要转换为SQL Server或者其它数据库,可以根据目标数据库的语法区别,针对性调整即可。

参考

https://stackoverflow.com/questions/937398/how-to-get-oracle-create-table-statement-in-sqlplushttps://github.com/teopost/oracle-scripts/blob/master/fn_gen_inserts.sqlhttps://docs.oracle.com/cd/E11882_01/server.112/e41085/sqlqr06002.htm#SQLQR959https://stackoverflow.com/questions/1649183/generating-sql-insert-into-for-oracle