數(shù)據(jù)庫(kù)oracle與mysql在語(yǔ)法上區(qū)別是什么

170次閱讀

共計(jì) 6088 個(gè)字符，預(yù)計(jì)需要花費(fèi) 16 分鐘才能閱讀完成。

數(shù)據(jù)庫(kù) oracle 與 mysql 在語(yǔ)法上區(qū)別是什么，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

區(qū)別：1、MySQL 沒有 Number 類型，而 oracle 有；2、MySQL 中 Date 類型僅表示日期，Oracle 中 Date 類型表示日期時(shí)間；3、獲取字符串長(zhǎng)度，Oracle 用 length()，MySQL 用 char_length()等等。

本教程操作環(huán)境：windows7 系統(tǒng)、mysql8 版本、Dell G3 電腦。

最近需要遷移項(xiàng)目，將數(shù)據(jù)庫(kù)由 Oracle 改為 MySQL。由于兩者的語(yǔ)法有部分不一樣，所以需要把 Oracle 中能用但 MySQL 中不能用的函數(shù) / 類型等改為 MySQL 中能用的，以下是總結(jié)出的部分語(yǔ)法區(qū)別：

一、數(shù)據(jù)類型

1. Number 類型

MySQL 中是沒有 Number 類型的，但有 int/decimal 類型，Oracle 中的 Number(5,1)對(duì)應(yīng) MySQL 中的 decimal(5,1)，Number(5) 對(duì)應(yīng) int(5)。

MySQL 中的數(shù)字型類型比較多，分的也比較細(xì)，還有 tinyint、smallint、mediumint、bigint 等類型

2. Varchar2(n)類型

MySQL 中對(duì)應(yīng) Oracle Varchar2(n)類型的替代類型是 varchar(n)類型。

3. Date 類型

MySQL 中的日期時(shí)間類型有 Date、Time、Datetime 等類型，MySQL 中 Date 類型僅表示日期(年 - 月 - 日)，Time 類型僅表示時(shí)間（時(shí): 分: 秒），而 Datetime 類型表示日期時(shí)間(年 - 月 - 日時(shí): 分: 秒)，Oracle 中的 Date 類型和 MySQL 中的 Datetime 類型一致。

二、函數(shù)

1. length(str)函數(shù)

Oracle 中的 length(str)是獲取字符串長(zhǎng)度的函數(shù)，MySQL 中對(duì)應(yīng)的函數(shù)為 char_length(str)。

2. sys_guid()函數(shù)

Oracle 中可通過 sys_guid()函數(shù)是生成隨機(jī)序列，MySQL 通過 UUID()生成隨機(jī)序列。

3. 時(shí)間格式化函數(shù)

將時(shí)間轉(zhuǎn)換為字符串型時(shí)間 MySQL date_format(NOW(), %Y-%m-%d ) 對(duì)應(yīng) Oracle 的 Oracle 中的 to_char(sysdate, YYYY-MM-DD

將字符串型時(shí)間轉(zhuǎn)換為時(shí)間類型 MySQL str_to_date(2019-01-01 , %Y-%m-%d) 對(duì)應(yīng) Oracle 中的 to_date(2019-01-01 , YYYY-MM-DD

包括時(shí)分秒的函數(shù)轉(zhuǎn)換：DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s )，str_to_date(2019-01-01 , %Y-%m-%d %H:%i:%s)。

4. 條件函數(shù)（nvl()、nvl2()、decode()）

nvl(tab.columnName, 0)：如果 tab.columnName 值為空，則返回值取 0，否則取 tab.columnName；對(duì)應(yīng)的 MySQL 函數(shù)為：ifnull(tab.columnName, 0)。

nvl2(expr1,expr2,expr3)：如果 expr1 不為 null，則返回 expr2，否則返回 expr3；對(duì)應(yīng)的 MySQL 函數(shù)為：if(expr1,expr2,expr3)。

DECODE(value, val1, val2, val3)：如果 value 等于 val1，則返回 val2，否則返回 val3；MySQL 可用 IF 函數(shù)表示：if(value=val1, val2, val3)；

DECODE(value, if1, val1, if2,val2,…,ifn, valn, val)：如果 value 等于 if1，則返回 val1，如果 value 等于 if2，則返回 value2… 如果 value 等于 ifn，則返回 valn，否則返回 val；MySQL 對(duì)于這種判斷可以通過 case when then else end;l 來(lái)判斷，即：case when value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end;

5. trunc()函數(shù)

TRUNC(12.123)：返回整數(shù)(12)；MySQL 對(duì)應(yīng)的函數(shù)：truncate(12.123, 0)；

TRUNC(12.123, 2)：返回值保留 2 為小數(shù)(12.12)；MySQL 對(duì)應(yīng)的函數(shù)：truncate(12.123, 2)；

TRUNC(SYSDATE)：返回值為(2019-07-26 00:00:00)；MySQL 對(duì)應(yīng)的為 cast(now() as datetime)：返回值為(2019-07-26 14:11:38)；

MySQL 的 cast 函數(shù)語(yǔ)法為：CAST(xxx AS 類型)（可用類型為：二進(jìn)制, 同帶 binary 前綴的效果:BINARY；字符型, 可帶參數(shù):CHAR()；日期:DATE；時(shí)間:TIME；日期時(shí)間型: DATETIME；浮點(diǎn)數(shù): DECIMAL；整數(shù):SIGNED；無(wú)符號(hào)整數(shù):UNSIGNED）

6. to_char() to_number()

to_char(123)：將數(shù)字 123 轉(zhuǎn)換為字符串 123；MySQL 對(duì)應(yīng)的函數(shù)為 CAST(123 AS CHAR(3))；

to_number(123)：將字符串?dāng)?shù)字 123 轉(zhuǎn)換為數(shù)字類型；MySQL 對(duì)應(yīng)的函數(shù)為 cast(123 as SIGNED)；

