文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

oracle转mysql语句转换

2023-08-17 13:33

关注

场景:Oracle数据库 -> TD数据库(腾讯),而TD数据库实质上是对mysql数据库的封装拓展。因此,需要对项目中oracle的sql进行适配性转化,即在sql查询结果不变的前提下,转换成符合mysql语法的sql语句。
tips:其实TD数据库8.0以上版本的语法大部分是可以完美兼容oracle语法的。但是我们的版本是5.7,因此许多函数和语法是不兼容oracle。如果你的td数据库版本在8以上,就不用往下看了。

一、oracle函数 -> mysql函数

1. decode -> case when

-- oracle: decodeselect decode(1, 1, 'out1', 2, 'out2') from dual; -- out1select decode(2, 1, 'out1', 2, 'out2') from dual; -- out2select decode(3, 1, 'out1', 2, 'out2') from dual; -- null--mysql: 1. case [col_name] whenselect case 1 when 1 then 'out1' when 2 then 'out2' end; -- out1select case 2 when 1 then 'out1' when 2 then 'out2' end; -- out2select case 3 when 1 then 'out1' when 2 then 'out2' end; -- null-- mysql: 2. case when [expr]select case when 1 > 0  then 'out0' end; -- out01和2的区别在于,1中case后只能跟列,而2中case when后是可以跟表达式的,适合复杂情况

2. NVL(exp1,exp2) -> ifnull

-- oracle: nvl(param, val)select nvl(null, 1) from dual; -- 1select nvl('', 1) from dual;   -- 1select nvl('0', 1) from dual;  -- 0-- mysql: ifnull(param, val)select ifnull(null,1);-- 1select ifnull('',1);  -- ''select ifnull('0',1); -- 0nvl中null和''都输出1,是因为oracle中,空值''即为nullselect '' from dual;  -- null而ifnull中''输出'',是与oracle不同的,因此在进行函数替换的时候,要注意,如果条件值不为''时,才与nvl等价。如数字类型不会为''如果param可能为''时,需要用case when等价替换,例如:select case param when '' then 'empty' when null then 'nul' else 'val end';

3. NVL2(exp1,exp2,exp3) -> if(expr1,expr2,expr3)

-- oracle nvl2(exp1,exp2,exp3) select nvl2(1, '123', 'null') from dual;  -- 123select nvl2('', '123', 'null') from dual;  -- nullselect nvl2(null, '123', 'null') from dual;  -- null--mysql if(param,notNullVal,nullVal) select if(1, 123, 'null');-- 123select if('', 123, 'null');-- nullselect if(null, 123, 'null');-- null此时看起来是等同的,那就大错特错了。接下来看数据库实际情况:-- oacle: int_col_0 是int类型 值为0 select nvl2(int_col_0, 123, null) from demo where id = '1'; -- 123-- mysql: int_col_0 是int类型 值为0 select if(int_col_0, 123, null) from demo where id = '1'; -- null这时候两个函数的结果就不一样了。原因如下:if(param,notNullVal,nullVal)函数中,如果param是varchar类型,当param值为''时,会输出后值;如果param时int类型,当param值为0时,会输出后值;当param是decimal类型,当param为0.0时,会输出后者。得出结论:当param的值为其数据类型的默认值时,if()函数会走后者。因此,在param数据类型为varchar时,与nvl2函数是等价的。

4. TO_NUMBER(exp) -> CAST(value AS datatype)

-- 这个没什么讲的,可以直接用,在mysql中cast函数用于将数据类型强转为其它数据类型类似于java。select CAST('123' AS decimal(5,1) ); -- 123.0

5. to_char
a. to_char(date, dateFormatType) -> date_format(date, dateFormatType)

-- oracle: to_char(date, dateFormatType)select to_char(sysdate, 'yyyymmdd') from dual; -- 20230222-- mysql: date_format(date, dateFormatType)select date_format(now(), '%Y%m%d'); -- 20230222

b. to_char(str) -> CAST(value AS char)

-- oracle to_charselect to_char(sysdate) from dual; -- [varchar2] 22-2月 -23--mysql: cast(value AS char)select cast(now() as char ); -- [varcahr]2023-02-22 09:10:02

6. TRUNC() -> DATE()

-- oracle trunc()SELECT TRUNC(sysdate) from DUAL; -- [DATE]2023-02-21 00:00:00SELECT TRUNC(to_date('20210101','yyyymmdd')) from DUAL; -- [DATE]2021-01-01 00:00:00-- mysql date()SELECT DATE(NOW()); -- [DATE]2023-02-21SELECT DATE('20210201'); -- [DATE]2021-02-01

7. to_date -> str_to_date

