oracle到mysql的迁移步骤及各种注意事项

最近公司一个项目需要将数据库进行一次迁移，从oracle到mysql，网上资料甚少，现将我本次迁移过程中所遇到的一些问题总结于此(主要是存储过程的迁移)，希望能给自己做一个日后的参考，如果有幸能帮助到大家更好。

Mysql的安装过程中需要注意选择字符集，这里选择用utf8

-- mysql中没有包的概念，因此迁移的时候将存储过程命名为'包名.存储过程名'的格式

l   mysql存储过程格式：

DELIMITER $$   -- 分隔符

-- CREATE PROCEDURE([[IN |OUT ] 参数名 数据类型...])，IN和OUT写在最前面，其中IN可以省略

CREATE PROCEDURE `pkg_ypgl.prc_ypsc`(

 prm_ypbm VARCHAR (20),  

  OUT prm_AppCode VARCHAR (20),

  -- 程序执行代码

  OUT prm_ErrorMsg VARCHAR (100)

  -- 程序执行错误信息

)

BEGIN

  /*变量定义*/

  DECLARE n_count DECIMAL (8) ;

 DECLARE done INT(10);

 

  /*设置游标结束标志*/

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  -- 如果NOT FOUND，取不到值，则将done赋值1，并且程序继续执行

SET done=0;

 

/*定义一个区块lavel_error，逻辑错误处理*/

label_error :  BEGIN

  /*定义游标*/

 DECLARE cur_bdjl CURSOR FOR

  SELECT .....

   /*打开游标*/

   OPEN cur_bdjl ;

   REPEAT

     FETCH cur_bdjl INTO v_aaz001....

IF NOT done THEN -- 如果结束标志done为0则继续循环

........

END IF;

   /*结束循环，关闭游标*/ 

     UNTIL done -- 直到NOT FOUND

   END REPEAT ;

   CLOSE cur_bdjl ;

   

   SET prm_AppCode = 'noerror' ;  -- 将prm_AppCode设为正确

   SET prm_ErrorMsg = '' ;

  END;  -- 结束区块label_error

 

  END$$

DELIMITER ;

 

 

 

 

l   数据类型：

Oracle:varchar2    Mysql:varchar(20)  (参数自设)

Oracle:number()    Mysql:decimal()   -- 参数与oracle中一样

Oracle:date       Mysql:datetime  -- mysql的datetime存储的数据都精确到秒’yyyy-mm-dd hh:mm:ss’

 

l   定义变量：

Mysql需要在每句前面加DECLARE

 

l   给变量赋值：

Oracle:v_string := ‘asdas’;    Mysql: SET string :=‘asdas’; （等号前面的冒号可以有也可以没有）

 

l   异常处理：

Oracle:EXCEPTION WHEN OTHERS THEN….

Mysql: DECLARE { EXIT | CONTINUE } HANDLERFOR { error-number | {SQLSTATE error-string } | condition } SQL statement;

   SQLWARNING 代表所有以01开头的错误代码 

   NOT FOUND 代表所有以02开头的错误代码，也包括游标结束的时候

   SQLEXCEPTION 代表除了SQLWARNING和NOT FOUND 的所有错误代码

eg. DECLARE EXIT HANDLER FORSQLEXCEPTION,SQLWARNING,NOT FOUND SETa = 1;

注：一个begin....end里面只能声明一个HANDLER，EXIT表示遇到这种异常时就执行SET a = 1然后结束这个存储过程，CONTINUE表示遇到这种异常时就SET a = 1，然后继续执行之后的存储过程

 

l   跳转：

Oracle:  GOTO label_error;

            …..

<<label_error >>

Mysql:初始化错误代码prm_AppCode为“错误”，定义一个区块label_error，在区块的最后将prm_AppCodeset为’noerror’，中间触发条件，将GOTOlabel_error;改写成leavelabel_error;跳出区块

 

 

l   游标：

Mysql只有静态游标，没有动态游标，用存储过程代替

定义游标的语句为DECLAREcur_bdjl CURSOR FOR …..

Mysql不支持rec_curname.aaz001这种写法，所以必须将游标取得的所有字段FETCHINTO 到变量里

 

 

l   循环：

Mysql里有三种循环方式

