MySQL與Oracle資料類型對應關係(表格形式)，mysqloracle

MySQL與Oracle資料類型對應關係(表格形式)，mysqloracle

MySQL與Oracle兩種資料庫在工作中，都是用的比較多的資料庫，由於MySQL與Oracle在資料類型上有部分差異，在我們遷移資料庫時，會遇上一定的麻煩，下面介紹MySQL與Oracle資料庫資料類型的對應關係。

一、常見資料類型在MySQL與Oracle資料庫中的表現形式

說明	mysql	oracle
變長字串	VARCHAR[0-65535] 定義長度預設按字元長度計算，如果是GBK編碼的漢字將佔用2個位元組	VARCHAR2[1-4000] VARCHAR是VARCHAR2的同義字 定義預設按位元組長度計算
整數	TINYINT(-128-127) SMALLINT(-32768-32767) MEDIUMINT(-8388608-8388607) INT(-2147483648-2147483647) BIGINT(-9223372036854775808-9223372036854775807)	無專用類型， TINYINT可以用NUMBER(3,0)代替 SMALLINT可以用NUMBER(5,0)代替 MEDUIMINT可以用NUMBER(7,0)代替   INT可以用NUMBER(10,0)代替   BIGINT可以用NUMBER(20,0)代替   ORACLE中有SMALLINT,INT,INTEGER類型，不過這是NUMBER(38,0)的同義字
數實值型別	DECIMAL[1-65[,0-30]] NUMERIC是DECIMAL的同義字	NUMBER 可表示數範圍： 1*10^-130至1*10^126 NUMBER([1-38][,-84-127])   DECIMAL、NUMERIC、DEC是NUMBER的同義字
浮點型	FLOAT(D,M)	oracle10g開始增加BINARY_FLOAT類型 10g以前無專用類型，可以用NUMBER代替 ORACLE中有FLOAT和REAL類型，不過這是NUMBER的同義字
雙精確度浮點型	DOUBLE(D,M)	oracle10g開始增加BINARY_DOUBLE類型 10g以前無專用類型，可以用NUMBER代替 ORACLE中有DOUBLE PRECISION類型，不過這是NUMBER的同義字
位類型	BIT(1-64)	無
日期類型	DATE，3位元組儲存，只儲存日期，沒有時間，支援範圍是[1000-01-01]至[9999-12-31] TIME，3位元組儲存，只儲存時間，沒有日期，支援範圍是[-838:59:59]至[838:59:59] DATETIME，佔8位元組儲存，可表示日期和時間，支援範圍是[1000-01-01 00:00:00]至[9999-12-31 23:59:59] TIMESTAMP，佔4位元組儲存，可表示日期和時間，範圍是[1970-01-01 00:00:00]至[2038-01-19 03:14:07]	DATE類型 7位元組儲存，可表示日期和時間，支援範圍是[-4712-01-01 00:00:00]至[9999-12-31 23:59:59]
高精度日期	5.6.4以前不支援小數秒精度 5.6.4開始TIME,DATETIME,TIMESTAMP支援，最多可以6位小數秒，也就是微秒層級	TIMESTAMP[0-9] 佔用空間7-11個位元組，當小數秒精度為0時與DATE類型相同，小數秒最高精度可達9位，也就是納精度
年份	YEAR，1位元組儲存，只儲存年份，支援範圍是[1901]至[2155]	無對應類型，可以用NUMBER(3,0)代替
定長字串	CHAR[0-255]，定義長度預設按字元長度計算，最大儲存255字元	CHAR[1-2000] 定義預設按位元組長度計算
無符號說明	支援，用於數實值型別	不支援
大字串，一般用於儲存文字檔或超大描述及備忘類資訊	TINYTEXT 最大支援255個位元組 TEXT最大支援65535個位元組 MEDIUMTEXT最大支援16MB個位元組 LONGTEXT最大支援4GB位元組 欄位不支援預設值	支援(CLOB) oracle10g以前最大支援4GB個位元組 oracle10g開始最大支援4GB個資料區塊，資料區塊大小為2KB-32KB oracle還有一個LONG類型，是早期的儲存大字串類型，最大支援2GB位元組,現已不推薦使用
二進位對象，一般用於隱藏檔或圖片資料	TINYBLOB 最大支援255個位元組 BLOB最大支援65535個位元組 MEDIUMBLOB最大支援16MB個位元組 LONGBLOB最大支援4GB位元組   欄位不支援預設值	支援(BLOB) oracle10g以前最大支援4GB個位元組   oracle10g開始最大支援4G個資料區塊，資料區塊大小為2KB-32KB oracle還有一個LONG RAW類型，是早期的儲存二進位類型，最大支援2GB位元組,現已不推薦使用
二進位資訊	BINARY(0-255)，定長 VARBINARY(0-65535)，變長	RAW(1-2000)
枚舉類型	ENUM(v1,v2,v3,...),最多65535個元素	不支援
集合類型	SET(v1,v2,v3,...)，最多64個元素	不支援
國際化字元集類型，較少使用	無，MYSQL可以對每個欄位指定字元編碼	支援 NCHAR(1-2000) NVARCHAR(1-4000) NCLOB
外部檔案指標類型	不支援	支援 檔案大小最大4GB 檔案名稱最長255字元
	不支援	支援
	不支援	支援
