一、前言

因项目需求，小编要将项目从mysql迁移到oracle中 ~

之前已经完成 数据迁移 ()

现在将完成 基于MyBatis-Plus将项目中的MySQL语句全部转换成Oracle语句

大概实现步骤：

将项目改成支持双库配置(因项目基于mysql数据库已经全部完成，也不想直接替换掉，于是新增oracle库，让整个项目可支持多个数据库，这里不是多数据源哦！)Oracle中创建常用函数遍历项目中的xxxMapper.xml文件，找到mysql与oracle语句的区别，然后替换绝大部分SQL最后将一些特殊的mysql语句手动修改为oracle语句二、MyBatis-Plus 支持双库配置 【mysql，oracle】

1、application.yml中配置mybatis-plus的database-id

# mybatis-plus配置mybatis-plus:  configuration:    jdbc-type-for-null: 'null' # 解决oracle更新数据为null时无法转换报错    database-id: oracle # 支持多库配置 mysql,oracle

2、MybatisPlus核心配置文件 -> 根据不同的数据库厂商执行不同的SQL

@Configuration@MapperScan("com.zhengqing.demo.modules.**.mapper*")public class MybatisPlusConfig {    /**     * `xxxMapper.xml`文件中的`databaseId`会自动识别使用的数据库类型与这里相对应     * 注: 如果没有指定`databaseId`则该SQL语句适用于所有数据库哦~     *      * databaseIdProvider：支持多数据库厂商     * VendorDatabaseIdProvider: 得到数据库厂商的标识(驱动getDatabaseProductName())，mybatis就能根据数据库厂商标识来执行不同的sql;     * MySQL，Oracle，SQL Server,xxxx     */    @Bean    public DatabaseIdProvider getDatabaseIdProvider(){        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();        Properties properties = new Properties();        // 为不同的数据库厂商起别名        properties.setProperty("MySQL","mysql");        properties.setProperty("Oracle","oracle");        databaseIdProvider.setProperties(properties);        return databaseIdProvider;    }}

3、xxxMapper.xml中通过databaseId指定数据库类型

<select id="selectUserInfo" resultMap="UserVO" databaseId="mysql">    SELECT * FROM 表名 LIMIT 1</select><select id="selectUserInfo" resultMap="UserVO" databaseId="oracle">    SELECT * FROM 表名 WHERE ROWNUM <= 1</select>

三、Oracle中创建常用函数

这里根据个人项目情况去实际应用即可~

1、ORACLE_TO_UNIX

Oracle时间 Date类型转换为Unix时间戳，等同于mysql中的UNIX_TIMESTAMP

create or replace function ORACLE_TO_UNIX(in_date IN DATE) return number is  begin  return( ROUND( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600, 0) );end ORACLE_TO_UNIX;

2、FIND_IN_SET

CREATE OR REPLACE FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')RETURN NUMBER IS        l_idx    number:=0; -- 用于计算piv_str2中分隔符的位置    str      varchar2(500);  -- 根据分隔符截取的子字符串    piv_str  varchar2(500) := piv_str2; -- 将piv_str2赋值给piv_str    res      number:=0; -- 返回结果    loopIndex number:=0;BEGIN  -- 如果piv_str中没有分割符，直接判断piv_str1和piv_str是否相等，相等 res=1  IF instr(piv_str, p_sep, 1) = 0 THEN     IF piv_str = piv_str1 THEN         res:= 1;     END IF;  ELSE  -- 循环按分隔符截取piv_str  LOOP     l_idx := instr(piv_str,p_sep);       loopIndex:=loopIndex+1;      -- 当piv_str中还有分隔符时        IF l_idx > 0 THEN              -- 截取第一个分隔符前的字段str           str:= substr(piv_str,1,l_idx-1);              -- 判断 str 和piv_str1 是否相等，相等 res=1 并结束循环判断           IF str = piv_str1 THEN              res:= loopIndex;             EXIT;           END IF;          piv_str := substr(piv_str,l_idx+length(p_sep));        ELSE        -- 当截取后的piv_str 中不存在分割符时，判断piv_str和piv_str1是否相等，相等 res=1          IF piv_str = piv_str1 THEN              res:= loopIndex;          END IF;          -- 无论最后是否相等，都跳出循环          EXIT;        END IF;  END LOOP;  -- 结束循环  END IF;  -- 返回res  RETURN res;  END FIND_IN_SET; 

四、工具类（MySQL语句转换Oracle语句）

替换步骤：

在 xxxMapper.xml 中将所有sql语句上加入 databaseId="mysql"复制一份mysql的sql（即 将替换的oracle语句）在复制的sql上加入databaseId="oracle"找出mysql与oracle语句区别，然后替换sql

温馨小提示: 这里工具类只供参考，实际操作根据自己的项目做修改哦，操作前建议先备份自己的项目，以防操作不当丢失代码哦！

import org.apache.commons.lang3.StringUtils;import org.junit.Test;import java.io.*;import java.util.*;/** *  <p> mysql迁移oracle 测试工具类 </p> * * @description :   * @author : zhengqing   * @date : 2020/1/08 10:10 */public class MySQLToOracleTest {    private final static String ORACLE_SQL = "    <!-- ====================================== ↓↓↓↓↓↓ oracle ↓↓↓↓↓↓  ====================================== -->";    @Test // 替换项目中的sql语句    public void testSQL() throws Exception {        String path = System.getProperty("user.dir") + "\\src\\main\\java\\com\\zhengqing\\xxx"; // TODO 这里替换为自己的项目路径        File file = new File(path);        HashMap<Object, Object> fileMap = new HashMap<>();        getAllFileByRecursion(fileMap, file);        fileMap.forEach((key, value) -> {            String fileXmlName = (String) key;            File fileXml = (File) value;            String filePath = fileXml.getPath();            if (fileXmlName.equals("Test.xml")) {                System.out.println(filePath);                try {                    // 1、加入 databaseId="mysql"                    addMysql(filePath);                    // 2、复制一份oracle的sql                    if (!checkHasOracle(filePath)) {                        copyMysqlToOracle(filePath);                    }                    // 3、加入 databaseId="oracle"                    addOracle(filePath);                    // 4、替换mybatis `xxxMapper.xml` 中的sql语句                    repalceSQL(filePath);                } catch (IOException e) {                    e.printStackTrace();                }            }        });        System.out.println(fileMap);    }    /**     * 替换mybatis `xxxMapper.xml` 中的sql语句     */    private static void repalceSQL(String path) throws IOException {        File file = new File(path);        FileReader in = new FileReader(file);        BufferedReader bufIn = new BufferedReader(in);        // 内存流, 作为临时流        CharArrayWriter tempStream = new CharArrayWriter();        // 替换        String line = null;        int row = 0;        int rowOracle = 0;        while ((line = bufIn.readLine()) != null) {            row++;            if (line.contains(ORACLE_SQL)) {                rowOracle = row;            }            if (rowOracle != 0 && row > rowOracle) {                // ① 替换 `LIMIT` -> `AND ROWNUM <= 1` TODO 【注： 部分包含`ORDER BY` 关键字，需单独处理】                if (line.contains("limit") || line.contains("LIMIT")) {                    System.out.println();                    System.out.println(" ==============================↓↓↓↓↓↓  copy分页所需 (" + row + ")  ↓↓↓↓↓↓===================================== ");                    System.out.println("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (");                    System.out.println();                    System.out.println(") TMP  WHERE ROWNUM <=1) WHERE ROW_ID > 0");                    System.out.println();                }                line = StringUtils.replace(line, "limit 1", "AND ROWNUM <= 1");                line = StringUtils.replace(line, "LIMIT 1", "AND ROWNUM <= 1");                line = StringUtils.replace(line, "limit 0,1", "AND ROWNUM <= 1");                line = StringUtils.replace(line, "LIMIT 0,1", "AND ROWNUM <= 1");                // ② oracle中不能使用“ ` ”符号                line = StringUtils.replace(line, "`", "");                // ③ CONCAT('%', #{name}, '%')  ->     '%'||#{name}||'%' （Oracle中concat函数只能放两个参数）                if (line.contains("concat")) {//                    String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);                    line = line.replaceAll(",", " || ");                    line = line.replaceAll("concat", "");                }                if (line.contains("CONCAT")) {//                    String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);                    line = line.replaceAll(",", " || ");                    line = line.replaceAll("CONCAT", "");                }                // ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳                line = line.replaceAll("UNIX_TIMESTAMP", "ORACLE_TO_UNIX");                // ⑤ 部分关键字需加上双引号 TODO 【注： 字段名大写，映射的别名需保存原本小写！】 `level -> "LEVEL"`  `user -> "USER"`      `number -> "NUMBER"`  `desc -> "DESC"`                List<String> keywordList = new ArrayList<>(Arrays.asList("level", "user", "number"));                if (!line.contains("test=")) {                    for (String e : keywordList) {                        // StringUtils.swapCase(e) : 大小写互换                        line = line.replaceAll(" " + e + " ", " \"" + StringUtils.swapCase(e) + "\" ");                        line = line.replaceAll("." + e + " ", "\\.\"" + StringUtils.swapCase(e) + "\" ");                        if (line.endsWith(e) || line.endsWith(e + ",")) {                            line = line.replaceAll(e, "\"" + StringUtils.swapCase(e) + "\"");                        }                    }                }                if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {                    line = line.replaceAll(" date", " \"date\"");                    line = line.replaceAll(" date,", " \"date,\"");                    line = line.replaceAll(" 'date'", " \"date\"");                    line = line.replaceAll(" 'DATE'", " \"date\"");                    line = line.replaceAll(" DATE", " \"date\"");                }                line = line.replaceAll(" date ", " \"date\" ");                line = line.replaceAll(" DATE ", " \"date\" ");                // ⑥ `IFNULL` -> `NVL`                line = line.replaceAll("IFNULL", "NVL");                line = line.replaceAll("ifnull", "NVL");                // ⑦ 时间 `str_to_date` -> `to_date`     `date_format` -> `to_char`                // `%Y-%m-%d`  -> `yyyy-MM-dd`    `%Y-%m` -> `yyyy-MM`                line = line.replaceAll("str_to_date", "TO_DATE");                line = line.replaceAll("STR_TO_DATE", "TO_DATE");                line = line.replaceAll("date_format", "TO_CHAR");                line = line.replaceAll("DATE_FORMAT", "TO_CHAR");                // 这里注意替换顺序问题，最长的应该放最前面！！！                line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");                line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");                line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");                line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");                line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");                line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");                line = line.replaceAll("%Y-%m", "yyyy-MM");                line = line.replaceAll("%Y", "yyyy");                line = line.replaceAll("%H", "HH24");                line = line.replaceAll("%k", "HH24");                line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");                line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");                // ⑧ ...                // 需手动处理的SQL 【 group by | 批量插入 | ... 】            }            // 将该行写入内存            tempStream.write(line);            // 添加换行符            tempStream.append(System.getProperty("line.separator"));        }        // 关闭 输入流        bufIn.close();        // 将内存中的流 写入 文件        FileWriter out = new FileWriter(file);        tempStream.writeTo(out);        out.close();    }    /**     * 加入 databaseId="mysql"     */    private static void addMysql(String path) throws IOException {        File file = new File(path);        FileReader in = new FileReader(file);        BufferedReader bufIn = new BufferedReader(in);        // 内存流, 作为临时流        CharArrayWriter tempStream = new CharArrayWriter();        // 替换        String line = null;        while ((line = bufIn.readLine()) != null) {            if ((line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) && !line.contains("databaseId")) {                if (line.endsWith(">")) {                    line = line.replaceAll(">", " databaseId=\"mysql\">");                } else {                    line = line + " databaseId=\"mysql\"";                }            }            // 将该行写入内存            tempStream.write(line);            // 添加换行符            tempStream.append(System.getProperty("line.separator"));        }        // 关闭 输入流        bufIn.close();        // 将内存中的流 写入 文件        FileWriter out = new FileWriter(file);        tempStream.writeTo(out);        out.close();    }    /**     * 加入 databaseId="oracle"     */    private static void addOracle(String path) throws IOException {        File file = new File(path);        FileReader in = new FileReader(file);        BufferedReader bufIn = new BufferedReader(in);        // 内存流, 作为临时流        CharArrayWriter tempStream = new CharArrayWriter();        HashSet<String> lineSet = new HashSet<>();        // 替换        String line = null;        while ((line = bufIn.readLine()) != null) {            if (line.contains("databaseId=\"mysql\"")) {                if (lineSet.contains(line)) {                    line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"oracle\"");                }                lineSet.add(line);            }            // 将该行写入内存            tempStream.write(line);            // 添加换行符            tempStream.append(System.getProperty("line.separator"));        }        // 关闭 输入流        bufIn.close();        // 将内存中的流 写入 文件        FileWriter out = new FileWriter(file);        tempStream.writeTo(out);        out.close();    }    /**     * 复制一份oracle的sql     */    private static void copyMysqlToOracle(String path) throws IOException {        File file = new File(path);        FileReader in = new FileReader(file);        BufferedReader bufIn = new BufferedReader(in);        // 内存流, 作为临时流        CharArrayWriter tempStream = new CharArrayWriter();        // 替换        String line = null;        // 需要替换的行        List<String> lineList = new LinkedList<>();        int row = 0;        int firstRow = 0;        while ((line = bufIn.readLine()) != null) {            row++;            if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) {                firstRow = row;            }            // 添加替换内容            if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>")) {                lineList.add(line);            }            // 查询结束位置            if (line.contains("</mapper>")) {                tempStream.append(System.getProperty("line.separator"));                tempStream.write(ORACLE_SQL);                tempStream.append(System.getProperty("line.separator"));                tempStream.append(System.getProperty("line.separator"));                lineList.forEach(lineValue -> {                    // copy mysql 语句 转为oracle                    try {                        tempStream.write(lineValue);                        tempStream.append(System.getProperty("line.separator"));                    } catch (IOException e) {                        e.printStackTrace();                    }                });                tempStream.append(System.getProperty("line.separator"));            }            // 将该行写入内存            tempStream.write(line);            // 添加换行符            tempStream.append(System.getProperty("line.separator"));        }        // 关闭 输入流        bufIn.close();        // 将内存中的流 写入 文件        FileWriter out = new FileWriter(file);        tempStream.writeTo(out);        out.close();    }    /**     * 检查是否已经复制SQL     */    private static boolean checkHasOracle(String path) throws IOException {        File file = new File(path);        FileReader in = new FileReader(file);        BufferedReader bufIn = new BufferedReader(in);        // 内存流, 作为临时流        CharArrayWriter tempStream = new CharArrayWriter();        // 替换        String line = null;        boolean result = false;        while ((line = bufIn.readLine()) != null) {            if (line.contains(ORACLE_SQL)) {                result = true;            }            // 将该行写入内存            tempStream.write(line);            // 添加换行符            tempStream.append(System.getProperty("line.separator"));        }        // 关闭 输入流        bufIn.close();        // 将内存中的流 写入 文件        FileWriter out = new FileWriter(file);        tempStream.writeTo(out);        out.close();        return result;    }    /**     * 递归文件夹 -> 找到所有xml文件     */    private static void getAllFileByRecursion(HashMap<Object, Object> fileMap, File file) {        File[] fs = file.listFiles();        for (File f : fs) {            String fileName = f.getName();            if (f.isDirectory()) {                // 若是目录则递归，否则打印该目录下的文件                getAllFileByRecursion(fileMap, f);            }            if (f.isFile() && fileName.endsWith(".xml")) {                fileMap.put(fileName, f);            }        }    }}

五、总结

这里简单说下MySQL和Oracle中的SQL区别，以及mysql语句转换oracle语句示例

1、分页

mysql: LIMIT 0,1oracle: ROWNUM <= 1情景①：mysql中不含ORDER BY

-- mysqlSELECT * FROM 表名 LIMIT 1-- oracleSELECT * FROM 表名  WHERE ROWNUM <= 1

情景②：mysql中含ORDER BY

-- mysqlSELECT * FROM 表名 ORDER BY 字段名 DESC LIMIT 1 -- oracleSELECT * FROM (     SELECT TMP.*, ROWNUM ROW_ID FROM (         SELECT * FROM 表名 ORDER BY 字段名 DESC    ) TMP WHERE ROWNUM <= 1 ) WHERE ROW_ID > 0;

温馨小知识：SQL SELECT语句执行顺序

FROM子句组装来自不同数据源的数据WHERE子句基于指定的条件对记录进行筛选GROUP BY子句将数据划分为多个分组聚集函数进行计算HAVING子句筛选分组计算所有表达式ORDER BY对结果进行排序2、oracle中字段名不能使用符号 " ` " 包括

-- mysqlSELECT `字段名` FROM 表名-- oracleSELECT 字段名 FROM 表名

3、字符串拼接

注： Oracle中CONCAT函数只能放两个参数，因此改为 || 拼接

mysql: CONCAT('%', 'xxx' , '%')oracle: '%' || 'xxx' || '%'

-- mysqlSELECT 字段名 FROM 表名 WHERE 字段名 LIKE CONCAT('%','helloworld','%')-- oracleSELECT 字段名 FROM 表名 WHERE 字段名 LIKE ('%' || 'helloworld' || '%')

4、date类型时间转10位时间戳

mysql: UNIX_TIMESTAMPoracle: ORACLE_TO_UNIX (注：此函数为步骤三中手动创建的，并非oracle自带哦！)5、字段名为Oracle关键字需加上双引号

温馨小提示： 字段名需大写，如果Java实体类对应字段为小写，映射的别名注意需保持原本小写与之对应 ~

例如：

level -> "LEVEL"user -> "USER"number -> "NUMBER"desc -> "DESC"date -> DATE6、判断是否为 NULL: 如果x为NULL,则返回value,否则返回x值本身

mysql: IFNULL(x, value)oracle: NVL(x, value)7、日期时间互换

前mysql，后oracle

字符串类型转时间类型： STR_TO_DATE -> TO_DATE时间类型转指定字符串类型： DATE_FORMAT -> TO_CHAR获取系统当前时间： NOW() -> SELECT SYSDATE FROM DUAL

-- 时间类型转指定字符串类型SELECT DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s');             -- mysqlSELECT TO_CHAR( SYSDATE,'yyyy-MM-dd HH24:mi:ss') FROM DUAL; -- oracle-- 字符串类型转时间类型SELECT STR_TO_DATE( NOW(), '%Y-%m-%d %H');             -- mysqlSELECT TO_DATE( '2020-01-09', 'yyyy-MM-dd') FROM DUAL; -- oracle 【 注：oracle中前者字符串时间的格式需与后者转换格式相同哦~ 】-- 获取系统当前时间SELECT NOW(); -- mysqlSELECT SYSDATE + 8/24 FROM DUAL; -- oralce 【注：如果服务器时间没有相差8小时则无需加上`8/24`】-- mysqlSELECT YEAR( NOW() );     -- 求年份SELECT QUARTER( NOW() );  -- 求季度SELECT MONTH( NOW() );    -- 求月份-- oracleSELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; -- 求季度

另外这里给出小编所用到的时间标识符格式

-- 前：mysql             后：oracle"%Y-%m-%d %H:%i:%S"     "yyyy-MM-dd HH24:mi:ss""%Y-%m-%d %H:%i:%s"     "yyyy-MM-dd HH24:mi:ss""%Y-%m-%d %H:%i"        "yyyy-MM-dd HH24:mi""%Y-%m-%d %H"           "yyyy-MM-dd HH24""%Y-%m-%d %h"           "yyyy-MM-dd HH""%Y-%m-%d"              "yyyy-MM-dd""%Y-%m"                 "yyyy-MM""%Y"                    "yyyy""%H"                    "HH24""%k"                    "HH24"

8、判断时 左 右 字段类型必须相同

这里注意是必须，可能在oracle版本不同的情况下，老版本不同类型也会查询出来，但建议还是改为相同类型关联，避免以后数据库版本升级出现问题！！！

建议小转大，比如：数字转字符串；并使用CONCAT去修改类型，因为mysql和oracle都支持此函数，并且不会在特殊类型上出现问题 ~

-- ex: `JOIN` 关联表时 两张表的关联`字段类型`必须`相同`SELECT a.*,b.*FROM 表1 aLEFT JOIN 表2 b on a.字符串类型字段 = CONCAT(b.数字类型字段, '')

9、批量插入

-- mysql<insert id="insertBatch" databaseId="mysql">    INSERT INTO 表名( `字段名1`, `字段名2`, `字段...`) VALUES    <foreach collection="list" item="item" separator="," open="(" close=")">        #{item.字段1},#{item.字段2},#{item中的每一个字段名...}    </foreach></insert>-- oracle<insert id="insertBatch" databaseId="oracle">    INSERT INTO 表名(字段名1,字段名2,xxx...)    SELECT A.*    FROM(        <foreach collection="list" item="item" index="index" separator="UNION ALL" >            SELECT                #{item.字段1},#{item.字段2},#{item中的每一个字段名...}            FROM DUAL        </foreach>    ) A</insert>

10、分组 GROUP BY

oracle中GROUP BY分组后，查询出来的所有字段（除分组字段）必须为聚合函数的字段，否则会报错！

解决：

查询字段改为聚合函数使用如下分析函数 OVER (Partition BY ...) 及开窗函数

-- mysqlSELECT 字段名,xxx... FROM 表名 GROUP BY 分组字段-- oracleSELECT    *FROM (    SELECT tb.*, ROW_NUMBER ( ) OVER ( PARTITION BY tb.分组字段 ORDER BY tb.排序字段 DESC ) AS result    FROM (        SELECT 字段名,xxx... FROM 表名 -- 此处为查询sql，去掉`GROUP BY`分组条件，将分组字段加到上面 【 注：此sql的查询字段中要么全是聚合函数字段，要么都不是！ 】    ) tb) WHERE result = 1

11、Oracle中表的别名不能用AS, 列的别名可以用AS

why ?：为了防止和Oracle存储过程中的关键字AS冲突的问题

12、注意Oracle对数据类型要求很严！！！

MySQL与Oracle不同之处远不止小编提及的如上几点，更多的还需要大家根据在实际项目中做对比，然后去修改哦 ~

六、本文MySQL转Oracle语句工具源码

温馨小提示：如果后期小编空闲会将上文中提供的测试替换工具类再加以修改，到时候会存放在如下github仓库中...