-- oracle: to_dateselect to_date('20210101', 'yyyymmdd') from dual; -- 2021-01-01 00:00:00-- mysql : str_to_dateselect str_to_date('20210101', '%Y%m%d'); -- 2021-01-01

8. 常用时间格式转换

oracle  ->  mysqlYYYYmmdd -> %Y%m%dYYYY-mm-dd -> %Y-%m-%d yyyy-mm-dd hh24:mi:ss -> %Y-%m-%d %H:%i:%s

9. 时间计算方式
a. 日期+/-1天

-- oracle select to_date('20210101', 'yyyymmdd') - 1 from dual; -- 2020-12-31 00:00:00-- mysqlselect str_to_date('20210101', '%Y%m%d') - 1 from dual; -- 20210100从结果可以看出是错误的,只是单纯-1,并没有校验时间是否正确。-- mysql adddate(date, [num])select adddate(str_to_date('20210101', '%Y%m%d'), -1); -- 2020-12-31-- mysql date_sub/date_add(date, interval [num] [day/month/year...])select date_sub(str_to_date('20210101', '%Y%m%d'), interval 1 day ); -- 2020-12-31select date_add(str_to_date('20210101', '%Y%m%d'), interval 1 day ); -- 2021-01-02其中interval后可跟如下时间单位:FRAC_SECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEAR

b. 日期+/-1月/年

-- oracle  add_months(date, num)select add_months(to_date('20210101','yyyymmdd'), 1) from dual; -- 2021-02-01 00:00:00-- mysql date_add(date,, interval [num] month)select date_add(str_to_date('20210101', '%Y%m%d'), interval 1 month ); --2021-02-01-- mysql SELECT TIMESTAMPADD(SQL_TSI_MONTH, 1, str_to_date('20210101', '%Y%m%d')); -- 2021-02-01tips:mybatis中使用date_add时,一旦里卖弄interval关键字,总是会sql编译失败,不知道是否和版本还是什么有关。使用TIMESTAMPADD函数不会报错。interval-type间隔类型参数如下:SQL_TSI_FRAC_SECONDSQL_TSI_SECONDSQL_TSI_MINUTESQL_TSI_HOURSQL_TSI_DAYSQL_TSI_WEEKSQL_TSI_MONTHSQL_TSI_QUARTERSQL_TSI_YEAR

10. SUBSTR() -> SUBSTRING()

-- 截取字符串:注意:-- oracleselect SUBSTR('123456', 1, 3)、SUBSTR('123456', 0, 3) from dual; -- 输出都是123;-- mysqlselect SUBSTRING('123456', 1, 3); -- 输出123  select  SUBSTRING('123456', 0, 3); --输出空值''

11. 拼接符号|| -> concat(str, str1)

-- mysql concat(str1,str2,...)select concat('1','2','3','4','A'); -- 1234A

12. length() -> char_length()

-- oracle length()select char_length(''); -- nullselect LENGTH('123'), '123' from dual; -- 3select LENGTH(0.00), 0.00 from dual; -- 1select LENGTH(0), 0 from dual; -- 1select LENGTH(0.000001), 123456 from dual; -- 7select LENGTH(sysdate), sysdate from dual; -- 9 2023-02-22 01:23:39-- mysql char_lengthselect char_length(''); -- 0select char_length(123); -- 3select char_length(0); -- 1select char_length(0.0); -- 3select char_length(0.000001); -- 8select char_length(now()), now(); -- 19  2023-02-22 09:18:34tips:可以看出,oracle的length()对于字符串的长度计算结果是符合我们预期的,但是数字类型和并不会把小数点的长度加上。且当为0.0000时,会自动转会为0再做长度计算,得出结果为1.时间格式时掺长度我也不确定是怎么得出的。mysql的char_length()结果就比较符合我们的期望。会把所有参数都转化为char类型后,再进行长度计算。注意:当字符串计算长度时,如果字符串为空'',oracle-length()的结果为null,mysql-char_length的结果为0。

二、oracle语法 -> mysql语法

有些语法也不是通用的,下面是经常见到的几种。
1. 临时表with as
解决方案1:换成子查询放到表调用处
解决方案2:在java中拆分,减少sql复杂度。

2. 左右连接(+)写法
解决方案:根据原sql where后的表连接条件,有(+)的换成left join,没有的换成inner join

3. 递归 connect by prior
解决方案:这块内容比较多,另开了一篇:[‘@实现mysql递归查询’]
4. merge语法
解决方案:sql拆成查询、更新、插入三个语句。在java中组合调用

来源地址:https://blog.csdn.net/supuerlovepc/article/details/129157280

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