功能实现篇(数据库) ---Oracle转MySQL

目录

需求描述

表结构和数据转移

表产生问题及解决方案

字段默认值处理

查看表和列详情语句

Oracle中常用函数的改写

触发器迁移

创建触发器范例

Oracle转MySQL触发器脚本

检查触发器是否漏写

函数和存储过程迁移

创建函数范例

创建存储过程范例

视图迁移

 创建视图范例

序列迁移

MySQL实现Oracle序列

数据库备份

发到客户那遇到的问题及解决

总结


需求描述

    由于客户需要,需要将项目原本用的Oracle数据库转为MySQL,项目涉及到和数据库相关的也都需要进行相应的修改

    本文主要是记录将Oracle数据库中的数据转为MySQL数据库的数据的过程。


表结构和数据转移

    表结构和数据的转移是最基本的,这里使用到的工具是Navicat Premiium,可以使用其自带的工具进行数据传输

    首先分别简历Oracle的数据库连接和MySQL的数据库连接,然后再用工具进行数据传输,如下图

    从上面可以看到由于数据库不同,所以只能进行表之间的数据传输,而视图,函数,存储,触发器等由于语法不同是无法进行直接传输的,因此这些不能直接传输的就需要进行修改,根据MySQL语法要求进行改写、创建,下面会详细讲。

表产生问题及解决方案

  • 由于MySQL中VARCHAR类型的长度最大为255,因此在传输时如果Oracle那边字段长度很大的话会导致报错

    解决方案:根据实际使用中如果不需要这么大的长度可以讲Oracle的对应长度改小点,或者使用其它类型,如TEXT类型

  • 迁移过程中如果报下面这种错,是因为MySQL限制了每次执行插入操作的最大值,需要的话可以将最大值进行修改
1153 - Got a packet bigger than 'max_allowed_packet' bytes With statement

    解决方案:使用MySQL命令窗口执行以下内容来修改信息报的大小,如增大到16M

set global max_allowed_packet = 1024*1024*16
  • 其它类型的报错的话可以根据报错提示进行修改,在此就不一一列举了。
  • 在创建数据库时如果默认排序规则选择了 utf8_general_ci 这种,则在查询和导入时会不区分大小写,导致插入时会因为重复而报错,在查询时会返回多行而报错,如果不注意的话造成的结果会和预期不符,以及导致系统相应功能模块崩溃。

    解决方案:可以使用 utf8_bin 这种排序规则,这样就会大小写敏感,区分大小写了。

  • 在迁移过程中默认值会丢失,需要手动再给赋个默认值,MySQL默认值只支持默认字段类型的值,不支持函数等

    解决方案:为了确保批量修改导致表出错而崩溃,这里选择在Oracle中得到相关数据后在MySQL中进行手动修改

字段默认值处理

    以下脚本是获取表的字段的默认值信息,根据需要可以进行相关修改

-- 以下脚本是用来给MySQL数据库的表字段赋默认值的

BEGIN

  DBMS_OUTPUT.ENABLE(10000000000);
  FOR ITEM IN (SELECT T.COLUMN_NAME,
                      T.TABLE_NAME,
                      T.DEFAULT_LENGTH,
                      T.DATA_DEFAULT,
                      T.DATA_TYPE
                 FROM USER_TAB_COLUMNS T
                WHERE T.DEFAULT_LENGTH IS NOT NULL) LOOP
    IF ITEM.DATA_TYPE = 'NUMBER' OR ITEM.DATA_TYPE = 'FLOAT' THEN
      DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
                           ' alter column ' || ITEM.COLUMN_NAME ||
                           ' set default ' || ITEM.DATA_DEFAULT || ';');
    ELSIF (ITEM.DATA_TYPE = 'DATE') THEN
      IF INSTR(ITEM.COLUMN_NAME, 'LASTMODIFIEDON') > 0 THEN
        DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
                             ' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
                             ' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT "最后修改时间";');
      ELSIF INSTR(ITEM.COLUMN_NAME, 'CREATEDON') > 0 THEN
        DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
                             ' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
                             ' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间";');
      ELSE
        DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
                             ' MODIFY COLUMN ' || ITEM.COLUMN_NAME ||
                             ' datetime(0) NULL DEFAULT CURRENT_TIMESTAMP;');
      END IF;
    ELSE
      DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || ITEM.TABLE_NAME ||
                           ' alter column ' || ITEM.COLUMN_NAME ||
                           ' set default ' || ITEM.DATA_DEFAULT || ';');
    END IF;
  
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('');
END;

