关于Oracle、MySql通用标准SQL整理

1、 前言

最近项目需要将使用mysql语法项目适配oracle环境，由于使用了不少mysql语法导致对接oracle环境一直报错。将近期遇到的问题做一些总结归纳：

2、limit、rownum

 这两个聚合函数是用来帮助查找前多少行或者指定的某些行的数据，类似于分页功能：
 其中 mysql支持 limit语法 ，而rownum是oracle的语法，因此在编写标准sql时，不要用这两个聚合函数去实现查找某些数据的功能。
 limit:语法有：
    （1）select * from table limit n  查询前n行数据
    （2）select * from table limit m,n 查询 从m+1至m+n行数据
    （3）select * from table limit m,-1  查询 m+1至最后一行数据
  rownum语法：
    （1）select * from table where rownum<n 查询前n行数据
    （2）select * from (select rownum r, t.* from table t where rownum<=n) where r>=m   查询 m至n行数据

使用pagehelper 分页插件实现分页查找功能： eg:

private List<String> qryListByName(String name) {
        List<String> list = new ArrayList<String>();
        // 原有 sql 语法为 limit 1 使用分页实现改功能
        Integer pageNum = 0;
        Integer pageSize = 1;
        PageHelper.startPage(pageNum, pageSize, true);
        List<String> qryResult= demoMapper.qryByName(name);
        PageInfo listPage = new PageInfo<>(qryResult);
        // 得到分页查询后的 list 数据
        return listPage.getList();
    }

3、Group By

在mysql和oracle中 group by 对不同的数据库语法不同：因此在编写适配mysql和oracle的通用sql时需要注意：
其中在mysql中 可以按照下面根据名字进行分组：

select id,name,sex,phone from user group by name;

但是在oracle中上面的sql语句时不支持的，即对于select 后面的字段 在group by后面必须都要出现，否则就会报 ORA-00979: not a GROUP BY expression错误：

那么我们在oracle语法中只想根据name对数据进行分组，并且查询到 id,name,sex等字段的内容的话需要如何做呢？
可以参考下面的方法：

  select a.id,a.name,a.sex,a.phone from user a where (a.name,a.id) in (select b.name, min(b.id) id from user b group by b.name)  

更多其他写法参考: link

4、日期函数

本次修改问题最多的就是日期函数了，原来的逻辑在sql语法中使用了大量的 now函数获取数据库时间，在对接oracle的时候就嗝屁了，直接报 now 未标识。在oracle数据库中使用 sysdate函数获取时间，
因此 我们需要将数据库系统时间已参数的形式传递到sql语句中 进行动态拼接。其中获取数据库系统时间一般来说 各个公司的框架都会有相应的函数去获取。
如果 碰到真没有的话，可以设置一个系统配置项标识当前连接数据库是什么类型的数据库，然后根据配置项的值 分别根据对于的数据库 去获取：
eg:

DateTimeMapper.xml

    <select id="getCurrentDBDate" resultType="java.util.Date">
        <if test="dbType != null and dbType == 'Oracle'.toString()">
            select sysdate from dual
        </if>
        <if test="dbType != null and dbType == 'Mysql'.toString()">
            select now()
        </if>
    </select>

DateUtil.java

package com.date.util;

import com.date.DateTimeMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class DateUtil {
    @Autowired
    DateTimeMapper dateTimeMapper;

    /**
     *  获取数据库系统时间
     * @return
     */
    public Date getCurrentDBDate() {
        String dbType = "Mysql";
        if (DbUtil.isOracle()) {
            dbType = "Oracle";
        }
        else if (DbUtil.isMysql() || DbUtil.isUdal() || DbUtil.isDrds()) {
            dbType = "Mysql";
        }

        return dateTimeMapper.getCurrentDBDate(dbType);
    }
}

还要注意的是在mysql和oracle中计算两个时间差的函数也不一样，因此不要在sql 中直接使用 只支持各自数据库类型的语法。
其次还有一点就是 需要注意 使用mybatis将一个时间date作为参数传入进去时 ，需要注意时间的格式，如果时间格式不正确 也会报错：
格式化时间的方法如下：

package com.test.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateFormatUtil {
    public static Date parseDateToFormat(String params) {
        Date result = null;
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        try {
            if (null != params && !"".equals(params)) {
                result = sdf2.parse(params);
            }
        }
        catch (ParseException e) {
            // error..............
        }
        return result;
    }
}

5、自增序号

插入数据时 主键id的序号生成方式 mysql和 oracle 都支持 在插入之前先获取对于的序号：

oracle :

 create sequence seq_atable minvalue 1 maxvalue 9999999 start with 1 increment by 1 nocache

mysql：
mysql有自增字段的类型（auto_increment）

在使用mybatis 进行插入数据时 如果已经获取到自增序号id后，不要在 下面的语法中书写 keyProperty=“id” useGeneratedKeys=“true”，该语法在mysql 具有自增长方式的数据库支持，但是在oracle数据库中 这种写法是会报错的，因此如果在插入数据时 已获取自增序号，则不要编写 keyProperty=“id” useGeneratedKeys="true"在mybatis中。

<insert id="insert"  keyProperty="id"  useGeneratedKeys="true">
</insert>