您的位置: 首页  >  文章  >  记录一次mysql数据库迁移到oracle的详细过程

记录一次mysql数据库迁移到oracle的详细过程

分类: 文章 • 2024-01-04 22:01:52

 

记录一次mysql数据库迁移到oracle的详细过程

 

可行性分析：

1、mysql数据库id支持自增，oracle不支持，可以使用触发器产生id替代

2、日期函数、特殊函数、递归查询等特殊函数需要转换处理

3、mybatis的批量插入、批量更新mysql语法和oracle不一致需要调整

4、oracle的group by和mysql group by ，分页查询的语法差异，oracle不支持limit关键字等

5、需要制定兼容两种数据库的后续sql规范

6、公司框架本身兼容多种数据源，支持多种数据库的分页查询

总结：可以实现，替换数据源配置和增加oracle数据库的mapper文件，可以迁移项目到oracle数据库

方案：

  • 确定使用多份mapper文件方案，后续新的数据库接入修改按此标准

  • 产品仓库目录添加oracle版本脚本

  • mysql版本发包前统计增量脚本和mapper文件，通知oracle版本修改或直接修改对应oracle版本脚本和mapper文件实现同步

  • id生成触发器共用序列，起始序列使用15位，序列使用16位，性能问题待观察

  • 后续mysql版本避免使用日期函数等，日期计算逻辑，字段拼接、limit字段等不兼容语法避免使用

 

操作步骤：

1、数据库迁移使用sqldeveloper连接mysql数据库迁移数据库到oracle，导出ddl和dml

 

记录一次mysql数据库迁移到oracle的详细过程

 

2、整理初始化脚本

    1)添加全局的id序列

CREATE SEQUENCE GLOBAL_ID_SEQ

INCREMENT BY 1 START WITH 100000 MAXVALUE 999999999999999

MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

 

 

   2）为所有表添加id生成触发器

    ......

 

--DROP TRIGGER BM_BANK_BLACKLIST_ID_TRIG;

--/

CREATE TRIGGER BM_BANK_BLACKLIST_ID_TRIG BEFORE INSERT OR UPDATE ON bm_bank_blacklist

FOR EACH ROW

DECLARE

v_newVal NUMBER(16) := 0;

v_incval NUMBER(16) := 0;

BEGIN

IF INSERTING AND :new.id IS NULL THEN

SELECT GLOBAL_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;

-- If this is the first time this table have been inserted into (sequence == 1)

IF v_newVal = 1 THEN

--get the max indentity value from the table

SELECT NVL(max(id),0) INTO v_newVal FROM bm_bank_blacklist;

v_newVal := v_newVal + 1;

--set the sequence to that value

LOOP

EXIT WHEN v_incval>=v_newVal;

SELECT GLOBAL_ID_SEQ.nextval INTO v_incval FROM dual;

END LOOP;

END IF;

-- assign the value from the sequence to emulate the identity column

:new.id := v_newVal;

END IF;

END;

/

 

 

3）表注释和列注释导出

表注释导出

 SELECT concat('COMMENT ON TABLE ',TABLE_NAME,' is ','"',TABLE_COMMENT,'";') FROM information_schema.TABLES WHERE table_schema='bill_match_dev';

列注释导出

  SELECT   concat(' COMMENT ON COLUMN ',  table_name,'.',COLUMN_NAME,' is ','"',column_comment,'";') FROM INFORMATION_SCHEMA.Columns WHERE   table_schema='bill_match_dev'

4)初始化脚本目录结构分层

    ①若使用的数据库类型为mysql数据库
        ⅰ执行"bmtp-script\src\main\resources\v1.0.1\all\mysql"目录下最新日期的"xxx_init建库.sql"和"xxx_初始化数据.sql"全量脚本;
        ⅱ执行"bmtp-script\src\main\resources\increment\mysql"和"bmtp-script\src\main\resources\banks\标准初始化数据"目录下文件日期在执行的全量脚本文件日期之后的增量脚本文件;
        ⅲ执行"bmtp-script\src\main\resources\banks"目录下对应银行下的个性化脚本.

    ②若使用的数据库类型为oracle数据库
       ⅰ执行"bmtp-script\src\main\resources\v1.0.1\all\oracle"目录下最新日期的"xxx_init建库.sql”和“xxx_初始化数据.sql"全量脚本;
        ⅱ执行"bmtp-script\src\main\resources\increment\oracle"和"bmtp-script\src\main\resources\banks\标准初始化数据"目录下文件日期在执行的全量脚本文件日期之后的增量脚本文件;
        ⅲ执行“bmtp-script\src\main\resources\banks"目录下对应银行下的个性化脚本.

