MySQL與Oracle數據類型對應關系的示例分析

167次閱讀

沒有評論

共計 6563 個字符，預計需要花費 17 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 MySQL 與 Oracle 數據類型對應關系的示例分析，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

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 數據庫常見數據類型對應關系

編號 ORACLEMYSQL 注釋 1NUMBERint / DECIMALDECIMAL 就是 NUMBER(10,2)這樣的結構 INT 就是是 NUMBER(10)，表示整型；
MYSQL 有很多類 int 型，tinyint mediumint bigint 等，不同的 int 寬度不一樣 2Varchar2（n）varchar(n) 3DateDATATIME 日期字段的處理
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 中當前時間是 sysdate4INTEGERint / INTEGERMysql 中 INTEGER 等價于 int5EXCEPTIONSQLEXCEPTION 詳見 2009001-eService-O2MG.doc 中 2.5 Mysql 異常處理 6CONSTANT VARCHAR2(1)mysql 中沒有 CONSTANT 關鍵字從 ORACLE 遷移到 MYSQL, 所有 CONSTANT 常量只能定義成變量 7TYPE g_grp_cur IS REF CURSOR; 光標 : mysql 中有替代方案詳見 2009001-eService-O2MG.doc 中 2.2 光標處理 8TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; 數組: mysql 中借助臨時表處理
或者直接寫邏輯到相應的代碼中，
直接對集合中每個值進行相應的處理詳見 2009001-eService-O2MG.doc 中 2.4 數組處理 9 自動增長的序列自動增長的數據類型 MYSQL 有自動增長的數據類型，插入記錄時不用操作此字段，會自動獲得數據值。ORACLE 沒有自動增長的數據類型，需要建立一個自動增長的序列號，插入記錄時要把序列號的下一個值賦于此字段。10NULLNULL 空字符的處理
MYSQL 的非空字段也有空的內容，ORACLE 里定義了非空字段就不容許有空的內容。按 MYSQL 的 NOT NULL 來定義 ORACLE 表結構, 導數據的時候會產生錯誤。因此導數據時要對空字符進行判斷，如果為 NULL 或空字符，需要把它改成一個空格的字符串。

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

編號類別 ORACLEMYSQL 注釋 1 變量的聲明方式不同 li_index NUMBER := 0DECLARE li_index INTEGER DEFAULT 01. mysql 使用 DECLARE 定義局部變量.
定義變量語法為: DECLARE var_name[,…] type [DEFAULT value] 要給變量提供一個默認值，需要包含一個 DEFAULT 子句。值可以被指定為一個表達式，不需要為一個常數。如果沒有 DEFAULT 子句，初始值為 NULL。 2 變量的賦值方式不同 lv_inputstr := iv_inputstrSET lv_inputstr = iv_inputstr1. 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:ssMysql 時間格式：%Y-%m-%d %H:%i:%s1. 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 中使用方法相同 12GOTO 語句 GOTO check_date;GOTO check_date;oracle 和 mysql 中使用方法相同