2020-11-02，MySQL與Oracle資料型別，用法對比大全

tony_yang6發表於2020-11-02
MySqlOracle資料型別

常見的資料型別再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中當前時間是sysdate
4INTEGERint / INTEGERMysql中INTEGER等價於int
5EXCEPTIONSQLEXCEPTION 詳見<<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中使用方法相同

 

相關文章