自動成長類型	支援 使用簡單	不支援 一般使用SEQUENCE解決，用法與自增類型差別較大，使用較複雜，但能實現非常靈活的應用，包括字元自增主鍵、全域主鍵等等
	不支援函數和運算式 TEXT和BLOB欄位類型不支援預設值	支援函數和運算式
	支援，例如，把emp表的id欄位順序放在name欄位後面： alter table emp modify column id varchar(20) after name;	不支援，只能重建表或欄位
虛擬欄位是一個邏輯欄位定義，其結果值通常是一個運算式，並在表中儲存物理值，不佔用空間，主要用於簡化查詢邏輯。比如有一個商品銷售表有單價和數量兩個欄位，那可以建一個虛擬欄位金額，其運算式=單價*數量	不支援	11g支援，例： create table sales (   id       number,   quantity number,   price    number,   amount   GENERATED always as (quantity*price) virtual );
	INNODB 最大1000個欄位 所有欄位總定義長度不能超過65535位元組 所有固定長度欄位的總長度不超過半個資料區塊大小(資料區塊大小一般為16K)	最大1000個欄位

二、MySQL與Oracle資料庫常見資料類型對應關係

編號	ORACLE	MYSQL	注釋
1	NUMBER	int / DECIMAL	DECIMAL就是NUMBER(10,2)這樣的結構INT就是是NUMBER(10)，表示整型； MYSQL有很多類int型，tinyint mediumint bigint等，不同的int寬度不一樣
2	Varchar2（n）	varchar(n)
3	Date	DATATIME	日期欄位的處理 MYSQL日期欄位分DATE和TIME兩種，ORACLE日期欄位只有DATE，包含年月日時分秒資訊，用當前資料庫的系統時間為 SYSDATE, 精確到秒，或者用字串轉換成日期型函數TO_DATE(‘2001-08-01','YYYY-MM-DD')年-月-日 24小時:分鐘:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()還有很多種日期格式, 可以參看ORACLE DOC.日期型欄位轉換成字串函數TO_CHAR(‘2001-08-01','YYYY-MM-DD HH24:MI:SS') 日期欄位的數學運算公式有很大的不同。MYSQL找到離目前時間7天用 DATE_FIELD_NAME ＞ SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到離目前時間7天用 DATE_FIELD_NAME ＞SYSDATE - 7; MYSQL中插入目前時間的幾個函數是：NOW()函數以`'YYYY-MM-DD HH:MM:SS'返回當前的日期時間，可以直接存到DATETIME欄位中。CURDATE()以'YYYY-MM-DD'的格式返回今天的日期，可以直接存到DATE欄位中。CURTIME()以'HH:MM:SS'的格式返回當前的時間，可以直接存到TIME欄位中。例：insert into tablename (fieldname) values (now()) 而oracle中目前時間是sysdate
4	INTEGER	int / INTEGER	Mysql中INTEGER等價於int
5	EXCEPTION	SQLEXCEPTION	詳見<<2009001-eService-O2MG.doc>>中2.5 Mysql異常處理
6	CONSTANT VARCHAR2(1)	mysql中沒有CONSTANT關鍵字	從ORACLE遷移到MYSQL,所有CONSTANT常量只能定義成變數
7	TYPE g_grp_cur IS REF CURSOR;	游標 : mysql中有替代方案	詳見<<2009001-eService-O2MG.doc>>中2.2 游標處理
8	TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;	數組: mysql中藉助暫存資料表處理 或者直接寫邏輯到相應的代碼中， 直接對集合中每個值進行相應的處理	詳見<<2009001-eService-O2MG.doc>>中2.4 數組處理
9	自動成長的序列	自動成長的資料類型	MYSQL有自動成長的資料類型，插入記錄時不用操作此欄位，會自動獲得資料值。ORACLE沒有自動成長的資料類型，需要建立一個自動成長的序號，插入記錄時要把序號的下一個值賦於此欄位。
10	NULL	NULL	Null 字元的處理 MYSQL的非空欄位也有空的內容，ORACLE裡定義了非空欄位就不容許有空的內容。按MYSQL的NOT NULL來定義ORACLE表結構, 導資料的時候會產生錯誤。因此導資料時要對Null 字元進行判斷，如果為NULL或Null 字元，需要把它改成一個空格的字串。

MySQL與Oracle 差異比較之基本文法

編號	類別	ORACLE	MYSQL	注釋
1	變數的聲明方式不同	li_index NUMBER := 0	DECLARE li_index INTEGER DEFAULT 0	1. mysql 使用DECLARE定義局部變數.     定義變數文法為:  DECLARE var_name[,...] type [DEFAULT value] 要給變數提供一個預設值，需要包含一個DEFAULT子句。值可以被指定為一個運算式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。
2	變數的賦值方式不同	lv_inputstr := iv_inputstr	SET lv_inputstr = iv_inputstr	1. oracle變數賦值使用:=      mysql 使用賦值使用set關鍵字. 將一個值賦給一個變數時使用"=".
3	跳出（退出）語句不同	EXIT;	LEAVE procedure name;	1. oracle: 如果exit語句在迴圈中就退出當前迴圈.如果exit語句不再迴圈中,就退出當前過程或方法.     Mysql: 如果leave語句後面跟的是預存程序名,則退出當前預存程序. 如果leave語句後面跟的是lable名. 則退出當前lable.
		while 條件 loop exit; end loop;	label_name:while 條件 do     leave label_name; end while label_name;
4	定義遊標	TYPE g_grp_cur IS REF CURSOR;	DECLARE cursor_name CURSOR FOR SELECT_statement;	oracle可以先定義遊標,然後給遊標賦值.  mysql定義遊標時就需要給遊標賦值. Mysql定義遊標出自 Mysql 5.1 參考手冊20.2.11.1.聲明游標.
5	定義數組	TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;	可以使用暫存資料表代替oracle數組, 也可以迴圈拆分字元來替代oracle數組.	目前可以使用暫存資料表來代替oracle數組.  詳見<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.4 Mysql數組處理部分
6	注釋方式不同	"-- message"  或 "/** ….  */" 或"/* ….  */"	"-- message"  或 "/* ….  */" 或 "#"	mysql注釋來自  MySQL 5.1參考手冊 9.5. 注釋文法, 建議同oracle一樣, 單行用--, 多行/* */
7	內建日期時間函數格式不同	Oracle時間格式：yyyy-MM-dd hh:mi:ss	Mysql時間格式：%Y-%m-%d %H:%i:%s	1. MYSQL日期欄位分DATE和TIME兩種.     ORACLE日期欄位只有DATE，包含年月日時分秒資訊.  2. mysql中取當前系統時間為now()函數,精確到秒.     oracle中取當前資料庫的系統時間為SYSDATE, 精確到秒.
8	日期加減	目前時間加N天: sysdate+N 目前時間減N天: sysdate-N	日期相加: date_add(now(), INTERVAL 180 DAY) 日期相減: date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second)
9	字串串連符不同	result  := v_int1||v_int2;	set result =concat(v_int1,v_int2);	1. oracle使用||連接字串,也可以使用concat函數. 但Oracle的concat函數只能串連兩個字串.  Mysql使用concat方法連接字串. MySQL的concat函數可以串連一個或者多個字串,如        mysql> select concat('10');   結果為: 10.        mysql> select concat('11','22','33','aa'); 結果為: 112233aa 2. "||"在Mysql是與運算
10	定義遊標不同	CURSOR l_bk_cur IS    SELECT B.BK_HDR_INT_KEY, B.BK_NUM    FROM ES_SR_DTL_VRB A, ES_BK_HDR B    WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY          AND b.BK_STATUS != ES_BK_PKG.g_status_can          AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key;	DECLARE l_bk_cur CURSOR     FOR SELECT B.BK_HDR_INT_KEY, B.BK_NUM    FROM ES_SR_DTL_VRB A, ES_BK_HDR B    WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY          AND b.BK_STATUS != ES_BK_PKG.g_status_can          AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key;	詳見<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.2 Mysql遊標處理部分
11	交易回復	ROLLBACK;	ROLLBACK;	oracle和mysql中使用方法相同
12	GOTO語句	GOTO check_date;	GOTO check_date;	oracle和mysql中使用方法相同

以上就是MySQL與Oracle資料類型對應關係的全部內容了