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: decode
select decode(1, 1,  out1 , 2,  out2 ) from dual; -- out1
select decode(2, 1,  out1 , 2,  out2 ) from dual; -- out2
select decode(3, 1,  out1 , 2,  out2 ) from dual; -- null

--mysql: 1. case [col_name] when
select case 1 when 1 then  out1  when 2 then  out2  end; -- out1
select case 2 when 1 then  out1  when 2 then  out2  end; -- out2
select 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; -- out0

1和2的区别在于，1中case后只能跟列，而2中case when后是可以跟表达式的，适合复杂情况

NVL(exp1,exp2) -> ifnull

-- oracle: nvl(param, val)
select nvl(null, 1) from dual; -- 1
select nvl(  , 1) from dual;   -- 1
select nvl( 0 , 1) from dual;  -- 0

-- mysql: ifnull(param, val)
select ifnull(null,1);-- 1
select ifnull(  ,1);  --   
select ifnull( 0 ,1); -- 0

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

如果param可能为  时，需要用case when等价替换，例如：
select case param when    then  empty  when null then  nul  else  val end ;

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

-- oracle nvl2(exp1,exp2,exp3) 
select nvl2(1,  123 ,  null ) from dual;  -- 123
select nvl2(  ,  123 ,  null ) from dual;  -- null
select nvl2(null,  123 ,  null ) from dual;  -- null

--mysql if(param,notNullVal,nullVal) 
select if(1, 123,  null );-- 123
select if(  , 123,  null );-- null
select 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函数是等价的。

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

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

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_char
select 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

TRUNC() -> DATE()

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

-- mysql date()
SELECT DATE(NOW()); -- [DATE]2023-02-21
SELECT DATE( 20210201 ); -- [DATE]2021-02-01

to_date -> str_to_date

-- oracle: to_date
select to_date( 20210101 ,  yyyymmdd ) from dual; -- 2021-01-01 00:00:00

-- mysql : str_to_date
select str_to_date( 20210101 ,  %Y%m%d ); -- 2021-01-01

常用时间格式转换

oracle  ->  mysql
YYYYmmdd -> %Y%m%d
YYYY-mm-dd -> %Y-%m-%d 
yyyy-mm-dd hh24:mi:ss -> %Y-%m-%d %H:%i:%s

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

-- oracle 
select to_date( 20210101 ,  yyyymmdd ) - 1 from dual; -- 2020-12-31 00:00:00

-- mysql
select 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-31
select date_add(str_to_date( 20210101 ,  %Y%m%d ), interval 1 day ); -- 2021-01-02

其中interval后可跟如下时间单位：
FRAC_SECOND
SECOND
MINUTE
HOUR
DAY
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 ), interval 1 month ); --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

SUBSTR() -> SUBSTRING()

-- 截取字符串:
注意：
-- oracle
select SUBSTR( 123456 , 1, 3)、SUBSTR( 123456 , 0, 3) from dual; -- 输出都是123；

-- mysql
select SUBSTRING( 123456 , 1, 3); -- 输出123  
select  SUBSTRING( 123456 , 0, 3); --输出空值

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

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

length() -> char_length()

-- oracle length()
select char_length(  ); -- null
select LENGTH( 123 ),  123  from dual; -- 3
select LENGTH(0.00), 0.00 from dual; -- 1
select LENGTH(0), 0 from dual; -- 1
select LENGTH(0.000001), 123456 from dual; -- 7
select LENGTH(sysdate), sysdate from dual; -- 9 2023-02-22 01:23:39

-- mysql char_length
select char_length(  ); -- 0
select char_length(123); -- 3
select char_length(0); -- 1
select char_length(0.0); -- 3
select char_length(0.000001); -- 8
select 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语法
有些语法也不是通用的，下面是常常见到的几种。