oracle转mysql语句转换

场景：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] whenselectcase1when1then'out1'when2then'out2'end;-- out1selectcase2when1then'out1'when2then'out2'end;-- out2selectcase3when1then'out1'when2then'out2'end;-- null-- mysql: 2. case when [expr]selectcasewhen1>0then'out0'end;-- out01和2的区别在于，1中case后只能跟列，而2中casewhen后是可以跟表达式的，适合复杂情况

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);-- 0

nvl中null和''都输出1，是因为oracle中，空值''即为nullselect''from dual;-- null
而ifnull中''输出''，是与oracle不同的，因此在进行函数替换的时候，要注意，
如果条件值不为''时，才与nvl等价。如数字类型不会为''

如果param可能为''时，需要用casewhen等价替换，例如：
selectcase param when''then'empty'whennullthen'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) selectif(1,123,'null');-- 123selectif('',123,'null');-- nullselectif(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 selectif(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'ASdecimal(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()aschar);-- [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()SELECTDATE(NOW());-- [DATE]2023-02-21SELECTDATE('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  ->  mysql
YYYYmmdd ->%Y%m%d
YYYY-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')-1from dual;-- 2020-12-31 00:00:00-- mysqlselect str_to_date('20210101','%Y%m%d')-1from 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'),interval1day);-- 2020-12-31select date_add(str_to_date('20210101','%Y%m%d'),interval1day);-- 2021-01-02

其中interval后可跟如下时间单位：
FRAC_SECOND
SECONDMINUTEHOURDAY
WEEK
MONTH
QUARTER
YEAR

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'),interval1month);--2021-02-01-- mysql SELECT TIMESTAMPADD(SQL_TSI_MONTH,1, str_to_date('20210101','%Y%m%d'));-- 2021-02-01

tips:
mybatis中使用date_add时，一旦里卖弄interval关键字，总是会sql编译失败，
不知道是否和版本还是什么有关。

使用TIMESTAMPADD函数不会报错。

interval-type间隔类型参数如下：
SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_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.00from dual;-- 1select LENGTH(0),0from dual;-- 1select LENGTH(0.000001),123456from 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:34

tips:
可以看出，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中组合调用