Mysql遷移到Oracle簡單記錄

標籤：簡單記錄

因業務的擴充和客戶的要求，公司需要將原運行於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 isBEGIN      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 isBEGIN        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  替代。

本文出自 “LIBYDWEI” 部落格，請務必保留此出處http://libydwei.blog.51cto.com/37541/1772506

Mysql遷移到Oracle簡單記錄