資料庫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');

        將字串型時間轉換為時間型別時間轉換為時間型別('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)；#UNC#TR##   SYS (2019-07-26 00:00:00)；MySQL對應的為cast(now() as datetime)：傳回值為(2019-07-26 14:11:38)；

    My   1:38)；

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

6. to_char() to_number()

#        to_char(123

        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影片教學】

以上是資料庫oracle與mysql在語法上有什麼區別的詳細內容。更多資訊請關注PHP中文網其他相關文章！