Oracle和Mysql语法异同整理笔记

2019-02-16 本文已影响10人 smileNicky

@[toc]
最近在做项目迁移，Oracle版本的迁到Mysql版本，遇到有些oracle的函数，mysql并没有，所以就只好想自定义函数或者找到替换函数的方法进行改造。

所以本博客主要介绍Oracle兼容mysql改造方式以及注意事项，也就是介绍原本Oracle一些函数在Mysql的替换方法等等，适合给原本是Oracle版本的项目，想兼容Mysql版本。

(1) 模糊匹配

Oracle的模糊匹配和mysql是不同的，在mybatis项目里，用Oracle，我们可能会这样写：where a like '%'|| #{参数} ||'%'，不过放在mysql就不兼容了，mysql的做法是这样的where a like concat('%', #{参数} ,'%')

(2) 删除数据

一个小细节，在Oracle里删除数据，delete 表格 t where t.id = '?' 或者 delete from 表格 t where t.id = '?' 不加关键字from或者用和不用别名t都是可以的，不过放在mysql5.X就不可以了，其它Mysql版本没验证过，Mysql版本要求必须加关键字from同时不能加别名 delete from 表格 where id = '?'

(3) 时间函数

Oracle的时间函数和Mysql的时间函数是不同的，Oracle的格式是to_date('2019-02-12 14:20:22', 'yyyy-mm-dd hh24:mi:ss')，Mysql的格式是str_to_date('2019-02-12 11:34:32', '%Y-%m-%d %H:%i:%s')

(4) 关键字问题

在Oracle还是mysql建表的时候，一般都不要用数据库关键字做表的字段，比如Order，CONDITION等等，特别是mysql就直接报错。假如用CONDITION做表字段，在写入数据的时候就要用CONDITION，加“`”符号，例子

insert into table (UUID, `CONDITION`, FLAG,CREATE_TIME)
values ('EAF472C6332241FBBBB22A37336BBD65', '', '${voteLZ_Boolean}', 'control/doApprFlowNew', str_to_date('13-06-2018 17:38:33', '%d-%m-%Y %H:%i:%s'));

(5) 递归查询

oracle实现递归查询的话，就可以使用start with … connect by，mysql并没有通过类似函数，解决方法是通过自定义函数的方法，具体可以参考我这篇博客
https://blog.csdn.net/u014427391/article/details/87297884

(6) 排序问题

oracle做数据排序的时候，有时候可以用nulls first或者nulls last将null值排在最前或者最后。
不过迁到Mysql的话，mysql并没有提供类似函数，所以要怎么实现？下面给出解决方法：
null值排在最后，用Mysql的IF和ISNULL函数。如果为空返回1，否返回0

select * from A order by IF(ISNULL(a),1,0),a desc

null值排在最前，用Mysql的IF和ISNULL函数。如果为空返回0，否返回1

select * from A order by IF(ISNULL(a),0,1),a desc

具体可以参考我写的这篇博客：https://blog.csdn.net/u014427391/article/details/87297068

(7) 空值返回0

业务是这样的，加入查询到一个参数为null的话，就返回0，在Oracle里，可以用nvl函数nvl(参数,0)，意思是参数为null，就返回0，避免空指针报错

介绍一下oracle的nvl函数和nvl2函数。

nvl函数
nvl函数基本语法为nvl(E1,E2)，意思是E1为null就返回E2，不为null就返回E1。

nvl2函数
nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。
nvl函数具体可以参考我这篇博客：https://blog.csdn.net/u014427391/article/details/84996009

上面是Oracle的做法，mysql的做法可以用IFNULL(参数,0)函数

(8) 取最大值

Oracle和Mysql取最大值都可以用max函数，不过Oracle有提供partition by和开窗函数的方法实现去最大值，开窗函数具体可以参考我以前的博客：https://blog.csdn.net/u014427391/article/details/85412064
Oracle例子

<select id="getMaxVaue" parameterType="java.lang.String" resultType="java.lang.Integer" databaseId="oracle" >
        select nvl(参数,0)
          from (select 参数,
                       row_number() over(partition by id order by 参数 desc) rn
                  from 表格
                 ) t
         where t.rn = 1
    </select>

Mysql例子

<select id="getMaxValue" parameterType="java.lang.String" resultType="java.lang.Integer" databaseId="mysql" >
        select IFNULL(MAX(参数),0) from 表格
    </select>

(9) 列转换函数

Oracle列转行函数可以用vm_comcat函数，mysql的用concat_ws函数，语法类似

Oracle列转行函数的可以参考我以前博客：https://blog.csdn.net/u014427391/article/details/84981114

列转行函数不兼容问题:https://blog.csdn.net/u014427391/article/details/84867390

(10) 类型转行函数

Oracle类型转换函数cast语法是类似的，不过Oracle是没有int类型，只有number类型，所以就略有不同了