查看表和列详情语句

-- Oracle 查询语句
SELECT T.* FROM USER_TABLES T;
SELECT T.* FROM USER_TAB_COLUMNS T;

-- MySQL 查询语句
SELECT *
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'base table'
   AND TABLE_SCHEMA = '数据库名'
 ORDER BY TABLE_NAME ASC;
 
SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = '数据库名'

Oracle中常用函数的改写

    有些在Oracle中能直接使用的函数,在MySQL中没有或者使用方式不一样,为了使原有SQL语句正常执行,则需要在MySQL

    中进行改写、创建,大多数常用函数MySQL中也都有,只是使用方法不一样,可以创建个函数直接调用MySQL的函数就行,

    其它特殊的函数则需要特殊处理,比如to_char,to_date,to_number等,由于MySQL中不支持函数的重载,所需要注意传参和

    返回值类型,可以进行 case when 进行判断然后做相应的处理,还可以抛个异常方便及时处理,以保证函数的通用性健壮性

    MySQL中不支持 || 进行字符串拼接,需要改成CONCAT()函数进行拼接,一个可拼接多个字符串,用 ,分割,当然为了保证

    SQL语句的通用性,可以两个两个字符串拼接,使用多个CONCAT()进行拼接

    对于DECODE() 函数,这个没有好的解决办法,创建这个函数的在实际使用中也达不到要求,因此都改成了 case when 语法

