因业务的扩展和客户的要求,公司需要将原运行于Mysql上的数据迁移至Oracle。

参加工作的最初几年短暂接触过Mysql,但不经常使用,最初的印象已经所剩无几了。

本文主要记录在数据库迁移过程中,遇到相关问题的解决办法。主要集中于数据类型的选择、部分函数的替代以及其他数据库之间差异的问题。

对于存在相同需求的同学,能够有一点点帮助,我会感到很欣慰 : )

 

1.数据类型的选择。

数值类型:

Mysql有两种类型的数字:整数(whole number)和实数(real number).


存储整数可以选择:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT  ,分别对应8,16,24,32,64位存储空间。他们可存储的范围从-2的N-1次方到2的N-1次方-1,其中N是存储空间的位数。

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT  UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128-127.


实数是带有小数部分的数字。DECIMAL类型用于存储精确的小数。因为额外的计算和开销,应该尽量只是在对小数进行精确计算时才使用。在数据量比较大时,可以考虑用bigint代替DECIMAL。

 

相比较而言,Oracle有number,int,float,binary_float,binary_double,numeric类型


number类型在oracle中会占用0~22个字节的存储空间,是一种变长数据类型,采用oracle内部算法,是一种软数据类型,因此具有较好的数据精确度,通用性和可移植性较强


其中如下的这些类型都是从number类型映射而来。

NUMERIC(p,s):完全映射至NUMBER(p,s)。如果p未指定,则默认为38.

DECIMAL(p,s)或DEC(p,s):完全映射至NUMBER(p,s)。如果p为指定,则默认为38

INTEGER或INT:完全映射至NUMBER(38)类型。

SMALLINT:完全映射至NUMBER(38)类型

由于存储结构上的不同,BINARY_FLOAT和BINARY_DOUBLE较number,可以存储更大范围的数据,但是其精度并不如number。如果存储金融数据,建议使用number。

而如果进行科学运算,建议使用BINARY_FLOAT和BINARY_DOUBLE,因为浮点型数据使用的是硬件计算,其计算效率是非常高的。


对于Mysql中的各种int类型,建议统一都对应到Oracle的number类型。(number(p,s)也是完全映射到number类型的,所以无需设置精度)


字符类型:

Mysql使用VARCHAR和CHAR两种类型。

VARCHAR是变长类型

CHAR是定长类型

CHAR这类定长类型,会删除所有末尾的空格,在数据存储和比较的时候,某些行为就难以理解。所以正常选择VARCHAR为好。

 

Oracle中char属于定长类型会使用空格进行填充。

而varchar2采用变长的方式存储数据,相对会节省空间。在存储效率上,与char不相上下。

另外char类型同样存在末尾空格的问题。

对于Oracle类型,由于工作习惯以及存储的要求来考虑,应该尽可能的选择varchar2。

 

2.Mysql中delimiter的作用


该关键字是告诉解释器,该段命令是否已经结束,mysql是否可以执行后续脚本。

DELIMITER ;
DROP PROCEDURE IF EXISTS p_contract;  --该语句可以立即执行
DELIMITER $$    -- 并没有以分号结束,后续语句等待遇到$$时执行。
CREATE PROCEDURE p_contract()
    BEGIN
。。。。。。
。。。。。
    END $$  -- 执行中间的语句。
DELIMITER ;

3.DECLARE CONTINUE HANDLER FOR NOT FOUND


若没有数据返回,程序继续,并将变量IS_FOUND设为0 ,这种情况是出现在select XX into XXX from  tablename的时候发生的。


4.UNIX时间戳与日期的相互转换


Mysql中日期以及时间函数,推荐你可以看一下这篇文章:http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

 

Oracle获取UTC时间:

select to_char(sys_extract_utc(systimestamp),'yyyy-mm-dd hh24:mi:ss') from  dual;     --UTC时间

 

mysql中UNIX时间戳与日期的相互转换

UNIX时间戳转换为日期用函数:FROM_UNIXTIME()


select FROM_UNIXTIME(1410318106);

日期转换为UNIX时间戳用函数:UNIX_TIMESTAMP()

select UNIX_TIMESTAMP('2014-09-10 11:01:46');

where DATE_FORMAT(FROM_UNIXTIME('1410318106','%Y-%m-%d %h:%m:%s'),'%Y-%m-%d  %h:%m:%s')

Oracle并没有这类的转换函数,需要自己写(如下的写法也来自网络)

 

--Oracle时间Date型转换为Unix时间戳
create or replace function bill_query.oracle_to_unix(in_date DATE) return number is
BEGIN
   
   return( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600);
END oracle_to_unix;
/
--Unix时间戳转换为Oracle时间
create or replace function bill_query.unix_to_oracle(in_number NUMBER) return date is
BEGIN
    
    return(TO_DATE('19700101','yyyymmdd') + in_number/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24);
END unix_to_oracle;

/



5.Mysql中的索引和约束与Oracle的对应


primary key  ---> primary key

index ---> index

unique key –> unique index

key --->  index

 

7.AUTO_INCREMENT属性


Mysql 中字段存在AUTO_INCREMENT属性,Oracle中需要使用序列代替。

在用到该字段时,需要显式的调用,插入sequence_name.nextval

 

8.Mysql创建表时可以通过语句查询表是否存在并删除(drop database if  exists  table_name)


Oracle中可以通过自定义函数来实现。(函数中存在DDL语句时,不能通过select的方式调用,只能通过赋值的方式。这里可以考虑在存储过程中实现并调用会更方便一点。)

create or replace function hytpdtnmdb.fun_obj_ifexists(v_obj_name in  varchar2) return number  is
    num_tab   number;
    num_seq    number;
begin
      select count(1) into num_tab from all_objects where  OWNER||'.'||OBJECT_NAME = upper(v_obj_name) and OBJECT_TYPE='TABLE';
      if  num_tab = 1 then
         execute immediate 'drop table  '||v_obj_name;
         return(num_tab);
      end if;
      select count(1) into num_seq from all_objects where  OWNER||'.'||OBJECT_NAME = upper(v_obj_name) and OBJECT_TYPE='SEQUENCE';
       IF num_seq = 1 THEN
         EXECUTE IMMEDIATE 'drop SEQUENCE  '||v_obj_name;
         RETURN num_seq;
      END IF;
      return  -1;
end fun_obj_ifexists;

 

9.关于Oracle存储过程执行权限问题


在A用户,需要更新、删除、drop table 或 create table  B用户对象的情况。
即便给用户赋予DBA权限,在匿名存储过程或在command窗口,可以正常执行,在存储过程中会存在权限不足的情况。


通过网上查阅资料,通过添加AUTHID  CURRENT_USER。 以CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID  CURRENT_USER as的方式创建存储过程可以解决。
文章链接:http://blog.csdn.net/gavinloo/article/details/6869234

但偶尔仍然存在权限不足的问题,可以通过grant显式授权的方式解决。

 

10.部分函数的差别

  • Mysql中concat可以连接多个字符串。Oracle中只能连接两个字符。如果需要多个字符串的连接,仍然要使用'||'

  •   NUMTOYMINTERVAL ( n , 'char_expr' )
      char_expr:日期描述,可以是YEAR和MONTH;

  •   NUMTODSINTERVAL( n , 'char_expr' )

      char_expr:时间描述,可以是day、hour、minute、second;
     
      interval后面只能用数字

  •   正常情况下都可以使用 interval,但是当需要增加或减少的数值为变量时,只能用  

         numtodsinterval 和 NUMTOYMINTERVAL  替代。