7. sysdate 當(dāng)前時(shí)間

sysdate：返回當(dāng)前日期 + 時(shí)間；MySQL 對(duì)應(yīng)的函數(shù)為 now()；

三、其他

1. 引號(hào)

MySQL 可識(shí)別雙引號(hào)和單引號(hào)，Oracle 只能識(shí)別單引號(hào)。

2. 字符串連接符 ||

Oracle 可用 || 來(lái)連接字符串，但 MySQL 不支持 || 連接，MySQL 可通過 concat()函數(shù)鏈接字符串。

Oracle 的 a.studentname||【||a.studentno||】相當(dāng)于 MySQL 的 concat(a.studentname,【, a.studentno,】)

3. ROWNUM

Oracle 可通過 rownum 獲取前 n 條記錄，MySQL 通過 limit 來(lái)獲取前 n 條記錄，但二者的寫法略有不同，在 Oracle 中 rownum 作為 where 條件的一部分，而 MySQL 中 limit 不是 where 條件的一部分。

-- rownum 語(yǔ)法如下：SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; --  查詢第一條數(shù)據(jù)
SELECT * FROM XJ_STUDENT WHERE ROWNUM  = 10; --  獲取前 10 條數(shù)據(jù)
--  但 rownum 不支持查詢后幾條或第 n(n 1)條數(shù)據(jù)，例如以下 sql 是不支持的
SELECT * FROM XJ_STUDENT WHERE ROWNUM   2;
SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3;
-- limit  語(yǔ)法如下：SELECT * from fw_department limit 3; --  查詢前 3 條數(shù)據(jù)
SELECT * from fw_department limit 2, 4; --  從第 2(序號(hào)從 0 開始)條開始，查 4 條記錄

4. 空數(shù)據(jù)排序(nulls first 和 nulls last)

-- null 值排在最前
SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS FIRST
-- null 值排在最后
SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS LAST
 
-- MySQL  可通過 IF 和 ISNULL 函數(shù)達(dá)到相同的效果
-- null 值排在最后
select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc
-- null 值排在最前
select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc

5. 表 (左 / 右) 關(guān)聯(lián)(+)

Oracle 左連接, 右連接可以使用 (+) 來(lái)實(shí)現(xiàn). MySQL 只能使用 left join ,right join 等關(guān)鍵字。

-- Oracle  左關(guān)聯(lián)
select * from taba, tabb where taba.id = tabb.id(+);
-- Oracle  右關(guān)聯(lián)
select * from taba, tabb where taba.id(+) = tabb.id;
-- MySQL  左關(guān)聯(lián)
select * from taba left join tabb on taba.id=tabb.id;
-- MySQL  右關(guān)聯(lián)
select * from taba right join tabb on taba.id=tabb.id;

6. 刪除語(yǔ)法

MySQL 的刪除語(yǔ)法沒有 Oracle 那么隨意，例如下面的 sql 在 Oracle 中可以執(zhí)行，但在 MySQL 中就不可以。

-- Oracle  可執(zhí)行，但 MySQL 中不能執(zhí)行
DELETE FROM FW_DEPARTMENT A WHERE A.DEPID =  111 
DELETE FW_DEPARTMENT WHERE DEPID =  111 
-- MySQL 中刪除語(yǔ)句格式如下：DELETE FROM FW_DEPARTMENT WHERE DEPID =  111

7. 遞歸查詢(start with connect by prior)

