數據庫oracle與mysql在語法上差異是什么

2024-07-24 12:34:12

字體：大中小

來源：轉載

供稿：網友

　　數據庫oracle與mysql在語法上區別是什么，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

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

　　本教程操作環境：windows7系統、mysql8版本、Dell G3電腦。

　　最近需要遷移項目，將數據庫由Oracle改為MySQL。由于兩者的語法有部分不一樣，所以需要把Oracle中能用但MySQL中不能用的函數/類型等改為MySQL中能用的，以下是總結出的部分語法區別：

　　一、數據類型

　　1. Number類型

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

　　MySQL中的數字型類型比較多，分的也比較細，還有tinyint、smallint、mediumint、bigint等類型

　　2. Varchar2(n)類型

　　MySQL中對應Oracle Varchar2(n)類型的替代類型是varchar(n)類型。

　　3. Date 類型

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

　　二、函數

　　1. length(str)函數

　　Oracle中的length(str)是獲取字符串長度的函數，MySQL 中對應的函數為char_length(str)。

　　2. sys_guid()函數

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

　　3. 時間格式化函數

　　將時間轉換為字符串型時間 MySQL date_format(NOW(),'%Y-%m-%d') 對應Oracle的 Oracle中的 to_char(sysdate, 'YYYY-MM-DD');

　　將字符串型時間轉換為時間類型 MySQL str_to_date('2019-01-01','%Y-%m-%d') 對應Oracle中的 to_date('2019-01-01', 'YYYY-MM-DD');

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

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

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

　　nvl2(expr1,expr2,expr3)：如果expr1不為null，則返回expr2，否則返回expr3；對應的MySQL函數為：if(expr1,expr2,expr3)。

　　DECODE(value, val1, val2, val3)：如果value等于val1，則返回val2，否則返回val3；MySQL可用IF函數表示：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對于這種判斷可以通過case when then else end;l來判斷，即：case when value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end;

　　5. trunc()函數

　　TRUNC(12.123)：返回整數(12)；MySQL對應的函數：truncate(12.123, 0)；

　　TRUNC(12.123, 2)：返回值保留2為小數(12.12)；MySQL對應的函數：truncate(12.123, 2)；

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

　　MySQL的cast函數語法為：CAST(xxx AS 類型) （可用類型為：二進制,同帶binary前綴的效果:BINARY；字符型,可帶參數:CHAR()；日期:DATE；時間:TIME；日期時間型: DATETIME；浮點數: DECIMAL；整數:SIGNED；無符號整數:UNSIGNED）

　　6. to_char() to_number()

　　to_char(123)：將數字123轉換為字符串123；MySQL對應的函數為CAST(123 AS CHAR(3))；

　　to_number('123')：將字符串數字123轉換為數字類型；MySQL對應的函數為cast('123' as SIGNED)；

　　7. sysdate 當前時間

　　sysdate：返回當前日期+時間； MySQL對應的函數為 now()；

　　三、其他

　　1. 引號

　　MySQL可識別雙引號和單引號，Oracle只能識別單引號。

　　2. 字符串連接符 ||

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

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

　　3. ROWNUM

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

　　-- rownum語法如下：
　　SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; -- 查詢第一條數據
　　SELECT * FROM XJ_STUDENT WHERE ROWNUM <= 10; -- 獲取前10條數據
　　-- 但rownum不支持查詢后幾條或第n(n>1)條數據，例如以下sql是不支持的
　　SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2;
　　SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3;

　　-- limit 語法如下：
　　SELECT * from fw_department limit 3; -- 查詢前3條數據
　　SELECT * from fw_department limit 2, 4; -- 從第2(序號從0開始)條開始，查4條記錄
　　4. 空數據排序(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函數達到相同的效果
　　-- 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. 表(左/右)關聯(+)

　　Oracle左連接,右連接可以使用(+)來實現. MySQL只能使用left join ,right join等關鍵字。

　　-- Oracle 左關聯
　　select * from taba, tabb where taba.id = tabb.id(+);
　　-- Oracle 右關聯
　　select * from taba, tabb where taba.id(+) = tabb.id;
　　-- MySQL 左關聯
　　select * from taba left join tabb on taba.id=tabb.id;
　　-- MySQL 右關聯
　　select * from taba right join tabb on taba.id=tabb.id;
　　6. 刪除語法

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

　　-- Oracle 可執行，但MySQL中不能執行
　　DELETE FROM FW_DEPARTMENT A WHERE A.DEPID = '111';
　　DELETE FW_DEPARTMENT WHERE DEPID = '111';
　　-- MySQL中刪除語句格式如下：
　　DELETE FROM FW_DEPARTMENT WHERE DEPID = '111';
　　7. 遞歸查詢(start with connect by prior)

　　MySQL不支持(start with connect by prior)的這種遞歸查詢，但可以通過自定義函數來實現。

　　-- 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 先創建fun_getDepIDList函數，用于查詢部門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)>0;
　　 END WHILE;
　　 RETURN pTemp;
　　END;

　　-- 查詢部門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可實現相似的功能。

　　-- Oracle merge into (有則修改，無則新增)
　　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 (特點：1、先刪后增； 2、插入/更新的表必須有主鍵或唯一索引；
　　-- 3、未修改/新增的數據項，如果必填，則必須有默認值)
　　-- 1、由于是先刪后增，所以需要滿足以下2個條件之一：
　　-- 1.要么必填項有默認值；
　　-- 2.要么插入/更新時為沒有默認值的必填項賦值，否則新增時會報錯。
　　-- 2、表中需要有主鍵或唯一索引，否則下面語句如果執行多次，表中會出現重復數據。
　　replace into fw_department(DEPID,PARENTDEPID,DEPNO,DEPNAME)
　　values('1111111', '1234','123', '哈哈');

　　-- MySQL on duplicate key update (特點：1、插入/更新的表必須有主鍵或唯一索引；
　　-- 2、未修改/新增的數據項，如果必填，則必須有默認值)
　　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來構建一個臨時表，但MySQL不支持with，對應臨時表，MySQL可通過小括號的方式來處理，但構建的臨時表必須設置臨時表名。

　　-- Oracle with使用
　　WITH TMPTAB AS (
　　 SELECT A.DEPID FROM FW_DEPARTMENT A
　　)
　　SELECT DEPID
　　FROM TMPTAB

　　-- MySQL 構建臨時表使用(此處必須給括號中的臨時表設置表名)
　　select b.depid
　　from (
　　 select depid
　　 from fw_department
　　) b
　　看完上述內容是否對您有幫助呢？

（編輯：武林網）

上一篇：如何處理mysql 1146錯誤問題

下一篇：mysql如何刪掉index索引