Oracle和Mysql语法异同整理笔记

墨蓝 2021-09-18 01:40 476阅读 0赞

### 文章目录 ###

*   *   *  (1) 模糊匹配对比
         *  (2) 删除数据细节
         *  (3) 时间函数对比
         *  (4) 关键字问题
         *  (5) 递归查询
         *  (6) 排序问题
         *  (7) NVL和IFNULL函数
         *  (8) 取最大值对比
         *  (9) 列转行函数
         *  (10) 类型转行函数
         *  (11) 派生查询带别名
         *  (12) mysql rownum写法
         *  (13) select ... from dual
         *  (14) Mysql is not null
         *  (15) 批量Merge对比

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

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

### (1) 模糊匹配对比 ###

Oracle的模糊匹配和mysql是不同的，在mybatis项目里，用Oracle，我们可能会这样写：`where a like '%'|| #{参数} ||'%'`，不过放在mysql就不兼容了，mysql的做法是这样的`where a like concat('%', #{参数} ,'%')`  
或者用instr函数，如下代码：  
`SELECT column from table where instr(condition, ‘keyword’ )>0`

### (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’)

备注：而对于oracle中的`to_char(${日期},'yyyy-mm-dd hh24:mi:ss')`，在mysql中因为没有to\_char函数，所以可以用`date_format(${日期},'%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) NVL和IFNULL函数 ###

业务是这样的，加入查询到一个参数为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的做法NVL可以用`IFNULL(参数,0)`函数替代，NVL2函数可以用`IF ( ISNULL(字段名) , 0 , 1 )`

### (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的用group\_concat或者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类型，所以就略有不同了

代码示例：

select cast(#{userid} as signed integer) from sys_user;

### (11) 派生查询带别名 ###

如果sql，在oracle是可以正常查询的，不过在mysql5版本会报错

select count(1) from (select a.id , a.name from A)

> 异常：  
> Every derived table must have its own alias

原因是mysql规定子查询必须带个别名，所以sql可以改为：

select count(1) from (select a.id , a.name from A)  t

详情可以参考我之前博客：https://smilenicky.blog.csdn.net/article/details/100545991

### (12) mysql rownum写法 ###

mysql中是没有rownum这个关键字的，给个sql：

这是在oracle中的写法：

select * from (select id,name from t) where rownum <![CDATA[<=]]> to_number(num);

mysql要怎么替换？方法就是用临时变量来实现：

SELECT 
      * 
    FROM
      (SELECT 
        tb.*,
        @rownum := @rownum + 1 AS rownum 
      FROM
        (SELECT 
          id,
          NAME 
        FROM
          t) tb,
        (SELECT 
          @rownum := 0) r) AS t 
    WHERE rownum <= CAST(num AS SIGNED INTEGER) ;

详情参考我之前博客：https://smilenicky.blog.csdn.net/article/details/100554870

### (13) select … from dual ###

dual在oracle中是一个神奇的写法，比如`select function() from dual;`从自定义函数function里获取数据，oracle要求必须加个表在from后面，而MySQL是不需要的，直接`select function();`就可以的

### (14) Mysql is not null ###

这里介绍一下Oracle和mysql的is not null，这两个是不同的

*  oracle : 包括了null值的数据和空字符串数据
 *  mysql：只包括了(NULL)值数据，不包括空字符串的数据

所以，mysql要实现类似Oracle is not null效果的替换写法可以为：

select * from user where ISNULL(name)=0 and LENGTH(trim(name))>0;

显然，下面写法也是可以的

select * from user where name is not null and name !='';

显然，mysql要实现类似Oracle is null效果的替换写法可以为：

select * from user where ISNULL(name)=1 or LENGTH(trim(name))=0;

显然，下面写法也是可以的

select * from user where name is null or name == '';

### (15) 批量Merge对比 ###

oracle merge合并更新函数的详细介绍可以参考我以前的博客：https://blog.csdn.net/u014427391/article/details/87898729

oracle方法：

<insert id="batchSaveTipsRelatedConfig" databaseId="oracle" parameterType="com.extend.tipsConfig.model.TipsRelatedModel">
    		MERGE INTO t_tips_config_related A1 
    		USING(<foreach collection="relatedTipsList" item="itm" separator="union">select #{itm.seq} seq ,
    					 #{itm.tipsSeq} tipsSeq , 
    					 #{itm.relaSeq} relaSeq,
    					 #{itm.tipsContent} tipsContent
    		          from dual
    			</foreach>) A2
    		ON(A1.tips_seq = A2.tipsSeq and A1.rela_seq = A2.relaSeq)
    		WHEN MATCHED THEN
    		UPDATE SET
    		        A1.tips_content = A2.tipsContent
    		WHEN NOT MATCHED THEN
    		INSERT(seq , tips_seq ,  rela_seq, tips_content)
    		VALUES(A2.seq , A2.tipsSeq ,A2.relaSeq, A2.tipsContent)
    	</insert>

mysql方法：

<insert id="batchSaveTipsRelatedConfig" databaseId="mysql" parameterType="com.extend.tipsConfig.model.TipsRelatedModel">
    		REPLACE INTO t_tips_config_related(seq,tips_seq,rela_seq,tips_content)  
    		VALUES<foreach collection="relatedTipsList" item="itm" separator=",">(#{itm.seq},
    					 #{itm.tipsSeq}, 
    					 #{itm.relaSeq},
    					 #{itm.tipsContent})</foreach>
    	</insert>