oracle数据库与mysql区别(数据库oracle 和mysql区别)

oracle和mysql在语法上有什么区别？很多新手对此不是很清楚。为了帮助大家解决这个问题，下面小编就详细讲解一下。需要的人可以从中学习，希望你能有所收获。

区别：1。MySQL没有Number类型，而oracle有；2.MySQL中的日期类型只代表日期，而Oracle中的日期类型代表日期和时间；3.获取字符串长度，Oracle使用length()，MySQL使用char_length()等等。

本教程的操作环境：windows7系统，mysql8版本，戴尔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中有日期、时间和日期时间类型。MySQL中的Date类型只表示日期(年-月-日)，time类型只表示时间(33，360分钟和33，360秒)，Datetime类型表示Datetime(年-月-日的33，360分钟和33，360秒)。Oracle中的日期类型与MySQL中的日期时间类型一致。

二、函数

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中的to _ 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:% i3360% s ')，str _ to _ date ('2019-01-01 '，' % y-%m-%d% h

4. 条件函数（nvl()、nvl2()、decode()）

Nvl(tab.columnName，0):如果tab.columnName的值为空，返回值为0；否则，它是选项卡。ColumnName对应的MySQL函数为：ifnull(tab.columnName，0)。

Nvl2(expr1，expr2，expr3):如果expr1不为空，则返回expr2，否则返回expr 3；对应的MySQL函数是：if(expr1，expr2，expr3)。

DECODE(value，val1，val2，val3):如果value等于val1，则返回val2，否则返回val 3；MySQL可以表示为IF函数：if (value=Val1，Val2，Val3)；

解码(值，if1，val1，if2，Val2，ifn，valn，Vall):如果值等于IF1，则返回Val1，如果值等于IF2，则返回Val2.如果值等于IFN，则返回Valn，否则返回Valn；MySQL可以根据案例来判断，否则什么时候结束；l，即：当值=if1，然后val1时，当值=if2，然后val2时，当值=ifn，然后valn，否则valend

5. trunc()函数

TRUNC(12.123):返回一个整数(12)；MySQL对应的函数：截断

(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

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