1. 数据类型

• Number类型

  MySQL中是没有Number类型的，但有int/decimal 类型，Oracle中的Number(5,1)对应MySQL中的decimal(5,1)，Number(5) 对应 int(5)。MySQL中的数字型类型比较多，分的也比较细，还有tinyint、smallint、mediumint、bigint等类型

• varchar2(n)类型

  MySQL中对应Oracle Varchar2(n)类型的替代类型是varchar(n)类型。

• Date 类型

  MySQL 中的日期时间类型有Date、Time、Datetime等类型，MySQL中Date类型仅表示日期(年-月-日)，Time类型仅表示时间（时:分:秒），而Datetime类型表示日期时间(年-月-日 时:分:秒)，Oracle中的Date类型和MySQL中的Datetime类型一致。

2. 函数

• length(str)函数

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

• sys_guid()函数

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

• 时间格式化函数

  • 时间转换为字符串 MySQL: date_format(NOW(),'%Y-%m-%d')   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')

  注意: MySQL不能直接两个日期用减号相减, 它会去掉连接符转成数字进行计算, 结果没有任何意义

• 条件函数 nvl()、nvl2()、decode()

  • nvl(col, 0)   Oralce函数: col为空，则返回值取0，否则取col   MySQL: ifnull(col, 0)
  • nvl2(expr1,expr2,expr3)   Oralce函数: 如果expr1不为null，则返回expr2，否则返回expr3   MySQL: if(expr1,expr2,expr3)
  • DECODE(value, val1, val2, val3)   Oralce函数: 如果value等于val1，则返回val2，否则返回val3   MySQL: 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 value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end

• trunc()函数

  • TRUNC(12.123)   Oracle: 返回整数(12)   MySQL: truncate(12.123, 0)
  • TRUNC(12.123, 2)   Oracle: 返回值保留2为小数(12.12)   MySQL: truncate(12.123, 2)
  • TRUNC(SYSDATE)   Oracle: 返回值为(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）

• to_char() / to_number()

  • to_char(123)   Oracle: 数字123转为字符串123   MySQL: CAST(123 AS CHAR(3))
  • to_number('123')   Oracle: 字符串123转换为数字类型   MySQL: 对应的函数为cast('123' as SIGNED)；

• sysdate

  sysdate   Oracle: 返回当前日期+时间   MySQL: now()

其他

• 引号

  MySQL可识别双引号和单引号（反引号`为字段名），Oracle只能识别单引号（双引号为字段名）。

• 字符串连接符

  Oracle: 可用'||'来连接多个字符串（concat只能连接两个字符串）  MySQL: concat()函数可连接多个字符串

• ROWNUM

  Oracle可通过rownum获取前n条记录，MySQL通过limit来获取前n条记录（Oracle中rownum作为where条件的一部分，而MySQL中limit不是where条件的一部分）

• 空数据排序(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

• 全关联

MySQL不支持full join （需通过join + union方式实现）

• 递归查询(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));

• 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);

• with

Oracle 中可用with来构建一个临时表，MySQL8.0+也支持

源地址 (opens new window)