Mysql与Oracle语法差异大盘点,不是最全面但求更全面!

avatar
作者
筋斗云
阅读量:1

​文编|JavaBuild

哈喽,大家好呀!我是JavaBuild,以后可以喊我鸟哥,嘿嘿!俺滴座右铭是不在沉默中爆发,就在沉默中灭亡,一起加油学习,厚积薄发!

最近不知道领导抽的哪门子疯,非要将原来用的好好的Oracle数据库转为mysql数据库,且不说两者性能之间的差别,就单数去O这个理念,MySQL难道就不是国外的产品?不还是Oracle公司的嘛,但咱们互联网搬运工就不掰扯那么多了,指哪打哪!

鉴于两种关系型数据库之间的语法差异,花了点时间整理了一下,特此记录,本文主要记录的是两种数据库语言在做sql转换时存在的语法区别,很多详情没有过多介绍,不再赘言,直接上干货!用得着的学友们可以收藏一下哈。

一、数据类型

1、Date类型

MySQL中对于日期时间处理的类型分为Date、Time、DateTime等

        a.        Date:仅表示(年-月-日)

        b.        Time:仅表示时间(时:分:秒)

        c.        DateTime:表示日期和时间(年-月-日 时:分:秒)

Oracle中的Date类型其作用和MySQL中的DateTime类型一致。(其中还有TIMESTAMP时间戳也可表示时间,两种数据库用法类似)

2、varchar(n)

MySQL中的varchar(n)在转为Oracle语法时,会被转成varchar2(n),虽然两者有细微差别,但在大部分场景下可以平替,虽然Oracle中也有varchar类型,但varchar只对汉字和全角等字符占两字节,数字,英文字符等都是一个字节,这会带来很大的潜在问题。

3、Number

MySQL中没有Number类型,但有很多细分的数字类型,比如int、bigint、decimal。

        1)oracle中Number(5,1)等价于MySQL中decimal(5,1);

        2)   oracle中Number(5)等价于MySQL中int(5);

二、函数

1、length(str)

Oracle中的length(str)函数的作用是用来获取字符串长度的,MySQL中对应的可以使用char_length(str)。

2、substr(str,start,length)

这个函数的实现的是字符串截取,MySQL中start要从1开始,否则获取不到数据,而Oracle中start从0或1均可。MySQL中还可以用substring()实现相同的功能。

3、时间格式化函数

3.1,时间类型转字符串型时间:MySQL中用date_format(now(),'%Y-%m-%d');Oracle中用to_char(sysdate,'YYYY-MM-DD')。

3.2,字符串型时间转为时间类型:MySQL中用str_to_date('2023-12-19','%Y-%m-%d');Oracle中用to_date('2023-12-19','YYYY-MM-DD')。

3.3,计算两日期之间天数

MySQL中用IMESTAMPDIFF(DAY,STR_TO_DATE(STARTDATE,'%Y-%m-%d'),STR_TO_DATE(ENDDATE,'%Y-%m-%d'))。

Oracle中用ROUND(TO_NUMBER(TO_DATE(ENDDATE,'YYYY-MM-DD')-TO_DATE(STARTDATE,'YYYY-MM-DD')))。

4、条件函数

4.1,nvl()

Oracle中nvl(data,0)表示若data为空。则返回值0,都则返回data;

MySQL中对应的函数为ifnull(data,0).

4.2,nvl2()

Oracle中nvl2(ex1,ex2,ex3)表示如果ex1不为null,则返回ex2,否则返回ex3;

MySQL中对应的函数为if(ex1,ex2,ex3)。

4.3,decode()

Oracle中decode(value,val2,val2,val3),表示如果value等于val1,则返回val2,否则返回val3;

MySQL中对应的函数为if(value=val1,val2,val3)。

4.4,decode()多条件判断

Oracle中decode(value,if1,val1,if2,val2,...ifn,valn,val),表示如果value等于if1,返回val1,如果等于if2,返回val2...等于ifn,返回valn,否则返回val;

MySQL中对应的可以使用case when value=if1 then val1 when value=if2 then val2...when value=ifn then valn else val end。(当然在Oracle中同样支持case when 写法滴)

5、trunc()函数

Oracle中trunc(10.22)表示返回整数部分10,对应MySQL中truncate(10.22,0);

Oracle中trunc(10.223,2)表示返回值保留2为小数,对应MySQL中truncate(10.223,2);

Oracle中可以借助trunc函数直接获取本年度,本季度,本周等的第一天,而MySQL中则需要借助Year等函数来自定义。

如:Oracle中可通过trunc(sysdate,'q')获取本季度第一天日期,而想实现同样的功能,MySQL应该这样写:Date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1))。是不是看着就很复杂呀,这就是花钱和不花钱的区别!

6、数字与字符串转换函数

6.1,将数字转为字符串

Oracle中用to_char(123)实现,MySQL中用CAST('123',AS CHAR);

6.2,将字符串类型数字转数字类型

Oracle中用to_number('123'),对应MySQL则用CAST('123',AS SIGNED)

补充:MySQL中的cast(xxx as 类型),支持的类型有:二进制(binary),可带参数字符型char(),日期、时间、日期时间型(Date,Time,DateTime)、浮点数(decimal)、整数(signed)、无符号整数(unsigned)。另外一个CONVERT(data,type)可实现相同功能。

7、字符串拼接函数

Oracle中用"||"来连接字符串,如:id || ‘[’ || name || ']'

MySQL中用concat()函数来实现,如:concat(id,'[',name,']'),当然这个函数在Oracle中依旧可用

以上就是鸟哥在做数据库语法转换时所遇到的差异,整理如上,当然因为Oracle的强大,其中很多函数是MySQL所不支持的,这时候我们也只能通过MySQL中的自定义函数来实现相同的功能啦。

三、其他整理

1、MySQL可以识别单引号和双引号,Oracle中只能识别单引号(Oracle中双引号内容会被解释为对象而不是字段,故在Oracle中使用select * from table where name ="张三" 会报列名无效)。

2、MySQL中当前时间对应函数now(),Oracle中对应sysdate

3、ROWNUM问题

        (1)Oracle通过rownum获取前n条记录,rownum还可以作为where的一部分。MySQL中通过limit来获取前n条记录,limit不是where的一部分。

        (2)Oracle中select 后面跟上rownum字段后,会返回的结果中创建一列Number的计数列;MySQL中的实现方式则为:select @rownum:=@rownum+1,列名 from (@rownum:=0) a,表名。

4、with用法

在Oracle中可以通过with as 语法构建一个临时表,但在MySQL中没有这种用法,想要实现想用的功能,可通过小括号处理,且必须起别名。

但是!在MySQL8.0之后的版本已经支持WITH AS 的语法结构啦,这一点上的差异就不存在了。

5、分组统计后汇总

        (1)Oracle中使用group by rollup(a,b)

        (2)MySQL中使用 group by  a,b with  rollup

6、别名问题

        在xml中写sql时发现,Oracle在子句后面有没有别名均可,但是MySQL的子句后面一定要有别名,否则报错,因此,为了规范化起见,最好统一对子句添加别名,也让逻辑看着更清楚。

7、大小写敏感问题

        Oracle中对表名、字段名、别名大小写均不敏感;

        MySQL在linux上对表名和别名大小写敏感,对字段大小写不敏感,不过可以在建表时通过语句进行设置。

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!