MySQL 不支持 (start with connect by prior) 的這種遞歸查詢，但可以通過自定義函數(shù)來(lái)實(shí)現(xiàn)。

-- Oracle  遞歸查詢   查詢部門 ID 為‘1111’的所有子部門（包含自身）SELECT *
FROM FW_DEPARTMENT
START WITH DEPID= 1111 
CONNECT BY PRIOR DEPID = PARENTDEPID;
-- Oracle  遞歸查詢   查詢部門 ID 為‘1111’的所有父部門（包含自身）SELECT *
FROM FW_DEPARTMENT
START WITH DEPID= 1111 
CONNECT BY PRIOR PARENTDEPID = DEPID;
-- MySQL  先創(chuàng)建 fun_getDepIDList 函數(shù)，用于查詢部門 ID 字符串
CREATE FUNCTION fun_getDepIDList(rootId VARCHAR(32))
RETURNS VARCHAR(6000)
BEGIN 
 DECLARE pTemp VARCHAR(6000);
 DECLARE cTemp VARCHAR(6000);
 SET pTemp= $ 
 SET cTemp=rootId;
 WHILE cTemp is not null DO
 set pTemp=CONCAT(pTemp, , ,cTemp);
 SELECT GROUP_CONCAT(depid) INTO cTemp from fw_department
 WHERE FIND_IN_SET(PARENTDEPID,cTemp) 
 END WHILE;
 RETURN pTemp;
--  查詢部門 ID 為‘1111’的所有子部門（包含自己）select * from fw_department
where FIND_IN_SET(DEPID, fun_getDepIDList( 1111 
--  查詢部門 ID 為‘1111’的所有父部門(包含自己)
select * from fw_department
where FIND_IN_SET(1111 , fun_getDepIDList(DEPID));

8. merge into

MySQL 不支持（merge into），但提供的 replace into 和 on duplicate key update 可實(shí)現(xiàn)相似的功能。

-- Oracle merge into (有則修改，無(wú)則新增)
MERGE INTO TMPDEPTAB A
USING (SELECT  1111  DEPID,  哈哈  DEPNAME FROM DUAL) B
ON (A.DEPID = B.DEPID)
WHEN MATCHED THEN 
 UPDATE SET A.DEPNAME = B.DEPNAME
WHEN NOT MATCHED THEN 
 INSERT(DEPID, DEPNAME) VALUES(B.DEPID, B.DEPNAME);
-- MySQL replace into (特點(diǎn)：1、先刪后增； 2、插入 / 更新的表必須有主鍵或唯一索引；-- 3、未修改 / 新增的數(shù)據(jù)項(xiàng)，如果必填，則必須有默認(rèn)值)
-- 1、由于是先刪后增，所以需要滿足以下 2 個(gè)條件之一：-- 1. 要么必填項(xiàng)有默認(rèn)值； 
-- 2. 要么插入 / 更新時(shí)為沒有默認(rèn)值的必填項(xiàng)賦值，  否則新增時(shí)會(huì)報(bào)錯(cuò)。-- 2、表中需要有主鍵或唯一索引，否則下面語(yǔ)句如果執(zhí)行多次，表中會(huì)出現(xiàn)重復(fù)數(shù)據(jù)。replace into fw_department(DEPID,PARENTDEPID,DEPNO,DEPNAME) 
values( 1111111 ,  1234 , 123 ,  哈哈 
-- MySQL on duplicate key update (特點(diǎn)：1、插入 / 更新的表必須有主鍵或唯一索引；-- 2、未修改 / 新增的數(shù)據(jù)項(xiàng)，如果必填，則必須有默認(rèn)值)
insert into fw_department(depid,parentdepid,depno,depname)
select  1111111  depid,  123  parentdepid,  e12  depno,  哈哈哈哈  depname
from fw_department
on duplicate key 
update parentdepid = values(parentdepid),
 depno=values(depno),
 depname=values(depname);

9. with

Oracle 中可用 with 來(lái)構(gòu)建一個(gè)臨時(shí)表，但 MySQL 不支持 with，對(duì)應(yīng)臨時(shí)表，MySQL 可通過小括號(hào)的方式來(lái)處理，但構(gòu)建的臨時(shí)表必須設(shè)置臨時(shí)表名。

-- Oracle with 使用
WITH TMPTAB AS (
 SELECT A.DEPID FROM FW_DEPARTMENT A
SELECT DEPID
FROM TMPTAB
-- MySQL  構(gòu)建臨時(shí)表使用(此處必須給括號(hào)中的臨時(shí)表設(shè)置表名)
select b.depid
from (
 select depid
 from fw_department
) b

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對(duì)丸趣 TV 的支持。

正文完