3、切换数据源和修改mapper文件扫描路径

记录一次mysql数据库迁移到oracle的详细过程

4、修改mapper文件，兼容oracle

 1)插入方法修改，插入后返回最大id

记录一次mysql数据库迁移到oracle的详细过程

2）批量更新方法修改，增加begin和end分隔

记录一次mysql数据库迁移到oracle的详细过程

 

3）批量插入方法修改

①增加获取下个序列的函数，注意此处如使用sql直接取序列，执行报错

--/

create or replace function get_seq_next (seq_name in varchar2) return number

is

seq_val number ;

begin

execute immediate 'select ' || seq_name || '.nextval from dual' into seq_val ;

return seq_val ;

end get_seq_next;

/

②修改mapper语法

insert into (clonums)  select  id,values from  dual union all select  id,values from  dual

记录一次mysql数据库迁移到oracle的详细过程

 

4）语法方言修改

①递归查询语法修改

SELECT r.* from (

SELECT

@r AS _code,

( SELECT @r := PARENT_CODE FROM eclp_region WHERE code = _code ) AS parent_code

FROM

( SELECT @r := #{code}) vars,

eclp_region h WHERE @r != '1'

) t LEFT JOIN eclp_region r on r.code = t._code

---->

SELECT * FROM ECLP_REGION

START WITH code = #{code}

CONNECT BY PRIOR parent_code = code

②日期函数,日期计算修改

now()/sysdate() ---> sysdate 

Date(deal_time)   >= > DATE_SUB(now(),interval 510 day)

----> cast(deal_time As Date) <![CDATA[ >= ]]>   (SYSDATE-510)  

unix_timestamp(deal_time) --->trunc(deal_time)

③limit参数调整为rownum写法

select

<include refid="Base_Column_List"/>

from bm_discount_apply

where apply_status = '003'

order by gmt_modify desc limit #{number}

--->

select * from(

select

<include refid="Base_Column_List"/>

from BM_DISCOUNT_APPLY

where apply_status = '003'

order by gmt_modify desc

) where rownum <![CDATA[ <= ]]> #{number}

④group by语法调整为全分组或者不分组

Oracle在使用group by时，查询字段必须是分组的依据或聚合函数。

MySQL没有此限制，会自动取第一行。

所以mysql语法中要取的字段必须在oracle的分组中，所以需添加分组字段或删除分组

 

⑤cancat函数语法调整

cancat('%',#{param},'%') --->'%'||#{param}||'%'或者 concat(concat('%',#{param}),'%')

5、切换数据库到oracle，重新进行测试案例执行，修复缺陷

1）mysql varchar字段长度和oracle varchar存储汉字个数不同

数据库采用utf-8编码， mysql varchar采用字符存储，varchar（80）可存80个汉字

oracle varchar（80）可存26个汉字，所以所有oracle varchar字段拓展3倍长度，注意varchar2不得大于4000

可用以下脚本查询生成拓展长度脚本 

select  'ALTER TABLE ' ||t.table_NAME ||' modify ('||t.column_name|| ' '|| t.data_type || '('||to_char(t.data_length*3+5)||')'||');' from User_Tab_Columns t where t.data_type='VARCHAR2'；

2)mysql必输字段default '' not null ,oracle插入报错问题修复

前端加必输控制；

如为非必输字段，改为非必输；

 

总结Summary

 

通过这次迁移，我觉得有些一些经验可以用于多数据库兼容，一下是我的经验和分享，欢迎大家指教！

1、关于mybatis多数据库项目需要兼容多种库时，日期函数尽量使用拦截器统一输入，日期计算和格式化等处理尽量放在代码层

2、分页函数尽量由框架层提供

3、id生成尽量统一由redis产生或使用UUID的方案

4、多种数据库兼容时建表字段需要统一规范，避免切换数据库不兼容

5、对于批量插入和批量更新等操作尽量程序中循环调用，避免直接使用sql处理

6、禁止使用方言，无法处理时使用dbId的方式写不同数据库对应的sql