数据库oracle与mysql在语法上区别是什么

54次阅读

共计 6088 个字符，预计需要花费 16 分钟才能阅读完成。

数据库 oracle 与 mysql 在语法上区别是什么，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

区别：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) 
 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 可实现相似的功能。

-- 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

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。

正文完