触发器迁移

  • MySQL不支持CREATE OR REPLACE TARIGGER "XXX",可以通过 DROP TRIGGER IF EXISTS XXX 来创建
  • MySQL触发器的触发条件一次只能有一个,不像Oracle中那样可以 BEFORE INSERT OR UPDATE OR DELETE ON XXX
  • MySQL中的 new 和 old 用法是 new.xxxold.xxx ,和Oracle的区别是前面不加
  • 字段引号要用左上角~这个键的`号,而不是英文的单引号,不然会提示1064错误
  • 进行更新操作后的新老数据赋值不能直接使用 = 号,需要使用 SET 进行赋值,如:SET new.xxx = 'xxx';

创建触发器范例

DELIMITER $$
DROP TRIGGER IF EXISTS TRG_SYNCROLERIGHT_DELETE $$

CREATE TRIGGER TRG_SYNCROLERIGHT_DELETE
  BEFORE DELETE ON TSYS_ROLE_RIGHT
  FOR EACH ROW
BEGIN
DECLARE V_GROUPID VARCHAR(64);
    SELECT P.L_GROUPID
      INTO V_GROUPID
      FROM HSI_GROUP P
       WHERE P.C_GROUPCODE = OLD.ROLE_CODE;
    IF OLD.RIGHT_FLAG <> 2 THEN
      DELETE FROM HSI_GROUPRIGHT
       WHERE C_SYSNAME = 'XXX'
         AND L_GROUPID = V_GROUPID
         AND C_RIGHTCODE = OLD.SUB_TRANS_CODE;
    END IF;
END $$
DELIMITER ;

Oracle转MySQL触发器脚本

DECLARE

  -- 输入触发器的拥有者即可获得MySQL创建触发器所用的脚本,仅供简单的触发器做参考,复杂的还是需要手动调整
  TRIGGER_OWNER VARCHAR(1000) := 'ATS';

  TRIGGER_NAME VARCHAR2(64);

  SQLT CLOB;

  ERRORTEXT VARCHAR2(200);

BEGIN

  DBMS_OUTPUT.ENABLE(1000000000);

  FOR ITEM IN (SELECT T.TRIGGER_NAME
                 FROM ALL_TRIGGERS T
                WHERE T.OWNER = TRIGGER_OWNER
                GROUP BY T.TRIGGER_NAME
                ORDER BY T.TRIGGER_NAME DESC) LOOP
  
    DBMS_OUTPUT.PUT_LINE('-- ' || ITEM.TRIGGER_NAME);
  
    BEGIN
      SELECT ('CREATE ' || REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((LISTAGG(TEXT,
                                                                                                    '*')
                                                                                            WITHIN
                                                                                            GROUP(ORDER BY LINE)),
                                                                                           '"',
                                                                                           ''),
                                                                                   'ATS.',
                                                                                   ''),
                                                                           'declare',
                                                                           ''),
                                                                   'IF UPDATING THEN',
                                                                   ''),
                                                           ':',
                                                           ''),
                                                   'end if;',
                                                   ''),
                                           CHR(10),
                                           ''),
                                   '*',
                                   '')) TEXT
        INTO SQLT
        FROM USER_SOURCE
       WHERE NAME = ITEM.TRIGGER_NAME;
    
      DBMS_OUTPUT.PUT_LINE(SQLT);
      DBMS_OUTPUT.PUT_LINE('');
    EXCEPTION
    
      WHEN OTHERS THEN
      
        ERRORTEXT := SUBSTR(SQLERRM, 1);
      
        DBMS_OUTPUT.PUT_LINE(ERRORTEXT);
    END;
  
  END LOOP;

END;

检查触发器是否漏写

-- MySQL中执行
SELECT T.TRIGGER_NAME
  FROM INFORMATION_SCHEMA. TRIGGERS AS T
 WHERE T.TRIGGER_SCHEMA = '数据库名称';
 
 -- 将上面结果复制到下面即可查看有哪些触发器没有创建 --
 
-- Oracle中执行
 SELECT T.TRIGGER_NAME
  FROM ALL_TRIGGERS T
 WHERE T.OWNER = 'ATS'
   AND NOT INSTR('上面的查询结果', T.TRIGGER_NAME) > 0;

函数和存储过程迁移

  • 同样没有 CREATE OR REPLACE 语法,可以使用 DROP FUNCTION/PROCEDURE IF EXISTS XXX
  • MySQL中的 DECLARE 变量的定义是在 BEGIN END; 里面
  • 赋值需要执行 SET INTO
  • 返回值的关键字是 RETURNS 而不是 RETURN
  • MySQL中游标(CRUSON)的定义和使用和Oracle略有不同
  • 循环语法也有所不同,可以根据需求选择使用不同的循环方式
  • MySQL中没有 PackagesTypes,根据需求进行改写

创建函数范例

DELIMITER $$
DROP FUNCTION IF EXISTS f_getdictionarycaption$$

CREATE FUNCTION f_getdictionarycaption (
	var1 VARCHAR (20),
	var2 VARCHAR (20)
) RETURNS VARCHAR (64)
BEGIN

	DECLARE result VARCHAR (64) ;

	IF var1 IS NULL OR var1 = '' THEN
		SET result = '' ;
	ELSE
		SELECT xxx INTO result	FROM DUAL ;
	END IF ; 

	RETURN result ; 
END$$
DELIMITER ;

创建存储过程范例

DELIMITER $$
DROP PROCEDURE IF EXISTS WF_SP_INITDATA$$

CREATE PROCEDURE WF_SP_INITDATA (
	p_table_name VARCHAR (64),
	p_condition VARCHAR (64),
	p_cols VARCHAR (64),
	p_values VARCHAR (64)
) 

label : BEGIN

DECLARE v_l_rowcount INT ;
DECLARE v_vc_table_name VARCHAR (255) ;
DECLARE v_vc_condition VARCHAR (255) ;
DECLARE v_vc_values text ;
DECLARE v_sql text ;

SET v_vc_table_name = p_table_name ;
SET v_vc_condition = p_condition ;
SET v_vc_values = p_values ;

IF v_vc_table_name IS NULL OR v_vc_table_name = '' THEN
	SELECT '表名必须输入' FROM DUAL ; 
	LEAVE label ;
END IF ;

IF v_vc_condition IS NULL OR v_vc_condition = '' THEN
	SELECT '插入数据的条件不能为空' FROM DUAL ; 
	LEAVE label ;
END IF ;

IF v_vc_values IS NULL OR v_vc_values = '' THEN
	SELECT '插入数据的值不能为空' FROM DUAL ;
  LEAVE label ;
END IF ; 

/*判断表是否已经存在*/
SELECT count(1) v_l_rowcount FROM information_schema.tables WHERE table_name = upper(p_table_name) ;
IF v_l_rowcount = 0 THEN
	SELECT CONCAT( '表 ', p_table_name, ' 不存在!' ) FROM DUAL ; 
  LEAVE label ;
END IF ; 

/*判断记录是否已经存在*/
SET v_sql = CONCAT( 'select count(1) from ', v_vc_table_name, ' where ', v_vc_condition ) ; 
SELECT v_sql INTO v_l_rowcount ;

/* 不存在才插入数据*/
IF v_l_rowcount = 0 THEN
  SET v_sql = CONCAT( 'insert into ', v_vc_table_name, p_cols, ' values ', v_vc_values ) ; 
	SELECT v_sql ;
END IF ; 

END$$
DELIMITER ;

视图迁移

  • 视图是可以使用 CREATE OR REPLACE VIEW XXX 语法的
  • MySQL不像Oracle那样支持from子查询,但可以将子查询再建个视图后进行关联使用,需要注意创建的先后顺序
  • 视图中包含 union 的话不要在 as 后加括号
  • MySQL中 Union 的子句不支持 order by
  • MySQL能用 GROUP_CONCAT(xxx SEPARATOR',') 进行行转列

 创建视图范例

create or replace view bs_bank_vl_sub as
select b.urid as bankcode,
               b.name as bankname,
               b.urid as banklevelcode,
               b.urid as parentbanklevelcode,
               '1' as banklevleid,
               '总行' as banklevel,
               b.code as directcode

          from t_banks b
        union
        select tb.urid as bankcode,
               tb.name as bankname,
               tb.code as banklevlelcode,
               b.urid as parentbanklevelcode,
               '2' as banklevelid,
               '分行' as banklevel,
               b.code as directcode
        from t_banklocations tb, t_banks b
         where tb.bankid = b.urid  

序列迁移

  • MySQL中没有序列,有的主键可以用自增长来处理,前提是要知道哪些表的主键可以设为自增长
  • 如果无法设置自增长的话可以模仿Oracle,通过使用序列来实现
  • 可以创建个序列表,分别存着Oracle中的序列名称,当前值,增长值,在创建使用的函数

    为了方便创建序列和初始化数据,可以将下面查询结果导出到excel中,在导入到MySQL中创建的sequence表中,很方便

-- 查询Oracle序列信息
select t.sequence_name,t.last_number,t.increment_by from user_sequences t;

 

MySQL实现Oracle序列

-- 创建表(序列)
drop table if exists sequence;  
create table sequence (  
  seq_name    VARCHAR(50) NOT NULL, -- 序列名称  
  current_value   INT     NOT NULL, -- 当前值  
  increment_value  INT     NOT NULL  DEFAULT 1 -- 步长(跨度)  
);

-- 实现currval的模拟方案 函数使用为:select currval('MovieSeq'); 
DELIMITER $$
DROP FUNCTION IF EXISTS currval $$
create function currval(v_seq_name VARCHAR(50))  
returns integer 
begin 
  declare value integer;  
  set value = 0;  
  select current_value into value  
  from sequence 
  where seq_name = v_seq_name;  
  return value;  
END $$
DELIMITER;

-- 实现nextval的模拟方案 函数使用为:select nextval('MovieSeq');  
DELIMITER $$
DROP FUNCTION IF EXISTS nextval $$
create function nextval (v_seq_name VARCHAR(50))  
returns integer 
begin 
 update sequence 
 set current_value = current_value + increment_value
 where seq_name = v_seq_name;  
 return currval(v_seq_name);  
END $$
DELIMITER ;

-- 增加设置值的函数
DELIMITER $$
DROP FUNCTION IF EXISTS setval $$
create function setval(v_seq_name VARCHAR(50), v_new_value INTEGER)  
returns integer 
begin 
 update sequence 
 set current_value = v_new_value  
 where seq_name = v_seq_name;  
return currval(seq_name);
END $$
DELIMITER ;

数据库备份

    为了避免数据库产生的未知问题或操作而导致无法挽回的结果,可以在测试好后备份一下数据,可以通过导出SQL文件进行

    备份,也可以使用工具自带的备份功能进行备份,如下图

发到客户那遇到的问题及解决

  • 权限问题,由于导出的总的脚本里面包含用户信息(我使用的是root用户),所以会导致客户那边用户不同会报错及权限问题

    解决方式:将导出的脚本中的视图、函数、存储、触发器中的用户信息的sql片段去掉,可以批量替换成空格

  • 视图问题,上面也说了,由于MySQL中不支持from子查询,需要将子查询再创建个子视图,但导出语句中视图的顺序是不同的,导致子视图在后面的话,创建父视图会找不到而报错

    解决方式:手动将脚本中视图那块的语句改为普通的创建语句,并调整好顺序,将子视图放父视图前面,以免报错而中止导入

总结

    本次数据库迁移中遇到的问题及解决让我对Oracle和MySQL的不同有了进一步的了解,以后使用MySQL的企业和用户应该会越来越多,此文章记录下这个过程可以为之后类似的工作做个参考,记录并未涉及全部内容,后续有需要的话可以再新增修改。

附笔记链接Oracle转MySQL记录

©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页