数据库oracle与mysql在语法上差异是什么

2024-07-24 12:34:12

字体：大中小

来源：转载

供稿：网友

　　数据库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');

　　将字符串型时间转换为时间类型 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)>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 1146错误问题

下一篇：mysql如何删掉index索引