Mysql与Oracle语法差异大盘点,不是最全面但求更全面!
文编|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上对表名和别名大小写敏感,对字段大小写不敏感,不过可以在建表时通过语句进行设置。