(1).WHILE循环

 WHILE  expression DO

 statements

 END   WHILE;

 (2).LOOP循环

 LOOP

  statements

  END  LOOP;

 (3).REPEAT UNTIL循环

 REPEAT

 statements

 UNTIL expression

 END  REPEAT;

 

 

l   序列：

Mysql中没有序列，用函数+表的方法取代.

建表语句：

CREATE TABLE `seq` (
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '序列号生成器名称',
  `val` bigint(20) unsigned NOT NULL COMMENT '序列号',
  `increment` int(4) DEFAULT '1' COMMENT '序列的增量',
  `min` bigint(20) DEFAULT NULL COMMENT '序列最小值',
  `max` bigint(20) DEFAULT NULL COMMENT '序列最大值',
  `cycle` char(1) DEFAULT 'N' COMMENT '是否循环',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mysql模拟序列号生成器用表'

 

函数如下：

DELIMITER $$

DROP FUNCTION IF EXISTS `seq`$$

CREATE FUNCTION `seq`(seq_name VARCHAR(20))RETURNS BIGINT(20)

BEGIN

DECLARE v_value BIGINT(20);

DECLARE v_CYCLE CHAR;

DECLARE v_MIN BIGINT(20);

DECLARE v_MAX BIGINT(20);

SELECT a.val,a.MIN,a.MAX,a.CYCLE INTOv_value,v_MIN,v_MAX,v_CYCLEFROM seq a WHERE NAME = seq_name;

   IFv_CYCLE = 'Y' AND v_value = v_MAX THEN -- 该序列为循环且当前值为其最大值

  UPDATE seq  -- 将当前值设为 v_MIN  

  SET   val = v_MIN  

  WHERE NAME = seq_name; 

 ELSE

  UPDATE seq  -- 否则将当前值设为val + increment  

  SET   val = val + increment  

  WHERE NAME = seq_name;

  END IF;

  SELECT  val INTO v_value FROM seqWHERE NAME =seq_name;

   RETURN v_value;

END$$

DELIMITER ;

 

 

l   更新：

Oracle: UPDATE TABLE T SET (A,B,C) = (SELECT A,B,C FROMTABLE_2 K WHERE K.Y =T.Y) WHERE T.X = V_X;

Mysql: UPDATE TABLE T,TABLE_2 K SET T.A =K.A,T.B=K.B,T.C=K.CWHERE  T.Y = K.Y AND T.X = V_X;

特别注意：oracle中update的set里的子查询如果涉及到要update的表，在mysql中需要修改

eg:  Oracle中:

UPDATEaac001 a SET a.id = (SELECT id FROM aab002 WHERE member = a.member); -- 子查询引用a.member

Mysql中: UPDATEaac001 a, aab002 b SET a.id = b.id WHERE a.member = b.member;

 

l   删除

同理子查询如果涉及到要delete的表，在mysql中需要修改

Eg.Oracle里：

DELETEFROM ad54a8 a

WHEREa.yae374 =

  (SELECT

    b.yae374

  FROM

    ad53a3 b,

    ad54a8 a

  WHERE a.yae374 = b.yae374

    AND b.aae100 = '1'

AND b.yab109 LIKE CONCAT('01010101','%'))

Mysql里：

DELETEFROM ad54a8 a

WHEREa.yae374 =

(SELECT  yae374  FROM (SELECT

    b.yae374

  FROM

    ad53a3 b,

    ad54a8 a

  WHERE a.yae374 = b.yae374

    AND b.aae100 = '1'

AND b.yab109 LIKE CONCAT('01010101','%')) t );

 

 

l   GROUPBY：

mysql的groupby 语句可以select 没有被分组的字段，如

select id,name,age from A group by age

这个取出的id,name所在的行是每个分组中的第一行数据

 

l   调用：

Mysql: call procedure_name(所有参数);

 Eg: call `pkg_jsgl_prc_grbj` (‘111’,’1231’,’2013-05-17’,@aaz001,@code,@mes);

Select @aaz001,@code,@mes;

 

l   跳出循环：

Oracle: EXIT;

Mysql: 将循环的内容定义为一个区块label_loop，需要跳出循环时则 leavelabel_loop;

 

 

l   注释：

1、#注释内容

2、--注释内容  注意-- 后需要加一个空格

3、块注释用/*注释内容*/

 

 

l   表的注释：

在oracle中执行如下语句：

select 'alter table '||table_name||'comment'||' '''||COMMENTS||''''||';' from USER_TAB_COMMENTS where comments is not null;

将得到的结果放到mysql中执行即可添加表名的注释

 

 

l   表的字段注释：

在oracle中执行如下语句：

select distinct(data_type)  FROM all_tab_columns where owner= 'YDMIS'

将查询出的本次转换涉及到的数据类型用decode函数转换为Mysql中对应函数(参数)的形式，如将CHAR转换为CHAR(20)，参数的值在all_tab_columns的DATA_LENGTH DATA_PRECISION  DATA_SCALE中取得。

在oracle中执行如下语句，注意decode函数里的参数需根据上一步的查询结果转换：

select 'alter table '||a.table_name||'modify column '||a.column_name||''||decode(b.data_type,'VARCHAR2','VARCHAR('||b.DATA_LENGTH||')','DATE','DATETIME','NUMBER','DECIMAL('||b.DATA_PRECISION||','||b.DATA_SCALE||')','CHAR','CHAR('||b.DATA_LENGTH||')','LONGRAW','mediumblob')||'comment '||''''||comments||''''||';'

from user_col_commentsa,all_tab_columnsb

where a.comments is not null

and a.table_name = b.table_name

and a.column_name = b.column_name

and b.owner = 'YDMIS';

将得到的结果放到mysql中执行即可添加表字段的注释

 

l  Rownum:

Mysql用limitm,n来代替, LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last
//如果只给定一个参数，它表示返回最大的记录行数目： 
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n

Eg. Oracle中  select a.id from a,b where a.name = b.nameand rownum = 1;

Mysql中  select a.id from a,b where a.name = b.namelimit 1;

 

l  ASCII():

Oracle默认字符集为ZHS16GBK，如果mysql设置的字符集与oracle不同，要想得到与中一样的ascii码值，用以下函数：

DELIMITER $$

DROP FUNCTION IF EXISTS `fun_ascii`$$

CREATE DEFINER=`root`@`%` FUNCTION `fun_ascii`(str VARCHAR (8))RETURNS VARCHAR(15) CHARSET utf8

BEGIN

SELECT CONV(HEX(LEFT(CONVERT(str USING gbk),1)),16,10) INTO@asciiValue;

    RETURN @asciiValue;

 END$$

DELIMITER ;

 

函数：

功能	oracle	mysql	备注	eg.oracle	eg.mysql
连接字符串	||	concat()	'a'||'b'||'c'	concat( 'a','b','c')
将其他格式转换为字符串		concat(x,'')
截取字符串	substr()	substring()	substr('abcd',1,3)	substring('abcd',1,3)
string转换为date	to_date()	str_to_date(str, format)	to_date(aae036,'yyyy-mm-dd hh24:mi:ss')	str_to_date(aae036,'%Y-%m-%d %H:%i:%s')
获取当前日期	sysdate	now(),sysdate(),current_date	now()返回的是程序开始执行时的时间，sysdate()返回实时时间， 一般用now() current_date表示当前的年月日
取出日期的指定部分	date_format(date,type)	%Y：年 %c：月 %d：日 %H：小时 %i：分钟 %s：秒	date_format(now(),'%Y-%c-%d %h:%i:%s')
增加一天	sysdate+1	DATE_ADD(date,INTERVAL expr type)	sysdate+1	DATE_ADD(now() ,INTERVAL 1 DAY)
类型转换	TO_CHAR  TO_DATE  TO_NUMBER	cast(xxx as type)	type:二进制 : BINARY 字符型,可带参数 : CHAR() 日期 : DATE 时间: TIME 日期时间型 : DATETIME 浮点数 : DECIMAL 整数 : SIGNED 无符号整数 : UNSIGNED	to_char(33)	cast(33 as char(2))
精度转换	to_number(x,type)	round(x,d)	保留到小数点后d位，而第d位的保留方式为四舍五入。若要保留x值小数点左边的d位，可将d设为负值
替换空值	NVL( string1, replace_with)	ifnull(string1, replace_with)
decode()	decode (expression, search_1, result_1,  default)	case expression when search_1 then result_1 else default end