文章目录

1 前言2 数据类型3 函数1. length(str)函数2. sys_guid()函数3. 日期函数&转换函数4. 条件函数（nvl()、nvl2()、decode()） 4 其他1. 引号2. 字符串连接符 ||3. ROWNUM4. 空数据排序(`nulls first` 和`nulls last`)5. 表(左/右)关联(+)6. 删除语法7. 递归查询(start with connect by prior)8. merge into9. with

---

1 前言

本文主要描述了Oracle和MySQL的语法区别，主要包括数据类型，函数和其他三部分。 后文小标题均将Oralcle的语法写在前面，MySQL在后，形如‘Oralcle’&‘MySQL’。

2 数据类型

常见的数据类型如下。

Number & int。Oracle的数据类型是Number类型，MySQL中是没有Number类型的，但有int/decimal 等类型。MySQL中的数字型类型比较多，如tinyint、smallint、mediumint、bigint等类型。Oracle中的Number(5,1)对应MySQL中的decimal(5,1)，Number(5) 对应 int(5)。 Varchar2(n) & varchar(n)。Oracle Varchar2(n)类型对应MySQL中的varchar(n)类型。Date & Datetime。Oracle中的Date类型和MySQL中的Datetime类型一致。 MySQL 中的日期时间类型有Date、Time、Datetime等类型，MySQL中Date类型仅表示日期(年-月-日)，Time类型仅表示时间（时:分:秒），而Datetime类型表示日期时间(年-月-日 时:分:秒)。

表：Oracle & MySQL 常见数据类型对应关系

序号OracleMySQL备注1Numberint/decimalOracle中的Number(5,1)对应MySQL中的decimal(5,1)，Number(5) 对应 int(5)2Varchar2（n）varchar(n)3DateDatetime详见：【数据库笔记】Oracle & MySQL 日期时间数据类型、日期函数和转换函数の对比4Integerint5ExceptionSQLEXCEPTION6自动增长的序列自动增长的数据类型Oracle 没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。MySQL 有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。7NULLNULL在空字符的处理上，Oracle里定义了非空字段就不容许有空的内容。MySQL 的非空字段也有空的内容。因此，按MySQL 的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误：要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

部分表内容源于：https://blog.csdn.net/huangyinzhao/article/details/80739291

3 函数

1. length(str)函数

Oracle中的`length(str)`是获取字符串长度的函数，MySQL 中对应的函数为`char_length(str)`。

2. sys_guid()函数

Oracle中可通过`sys_guid()`函数是生成随机序列，MySQL通过`UUID()`生成随机序列。

3. 日期函数&转换函数

详见：[【数据库笔记】Oracle & MySQL 日期时间数据类型、日期函数和转换函数の对比](https://blog.csdn.net/qq_36056219/article/details/108977639)

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;来判断，即：

case when value=if1 then val1 when value=if2 then val2 ... when value=ifn then valn else val end;

4 其他

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（+）号用法

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可通过小括号的方式来处理，但构建的临时表必须设置临时表名。

新版MySQL现在也支持with构建附表，形式同Oracle 。

【数据库笔记】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

Reference：https://blog.csdn.net/lanmuhhh2015/article/details/97763615