oracle转mysql工具类

通过mian方法直接转换你本地的sql文件，实用工具，记得收藏

package com.datalook.util.common;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLTransformUtils;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.*;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLMethodInvokeExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.oracle.ast.expr.OracleSysdateExpr;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.*;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleToMySqlOutputVisitor;
import com.alibaba.druid.util.FnvHash;
import com.datalook.util.log.LogUtil;
import org.apache.commons.lang3.tuple.Pair;

import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.alibaba.druid.util.FnvHash.Constants.*;

/**
 * Oracle语句转换为MySQL的工具
 *
 */
public class SqlTransforUtil {

    private static Map<String, String> funcMapper = new HashMap<>();
    private static LinkedHashMap<String, String> oracle2mysqlMap = new LinkedHashMap<>();
    private static LinkedHashSet<String> notOracle2mysqlSet = new LinkedHashSet<>();

    static {
        funcMapper.put("nvl", "ifnull");
        funcMapper.put("NVL", "IFNULL");

        /*
        YY  年的最后两位数字，如：22
        YYY 年的最后三位数字，如：022
        YYYY    年，如：2022
        SYYYY   年
        IY  ISO年的最后两位数字，如：22
        IYY ISO年的最后三位数字，如：022
        IYYY    ISO年，如：2022
        RR  两位数字年，如：22
        RRRR    四位数字年，如：2022
        MM  Month (01-12)
        D   Day of week (1-7)
        DD  Day of month (1-31)
        DDD Day of year (001-366)
        HH  Hour of day (1-12)
        HH12    Hour of day (1-12)
        HH24    Hour of day (0-23)
        MI  Minute (0-59)
        SS  Second (0-59)
        FF [1..9]   毫秒
        DS  日期简称，如：2022-03-15
        W   Week of month (1-5)
        */
        oracle2mysqlMap.put("SYYYY", "%Y");//2022
        oracle2mysqlMap.put("YYYY", "%Y");//2022
        oracle2mysqlMap.put("IYYY", "%Y");//2022
        oracle2mysqlMap.put("YY", "%y");//22
        oracle2mysqlMap.put("IY", "%y");//22
        oracle2mysqlMap.put("RRRR", "%Y");//2022，年
        oracle2mysqlMap.put("RR", "%y");//22
        oracle2mysqlMap.put("MM", "%m");//03，月
        oracle2mysqlMap.put("DDD", "%b");//075，03月16号是2022年的第075天
        oracle2mysqlMap.put("DD", "%d");//16，日
        oracle2mysqlMap.put("HH24", "%H");//13，小时
        oracle2mysqlMap.put("HH12", "%h");//01，13点是下午01点
        oracle2mysqlMap.put("HH", "%I");//01，13点是下午01点
        oracle2mysqlMap.put("MI", "%i");//02，分钟
        oracle2mysqlMap.put("SS", "%s");//05，秒
        oracle2mysqlMap.put("FF6", "%f");//122330，毫秒，6位
        oracle2mysqlMap.put("FF5", "%f");//12233，毫秒，5位
        oracle2mysqlMap.put("FF4", "%f");//1223，毫秒，4位
        oracle2mysqlMap.put("FF3", "%f");//122，毫秒，3位
        oracle2mysqlMap.put("FF2", "%f");//12，毫秒，2位
        oracle2mysqlMap.put("FF1", "%f");//1，毫秒，1位
        oracle2mysqlMap.put("FF", "%f");//122330，毫秒
        oracle2mysqlMap.put("DS", "%Y-%m-%d");//日期简称，如：2022-03-15
        oracle2mysqlMap.put("W", "%w");//3，周，2022-03-16是3月的第3周

        notOracle2mysqlSet.add("RR");
        notOracle2mysqlSet.add("MON");
        notOracle2mysqlSet.add("MONTH");
        notOracle2mysqlSet.add("RM");
        notOracle2mysqlSet.add("SSSS");
        notOracle2mysqlSet.add("DL");
        notOracle2mysqlSet.add("AM");
        notOracle2mysqlSet.add("A.M.");
        notOracle2mysqlSet.add("PM");
        notOracle2mysqlSet.add("P.M.");
        notOracle2mysqlSet.add("DAY");
        notOracle2mysqlSet.add("DY");
        notOracle2mysqlSet.add("IW");
        notOracle2mysqlSet.add("TS");
    }

    public static boolean isNotSupport(String sql) {
        if (org.apache.commons.lang3.StringUtils.isBlank(sql)) {
            return false;
        }
        return notOracle2mysqlSet.stream().anyMatch(a -> sql.toUpperCase().contains(a));
    }

    /**
     * oracle的日期函数转换为mysql
     */
    public static String oracle2MysqlDate(String sql) {
        String[] code = new String[]{"to_char", "to_date", "to_timestamp"};
        for (String item : code) {
            sql = oracle2mysqlFormat(sql, item);
        }
        sql = specialFunc(sql);
        return sql;
    }

    private static String oracle2mysqlFormat(String sql, String type) {
        Pattern pattern = Pattern.compile(type, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            return sql;
        }
        String formatSql = sql;
        int sqlIndex = -1;
        //去除大小写特殊函数的大小写的影响
        if ("to_char".equalsIgnoreCase(type)) {
            formatSql = matcher.replaceFirst("to_char");
            sqlIndex = formatSql.indexOf("to_char");
        } else if ("to_date".equalsIgnoreCase(type)) {
            formatSql = matcher.replaceFirst("to_date");
            sqlIndex = formatSql.indexOf("to_date");
        } else if ("to_timestamp".equalsIgnoreCase(type)) {
            formatSql = matcher.replaceFirst("to_timestamp");
            sqlIndex = formatSql.indexOf("to_timestamp");
        }

        if (-1 == sqlIndex) {
            return formatSql;
        }
        String startSql = formatSql.substring(0, sqlIndex);
        String endSql = formatSql.substring(sqlIndex);
        int startNum = 1, endNum = -1;
        //初始位置括号的位置
        int startIndex = endSql.indexOf("(") + 1;
        char[] matchChar = new char[]{'(', ')'};
        int endSqlLength = endSql.length();
        for (int i = startIndex; i < endSqlLength; i++) {
            char charAt = endSql.charAt(i);
            if (charAt == matchChar[1]) {
                startNum--;
            }
            if (charAt == matchChar[0]) {
                startNum++;
            }
            if (startNum == 0) {
                endNum = i;
                break;
            }
        }
        if (startNum != 0) {
            throw new RuntimeException("oracle转换mysql的sql语句出错");
        }
        String matcherStr = endSql.substring(0, endNum + 1);
        String endStr = endSql.substring(endNum + 1);
        if (matcherStr.contains(",")) {
            matcherStr = oracle2mysqlFormat(matcherStr);
        } else if ("to_char".equalsIgnoreCase(type)) {
            //to_char(123)这样格式的转换，数字转字符串
            matcherStr = numberToChar(matcherStr);
            matcherStr = pattern.matcher(matcherStr).replaceFirst("cast");
        }
        //字符串的替换
        Matcher matcherNew = pattern.matcher(matcherStr);
        if ("to_char".equalsIgnoreCase(type) || "to_timestamp".equalsIgnoreCase(type)) {
            matcherStr = matcherNew.replaceFirst("date_format");
        } else if ("to_date".equalsIgnoreCase(type)) {
            matcherStr = matcherNew.replaceFirst("str_to_date");
        }
        String newSql = startSql + matcherStr + endStr;
        //是否还有未匹配的字段
        if (pattern.matcher(endStr).find()) {
            String newMatcherEnd = oracle2mysqlFormat(endStr, type);
            newSql = startSql + matcherStr + newMatcherEnd;
        }
        return newSql;
    }

    private static String oracle2mysqlFormat(String sql) {
        String[] arr = sql.split(",");
        if (arr.length > 1) {
            String[] arr2 = arr[1].replace(")", "").split("'");
            for (String str : arr2) {
                if (org.apache.commons.lang3.StringUtils.isBlank(str)) {
                    continue;
                }
                String value = oracle2mysqlMap.get(str.toUpperCase());
                if (org.apache.commons.lang3.StringUtils.isNotBlank(value)) {
                    sql = sql.replace(str, value);
                } else {
                    for (Map.Entry<String, String> item : oracle2mysqlMap.entrySet()) {
                        if (sql.toUpperCase().contains(item.getKey())) {
                            sql = Pattern.compile(item.getKey(), Pattern.CASE_INSENSITIVE).matcher(sql).replaceFirst(item.getValue());
                        }
                    }
                }
            }
        }
        return sql;
    }

    private static String numberToChar(String sql) {
        int index = sql.indexOf("(");
        int indexEnd = sql.lastIndexOf(")");
        String str = sql.substring(index, indexEnd);
        sql = sql.replace(str, str + " as char");
        return sql;
    }

    private static String specialFunc(String sql) {
        //处理nvl函数
        Pattern pattern = Pattern.compile("nvl\\s*\\(", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            sql = matcher.replaceAll("ifnull(");
        }

        //处理sysdate函数
        pattern = Pattern.compile(",\\s*sysdate\\s*,", Pattern.CASE_INSENSITIVE);
        matcher = pattern.matcher(sql);
        if (matcher.find()) {
            StringBuilder sb = new StringBuilder();
            String[] arr = sql.split(",");
            for (String item : arr) {
                if (item.trim().toLowerCase().equals("sysdate")) {
                    item = " sysdate() ";
                }
                sb.append(item).append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            sql = sb.toString();
        }
        pattern = Pattern.compile("\\s+sysdate\\s+", Pattern.CASE_INSENSITIVE);
        matcher = pattern.matcher(sql);
        if (matcher.find()) {
            sql = matcher.replaceAll(" sysdate() ");
        }

        return sql;
    }

    public static void main(String[] args) {
        /*String sql = " nvl(a.r,0) r, b.nvller, nvl(a.t,0) t and  t.r>= to_timestamp('2022-01-25 12:14:11.213','yyyy-mm-dd hh24:mi:ss.ff6') " +
                " a.inputdate <= to_date('2022-03-16','yyyy-mm-dd') and a.inputdate >= to_date('2021-03-16','yyyy-mm-dd')" +
                " and to_char(settdate,'yyyy-MM-dd')<='2022-03-15'" +
                " and to_char(txdate,'yyYy=Mm==dd日')<='2022-03-15'";
        sql = oracle2MysqlDate(sql);
        System.out.println(sql);*/

       // createTableData("D:\\DKcode\\datalook-mobile-core\\doc\\数据库脚本\\01-create.sql");
       initData("C:\\Users\\gyl19\\Desktop\\转账单支付卡成本变更.sql");
        //updateData("F:\\temp\\sql\\07-update-2022.sql");
    }

    /**
     * 处理create Table 脚本：01-createTable.sql
     */
    public static void createTableData(String path) {
        String writePath = path + ".mysql.sql";
        testOracle2Mysql(path, writePath);
    }

    /**
     * 处理init data 脚本：03-initdata.sql
     */
    public static void initData(String path) {
        String writePath = path + ".mid.sql";
        dealInitData(path, writePath);

        String writePath2 = path + ".mysql.sql";
        testOracle2Mysql(writePath, writePath2);
    }

    /**
     * 处理日常维护类脚本：07-update-xxxx.sql
     */
    public static void updateData(String path) {
        String writePath = path + ".mysql.sql";
        testOracle2Mysql(path, writePath);
    }

    /**
     * 处理init.sql数据
     */
    private static void dealInitData(String path, String writePath) {
        List<String> list = readFile(path);
        List<String> result = new ArrayList<>(list.size());
        for (String sql : list) {
            if (!isNote(sql)) {
                String str = deleteOracleTimeMethod(sql);
                result.add(str);
            }
        }
        try {
            writeFile(writePath, result);
        } catch (Exception e) {
            LogUtil.error(e);
        }
    }

    /**
     * 删除insert语句中的特殊的oracle函数
     */
    private static String deleteOracleTimeMethod(String sql) {
        String ret = sql;
        String lower = sql.toLowerCase();
        String[] arr = new String[]{"to_date", "to_timestamp"};
        for (String item : arr) {
            int indexStart = lower.indexOf(item);
            if (indexStart > -1) {
                String startSql = sql.substring(0, indexStart);
                String cStr = sql.substring(indexStart);
                int indexEnd = cStr.indexOf(")");
                String str = cStr.substring(0, indexEnd + 1);
                String str2 = str.split(",")[1];
                boolean isNotSupport = isNotSupport(str2);
                if (isNotSupport) {
                    //String newStr = item + "(sysdate,'YYYY-MM-DD HH24:MI:SS')";
                    //String newStr = "NULL";
                    String newStr = "sysdate";
                    String endSql = sql.substring(sql.indexOf(str) + str.length());
                    if (endSql.toLowerCase().contains(item)) {
                        endSql = deleteOracleTimeMethod(endSql);
                    }
                    ret = startSql + newStr + endSql;
                }

            }
        }
        boolean ok = Pattern.compile("timestamp\\s+'[0-9]+", Pattern.CASE_INSENSITIVE).matcher(ret).find();
        if (ok) {
            String arr2 = "timestamp";
            String lower2 = ret.toLowerCase();
            int indexStart = lower2.indexOf(arr2);
            if (indexStart > -1) {
                String startSql = sql.substring(0, indexStart);
                String cStr = sql.substring(indexStart);
                int indexEnd = cStr.indexOf(",");
                String str = cStr.substring(0, indexEnd);
                //String newStr = "to_timestamp('sysdate','YYYY-MM-DD HH24:MI:SS')";
                //String newStr = "NULL";
                String newStr = "sysdate";
                String endSql = sql.substring(sql.indexOf(str) + str.length());
                if (endSql.contains(arr2)) {
                    endSql = deleteOracleTimeMethod(endSql);
                }
                ret = startSql + newStr + endSql;
            }
        }
        return ret;
    }

    /**
     * 是否为废弃的insert、update等的注释
     */
    private static boolean isNote(String sql) {
        if (sql.startsWith("--")) {
            String[] arr = new String[]{
                    "create\\s+table\\s+\\S+", "create\\s+index\\s+\\S+\\s+on\\s+\\S+", "create\\s+\\S+\\s+index\\s+\\S+\\s+on\\s+\\S+"
                    , "alter\\s+table\\s+\\S+", "update\\s+\\S+", "insert\\s+into\\s+\\S+", "delete\\s+from\\s+\\S+", "delete\\s+\\S+"
            };

            for (String item : arr) {
                boolean ok = Pattern.compile(item, Pattern.CASE_INSENSITIVE).matcher(sql).find();
                if (ok) {
                    return true;
                }
            }

        }
        return false;
    }

    private static LinkedHashMap<Integer, Pair<String, String>> transformBeforeFilter(String path, List<String> strList) {
        LinkedHashMap<Integer, Pair<String, String>> retMap = new LinkedHashMap<>();
        List<String> filterList = new ArrayList<>();
        List<String> deleteList = new ArrayList<>();
        for (String item : strList) {
            String lower = item.toLowerCase();
            if (lower.startsWith("--") &&
                    (lower.contains("create ") || lower.contains("update ") || lower.contains("delete ") || lower.contains("insert ") || lower.contains("alter "))) {
                deleteList.add(item);
            } else {
                filterList.add(item);
            }
        }
        try {
            writeFile(path + ".delete.sql", deleteList);
            writeFile(path + ".filter.sql", filterList);
        } catch (IOException e) {
            e.printStackTrace();
        }

        StringBuilder sqlSb = new StringBuilder();
        HashSet<String> noteSet = new HashSet<>();
        String sqText = "";
        int key = 1;
        int count = filterList.size();
        for (int i = 0; i < count; i++) {
            String str = filterList.get(i);
            if (!str.startsWith("--")) {
                sqlSb.append(str).append("\n");
                if (i == count - 1) {
                    retMap.put(key, Pair.of(sqText, sqlSb.toString()));
                }
            } else {
                if (str.length() > 2) {
                    str = "-- " + str.substring(2);
                }
                if (noteSet.contains(str)) {
                    str = str + " re";
                }
                noteSet.add(str);
                String sql = sqlSb.toString().replaceAll("\\s", "");
                if (StringUtils.hasText(sql)) {
                    retMap.put(key, Pair.of(sqText, sqlSb.toString()));
                    key++;
                }
                sqlSb.setLength(0);
                sqText = str;
            }
        }
        return retMap;
    }

    private static int getMapCount(Map<String, List<String>> model) {
        int count = 0;
        for (List<String> item : model.values()) {
            count += item.size();
        }
        return count;
    }

    private static void transformAfterSum(String readFilePath, TransformModel model) {
        TransformModel srcSumRet = sumSql(readFilePath);
        System.out.println("----------------------------------");
        System.out.println(" 原文件create table数量: " + getMapCount(srcSumRet.createMap) + " 转换后的create table数量: " + getMapCount(model.createMap));
        System.out.println(" 原文件insert数量: " + getMapCount(srcSumRet.insertMap) + " 转换后的insert数量: " + getMapCount(model.insertMap));
        System.out.println(" 原文件update数量: " + getMapCount(srcSumRet.updateMap) + " 转换后的update数量: " + getMapCount(model.updateMap));
        System.out.println(" 原文件delete数量: " + getMapCount(srcSumRet.deleteMap) + " 转换后的delete数量: " + getMapCount(model.deleteMap));
        System.out.println(" 原文件alter数量: " + getMapCount(srcSumRet.alterMap) + " 转换后的alter数量: " + getMapCount(model.alterMap));
        System.out.println(" 原文件create index数量: " + getMapCount(srcSumRet.indexMap) + " 转换后的create index数量: " + getMapCount(model.indexMap));
        System.out.println("----------------------------------");

        for (String item : model.createMap.keySet()) {
            System.out.println("drop table if exists " + item + ";");
        }

        for (String item : model.insertMap.keySet()) {
            System.out.println("delete from " + item + ";");
            /*if ("sys_organ_initdata".equals(item)) {
                List<String> values = model.insertMap.get(item);
                for (String str : values) {
                    if (str.toLowerCase().contains("gateway_config_type")) {
                        System.out.println(str);
                    }
                }
            }*/
        }

        printSum(srcSumRet.createMap, model.createMap, "create table");
        printSum(srcSumRet.insertMap, model.insertMap, "insert");
        printSum(srcSumRet.updateMap, model.updateMap, "update");
        printSum(srcSumRet.deleteMap, model.deleteMap, "delete");
        printSum(srcSumRet.indexMap, model.indexMap, "create index");
        printSum(srcSumRet.alterMap, model.alterMap, "alter");

    }


    private static void printSum(LinkedHashMap<String, List<String>> src, LinkedHashMap<String, List<String>> model, String msg) {
        Set<String> tempSet = null;
        List<String> srcList = new ArrayList<>();
        List<String> modelList = new ArrayList<>();
        for (List<String> item : src.values()) {
            srcList.addAll(item);
        }
        for (List<String> item : model.values()) {
            modelList.addAll(item);
        }
        if (srcList.size() > modelList.size()) {
            tempSet = src.keySet();
            tempSet.removeAll(model.keySet());
            System.out.println("原" + msg + "数量多，多的表为：" + org.apache.commons.lang3.StringUtils.join(tempSet, ","));
            for (String item : tempSet) {
                System.out.println("表：" + item + "   原语句：");
                for (String a : src.get(item)) {
                    System.out.println(a);
                }
            }
        } else if (srcList.size() < modelList.size()) {
            tempSet = model.keySet();
            tempSet.removeAll(src.keySet());
            System.out.println("转换后的" + msg + "数量多，多的表为：" + org.apache.commons.lang3.StringUtils.join(tempSet, ","));
            for (String item : tempSet) {
                System.out.println("表：" + item + "   转换后的语句：");
                for (String a : model.get(item)) {
                    System.out.println(a);
                }
            }
        }
    }

    private static void testOracle2Mysql(String path, String writePath) {
        TransformModel ret = new TransformModel();
        List<String> strList = readFile(path);
        List<String> sqlMysql = new ArrayList<>();
        LinkedHashMap<Integer, Pair<String, String>> sqlOracleMap = transformBeforeFilter(path, strList);

        //转换create、insert、update、delete、alter等语句
        for (Pair<String, String> item : sqlOracleMap.values()) {
            TransformModel model = oracleTransformMysql(item.getValue());
            for (Map.Entry<String, List<String>> itemModel : model.createMap.entrySet()) {
                List<String> list = ret.createMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            for (Map.Entry<String, List<String>> itemModel : model.insertMap.entrySet()) {
                List<String> list = ret.insertMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            for (Map.Entry<String, List<String>> itemModel : model.updateMap.entrySet()) {
                List<String> list = ret.updateMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            for (Map.Entry<String, List<String>> itemModel : model.deleteMap.entrySet()) {
                List<String> list = ret.deleteMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            for (Map.Entry<String, List<String>> itemModel : model.indexMap.entrySet()) {
                List<String> list = ret.indexMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            for (Map.Entry<String, List<String>> itemModel : model.alterMap.entrySet()) {
                List<String> list = ret.alterMap.computeIfAbsent(itemModel.getKey(), key -> new ArrayList<>());
                list.addAll(itemModel.getValue());
            }
            ret.unknownSqls.addAll(model.unknownSqls);
            ret.notSupportSqls.addAll(model.notSupportSqls);
            sqlMysql.add(item.getKey());
            sqlMysql.add(model.sql);
        }

        List<String> result = new ArrayList<>(sqlMysql.size());
        for (String item : sqlMysql) {
            result.add(oracle2MysqlDate(item));
        }

        try {
            writeFile(writePath, result);
        } catch (IOException e) {
            LogUtil.error(e);
        }

        transformAfterSum(path + ".filter.sql", ret);

    }

    private static TransformModel sumSql(String path) {
        TransformModel ret = new TransformModel();
        List<String> strList = readFile(path);
        for (String item : strList) {
            String tableName = matcherTable(item, "create\\s+table\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> createList = ret.createMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                createList.add(item);
                continue;
            }
            tableName = matcherTable(item, "create\\s+index\\s+\\S+\\s+on\\s+\\S+", "create\\s+\\S+\\s+index\\s+\\S+\\s+on\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> list = ret.indexMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                list.add(item);
                continue;
            }
            tableName = matcherTable(item, "alter\\s+table\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> list = ret.alterMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                list.add(item);
                continue;
            }
            tableName = matcherTable(item, "update\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> list = ret.updateMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                list.add(item);
                continue;
            }
            tableName = matcherTable(item, "insert\\s+into\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> list = ret.insertMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                list.add(item);
                continue;
            }
            tableName = matcherTable(item, "delete\\s+from\\s+\\S+", "delete\\s+\\S+");
            if (StringUtils.hasText(tableName)) {
                List<String> list = ret.deleteMap.computeIfAbsent(tableName.toLowerCase().replaceAll("\"", ""), key -> new ArrayList<>());
                list.add(item);
            }
        }
        return ret;
    }

    private static String matcherTable(String item, String... patternStrs) {
        String tableName = "";
        for (String patternStr : patternStrs) {
            Pattern pattern = Pattern.compile(patternStr, Pattern.CASE_INSENSITIVE);
            Matcher matcher = pattern.matcher(item);
            if (matcher.find()) {
                String str = matcher.group();
                String[] arr = str.split(" ");
                String arrLastStr = arr[arr.length - 1];
                if (arrLastStr.contains("(")) {
                    int last = arrLastStr.indexOf("(");
                    tableName = arrLastStr.substring(0, last).trim();
                } else {
                    tableName = arrLastStr.trim();
                }
                break;
            }
        }
        return tableName;
    }

    private static TransformModel oracleTransformMysql(String sql) {
        TransformModel ret = new TransformModel();
        StringBuilder retSql = new StringBuilder();
        try {
            String notSupportSql = "";
            if (!StringUtils.hasText(sql)) {
                return ret;
            }
            List<SQLStatement> statementList = SQLUtils.parseStatements(sql, DbType.oracle, true);
            StringBuilder out = new StringBuilder();
            OracleToMySqlOutputVisitor visitor = new OracleToMySqlOutputVisitor(out, true);
            Map<String, HashMap<String, String>> tableFiledCommentMap = getTableFiledCommentMap(statementList);
            for (SQLStatement statement : statementList) {
                statement.accept(visitor);
                if (statement instanceof OracleCreateTableStatement) {
                    Pair<String, String> pair = createTable(statement, tableFiledCommentMap);
                    retSql.append(pair.getValue());
                    List<String> createList = ret.createMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    createList.add(pair.getValue());
                } else if (statement instanceof OracleInsertStatement) {
                    Pair<String, String> pair = insert(statement);
                    retSql.append(pair.getValue());
                    List<String> insertList = ret.insertMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    insertList.add(pair.getValue());
                } else if (statement instanceof SQLAlterTableStatement) {
                    Pair<String, String> pair = alterTable(statement, tableFiledCommentMap, notSupportSql);
                    retSql.append(pair.getValue());
                    List<String> list = ret.alterMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    list.add(pair.getValue());
                } else if (statement instanceof SQLUpdateStatement) {
                    Pair<String, String> pair = update(statement);
                    retSql.append(pair.getValue());
                    List<String> list = ret.updateMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    list.add(pair.getValue());
                } else if (statement instanceof SQLDeleteStatement) {
                    Pair<String, String> pair = delete(statement);
                    retSql.append(pair.getValue());
                    List<String> list = ret.deleteMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    list.add(pair.getValue());
                } else if (statement instanceof OracleCreateIndexStatement) {
                    Pair<String, String> pair = createIndex(statement);
                    retSql.append(pair.getValue());
                    List<String> list = ret.indexMap.computeIfAbsent(pair.getKey(), key -> new ArrayList<>());
                    list.add(pair.getValue());
                } else {
                    String curSql = statement.toString();
                    ret.unknownSqls.add(curSql);
                    if (!curSql.trim().toLowerCase().startsWith("comment")) {
                        retSql.append("-- 未知类型的sql未转换，需要再次检查\n").append(curSql).append("\n");
                    }
                }
            }
            if (StringUtils.hasText(notSupportSql)) {
                ret.notSupportSqls.add(notSupportSql);
                retSql.append("\n-- 暂不支持类型的sql未转换，请人工手动转换，需要再次检查\n").append(notSupportSql).append("\n");
                LogUtil.infoLog("\n-- 暂不支持类型的sql未转换，请人工手动转换，需要再次检查\n" + notSupportSql);
            }
        } catch (Exception e) {
            LogUtil.error(e);
            LogUtil.infoLog(sql);
        }

        ret.sql = retSql.toString();
        return ret;
    }

    private static Pair<String, String> createTable(SQLStatement statement, Map<String, HashMap<String, String>> tableFiledCommentMap) {
        StringBuilder createSql = new StringBuilder("create table ");
        OracleCreateTableStatement oracleCreateTableStatement = (OracleCreateTableStatement) statement;
        SQLExpr sqlExpr = oracleCreateTableStatement.getTableSource().getExpr();
        String owner = "", tableName = "";
        if (sqlExpr instanceof SQLPropertyExpr) {
            SQLPropertyExpr propertyExpr = (SQLPropertyExpr) oracleCreateTableStatement.getTableSource().getExpr();
            if (StringUtils.hasText(propertyExpr.getOwnerName())) {
                owner = propertyExpr.getOwnerName().replaceAll("\"", "").toLowerCase();
            }
        }
        tableName = oracleCreateTableStatement.getTableName().replaceAll("\"", "").toLowerCase();
        String key = tableName;
        if (StringUtils.hasText(owner)) {
            key = owner + "." + tableName;
            createSql.append("`").append(owner).append("`.`").append(tableName).append("`(");
        } else {
            createSql.append("`").append(tableName).append("`(");
        }
        HashMap<String, String> map = tableFiledCommentMap.get(key);
        List<SQLTableElement> sqlTableElementList = oracleCreateTableStatement.getTableElementList();

        for (SQLTableElement sqlTableElement : sqlTableElementList) {
            if (sqlTableElement instanceof SQLColumnDefinition) {
                SQLColumnDefinition columnDefinition = (SQLColumnDefinition) sqlTableElement;
                SQLDataType sqlDataType = columnDefinition.getDataType();
                SQLDataType mysqlDataType = SQLTransformUtils.transformOracleToMySql(sqlDataType);
                String fieldName = columnDefinition.getName().toString().replaceAll("\"", "").toLowerCase();
                createSql.append("\n\t`").append(fieldName).append("`");
                createSql.append(" ").append(mysqlDataType.toString());
                boolean isNotNull = false;
                for (SQLColumnConstraint constraint : columnDefinition.getConstraints()) {
                    if (constraint instanceof SQLNotNullConstraint) {
                        isNotNull = true;
                    }
                    if (constraint instanceof SQLColumnPrimaryKey) {
                        createSql.append(" primary key ");
                    } else {
                        createSql.append(" ").append(constraint.toString().replaceAll("\"", "").toLowerCase());
                    }
                }
                SQLExpr defaultExpr = columnDefinition.getDefaultExpr();
                if (defaultExpr != null) {
                    if (defaultExpr instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) defaultExpr;
                        if (identifierExpr.nameHashCode64() == FnvHash.Constants.SYSTIMESTAMP
                                || identifierExpr.nameHashCode64() == SYSDATE) {
                            createSql.append(" ").append("default").append(" ").append("current_timestamp");
                            if (mysqlDataType.getArguments() != null) {
                                SQLExpr expr = mysqlDataType.getArguments().get(0);
                                createSql.append("(").append(expr.toString()).append(")");
                            }
                        }
                    } else {
                        if (defaultExpr.toString().toLowerCase().contains("sysdate")) {
                            createSql.append(" ").append("default").append(" ").append("current_timestamp");
                        } else {
                            createSql.append(" ").append("default").append(" ").append(defaultExpr.toString());
                        }
                    }
                }
                if (map != null && map.containsKey(fieldName)) {
                    createSql.append(" comment ").append(map.get(fieldName));
                }
                createSql.append(",");
            } else if (sqlTableElement instanceof OraclePrimaryKey) {
                OraclePrimaryKey primaryKey = (OraclePrimaryKey) sqlTableElement;
                SQLIndexDefinition indexDefinition = primaryKey.getIndexDefinition();
                if (indexDefinition != null && !indexDefinition.getColumns().isEmpty()) {
                    List<SQLSelectOrderByItem> items = indexDefinition.getColumns();
                    createSql.append("\n\tconstraint ");
                    if (primaryKey.getName() != null) {
                        createSql.append(primaryKey.getName().toString().replace("\"", "").toLowerCase());
                    }
                    createSql.append(" primary key(");
                    for (int i = 0; i < items.size(); i++) {
                        SQLSelectOrderByItem item = items.get(i);
                        createSql.append("`").append(item.getExpr().toString().toLowerCase().replace("\"", "")).append("`,");
                    }
                    createSql.deleteCharAt(createSql.length() - 1);
                    createSql.append("),");
                }
            } else if (sqlTableElement instanceof OracleForeignKey) {
                OracleForeignKey foreignKey = (OracleForeignKey) sqlTableElement;
                createSql.append("\n\tconstraint ").append(foreignKey.getName().toString().replaceAll("\"", "").toLowerCase()).append(" foreign key (");
                for (SQLName item : foreignKey.getReferencingColumns()) {
                    createSql.append("`").append(item.getSimpleName().replaceAll("\"", "").toLowerCase()).append("`,");
                }
                createSql.deleteCharAt(createSql.length() - 1);
                createSql.append(") references ").append(foreignKey.getReferencedTable().toString().replaceAll("\"", "").toLowerCase()).append(" (");
                for (SQLName item : foreignKey.getReferencedColumns()) {
                    createSql.append("`").append(item.getSimpleName().replaceAll("\"", "").toLowerCase()).append("`,");
                }
                createSql.deleteCharAt(createSql.length() - 1);
                createSql.append("),");
            } else if (sqlTableElement instanceof OracleUnique) {
                OracleUnique unique = (OracleUnique) sqlTableElement;
                SQLIndexDefinition indexDefinition = unique.getIndexDefinition();
                if (indexDefinition != null && !indexDefinition.getColumns().isEmpty()) {
                    List<SQLSelectOrderByItem> items = indexDefinition.getColumns();
                    createSql.append("\n\tconstraint ");
                    if (unique.getName() != null) {
                        createSql.append(unique.getName().toString().replaceAll("\"", "").toLowerCase());
                    }
                    createSql.append(" unique (");
                    for (int i = 0; i < items.size(); i++) {
                        SQLSelectOrderByItem item = items.get(i);
                        createSql.append("`").append(item.getExpr().toString().replaceAll("\"", "").toLowerCase().replace("\"", "")).append("`,");
                    }
                    createSql.deleteCharAt(createSql.length() - 1);
                    createSql.append("),");
                }
            }
        }
        createSql.deleteCharAt(createSql.length() - 1);
        createSql.append("\n);\n");
        return Pair.of(key, createSql.toString());
    }

    private static Pair<String, String> insert(SQLStatement statement) {
        StringBuilder insertSql = new StringBuilder("insert into ");
        OracleInsertStatement oracleInsertStatement = (OracleInsertStatement) statement;
        List<SQLInsertStatement.ValuesClause> valuesClauseList = oracleInsertStatement.getValuesList();
        SQLExpr sqlExpr = oracleInsertStatement.getTableSource().getExpr();
        String owner = "", tableName = "";
        if (sqlExpr instanceof SQLPropertyExpr) {
            SQLPropertyExpr propertyExpr = (SQLPropertyExpr) oracleInsertStatement.getTableSource().getExpr();
            if (StringUtils.hasText(propertyExpr.getOwnerName())) {
                owner = propertyExpr.getOwnerName().replaceAll("\"", "").toLowerCase();
            }
            tableName = propertyExpr.getName().replaceAll("\"", "").toLowerCase();
        } else if (sqlExpr instanceof SQLIdentifierExpr) {
            SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) sqlExpr;
            tableName = identifierExpr.getName().replaceAll("\"", "").toLowerCase();
        }
        if (StringUtils.hasText(owner)) {
            insertSql.append("`").append(owner).append("`.");
        }
        insertSql.append("`").append(tableName).append("`(");
        oracleInsertStatement.getColumns().forEach(item -> {
            insertSql.append("`").append(item.toString().toLowerCase().replaceAll("\"", "")).append("`,");
        });
        insertSql.deleteCharAt(insertSql.length() - 1);
        insertSql.append(") values (");
        for (SQLInsertStatement.ValuesClause valuesClause : valuesClauseList) {
            List<SQLExpr> sqlExprList = valuesClause.getValues();
            for (SQLExpr sqlExpr2 : sqlExprList) {
                if (sqlExpr2 instanceof SQLMethodInvokeExpr) {
                    SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) sqlExpr2;
                    SQLDataType sqlDataType = new SQLDataTypeImpl(methodInvokeExpr.getMethodName());
                    long nameHash = sqlDataType.nameHashCode64();
                    if (nameHash == TO_TIMESTAMP || nameHash == TO_DATE || nameHash == TO_CHAR || nameHash == SYSDATE) {
                        insertSql.append("sysdate()");
                    }
                } else if (sqlExpr2 instanceof OracleSysdateExpr) {
                    insertSql.append("sysdate()");
                } else {
                    insertSql.append(sqlExpr2);
                }
                insertSql.append(",");
            }
        }
        insertSql.deleteCharAt(insertSql.length() - 1);
        insertSql.append(");").append("\n");
        return Pair.of(tableName, insertSql.toString());
    }

    private static Pair<String, String> update(SQLStatement statement) {
        StringBuilder sb = new StringBuilder();
        SQLUpdateStatement updateStatement = (SQLUpdateStatement) statement;
        String tableName = updateStatement.getTableName().toString().replaceAll("\"", "").toLowerCase();
        sb.append("-- update类型的sql，需要再次检查\n").append(statement.toString()).append("\n");
        return Pair.of(tableName, sb.toString());
    }

    private static Pair<String, String> delete(SQLStatement statement) {
        StringBuilder sb = new StringBuilder("-- delete类型的sql，需要再次检查\n");
        String tableName = "";
        if (statement instanceof SQLUpdateStatement) {
            SQLUpdateStatement updateStatement = (SQLUpdateStatement) statement;
            tableName = updateStatement.getTableName().toString().replaceAll("\"", "").toLowerCase();
            sb.append(statement.toString()).append("\n");
        } else if (statement instanceof OracleDeleteStatement) {
            OracleDeleteStatement updateStatement = (OracleDeleteStatement) statement;
            tableName = updateStatement.getTableName().toString().replaceAll("\"", "").toLowerCase();
            sb.append(statement.toString()).append("\n");
        }
        return Pair.of(tableName, sb.toString());
    }

    private static Pair<String, String> alterTable(SQLStatement statement, Map<String, HashMap<String, String>> tableFiledCommentMap
            , String notSupportSql) {
        StringBuilder alterSql = new StringBuilder("alter table ");
        SQLAlterTableStatement alterTableModify = (SQLAlterTableStatement) statement;
        String tableName = alterTableModify.getTableSource().toString().replaceAll("\"", "").toLowerCase();
        alterSql.append("`").append(tableName).append("`");
        HashMap<String, String> map = tableFiledCommentMap.get(tableName);
        boolean isModify = false;
        for (SQLAlterTableItem item : alterTableModify.getItems()) {
            List<SQLColumnDefinition> columns = new ArrayList<>();
            if (item instanceof SQLAlterTableAddColumn) {
                alterSql.append(" add (");
                SQLAlterTableAddColumn addColumn = (SQLAlterTableAddColumn) item;
                columns = addColumn.getColumns();
            } else if (item instanceof OracleAlterTableModify) {
                isModify = true;
                alterSql.append(" modify ");
                OracleAlterTableModify modify = (OracleAlterTableModify) item;
                columns = modify.getColumns();
            } else if (item instanceof SQLAlterTableRenameColumn) {
                alterSql.setLength(0);
                notSupportSql = notSupportSql + statement.toString() + "\n";
                LogUtil.infoLog("不支持转换的sql：{}", notSupportSql);
                break;
            } else if (item instanceof SQLAlterTableAddConstraint) {
                alterSql.append(" add constraint ");
                SQLAlterTableAddConstraint addConstraint = (SQLAlterTableAddConstraint) item;
                SQLConstraint constraint = addConstraint.getConstraint();
                alterSql.append(constraint.getName().toString().replaceAll("\"", "").toLowerCase());
                if (constraint instanceof OraclePrimaryKey) {
                    OraclePrimaryKey primaryKey = (OraclePrimaryKey) constraint;
                    alterSql.append(" primary key(");
                    SQLIndexDefinition indexDefinition = primaryKey.getIndexDefinition();
                    if (indexDefinition != null && !indexDefinition.getColumns().isEmpty()) {
                        List<SQLSelectOrderByItem> items = indexDefinition.getColumns();
                        for (int i = 0; i < items.size(); i++) {
                            SQLSelectOrderByItem item2 = items.get(i);
                            alterSql.append("`").append(item2.getExpr().toString().toLowerCase().replace("\"", "")).append("`,");
                        }
                        alterSql.deleteCharAt(alterSql.length() - 1);
                    }
                } else if (constraint instanceof OracleForeignKey) {
                    OracleForeignKey foreignKey = (OracleForeignKey) constraint;
                    alterSql.append(" foreign key (");
                    for (SQLName item2 : foreignKey.getReferencingColumns()) {
                        alterSql.append("`").append(item2.getSimpleName().replaceAll("\"", "").toLowerCase()).append("`,");
                    }
                    alterSql.deleteCharAt(alterSql.length() - 1);
                    alterSql.append(") references ").append(foreignKey.getReferencedTable().toString().replaceAll("\"", "").toLowerCase()).append(" (");
                    for (SQLName item2 : foreignKey.getReferencedColumns()) {
                        alterSql.append("`").append(item2.getSimpleName().replaceAll("\"", "").toLowerCase()).append("`,");
                    }
                    alterSql.deleteCharAt(alterSql.length() - 1);
                } else if (constraint instanceof OracleUnique) {
                    OracleUnique unique = (OracleUnique) constraint;
                    SQLIndexDefinition indexDefinition = unique.getIndexDefinition();
                    if (indexDefinition != null && !indexDefinition.getColumns().isEmpty()) {
                        List<SQLSelectOrderByItem> items = indexDefinition.getColumns();
                        alterSql.append(" unique (");
                        for (int i = 0; i < items.size(); i++) {
                            SQLSelectOrderByItem item2 = items.get(i);
                            alterSql.append("`").append(item2.getExpr().toString().replaceAll("\"", "").toLowerCase().replace("\"", "")).append("`,");
                        }
                        alterSql.deleteCharAt(alterSql.length() - 1);
                    }
                }
            }
            for (SQLColumnDefinition scd : columns) {
                String fieldName = scd.getName().toString().replaceAll("\"", "").toLowerCase();
                alterSql.append("`").append(fieldName).append("` ");
                if (scd.getDataType() != null) {
                    SQLDataType mysqlDataType = SQLTransformUtils.transformOracleToMySql(scd.getDataType());
                    alterSql.append(mysqlDataType);
                }
                if (scd.getConstraints() != null) {
                    scd.getConstraints().forEach(a -> {
                        if (a instanceof SQLNotNullConstraint) {
                            alterSql.append(" not null");
                        }
                    });
                }
                SQLExpr defaultExpr = scd.getDefaultExpr();
                if (defaultExpr != null) {
                    if (defaultExpr instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) defaultExpr;
                        if (identifierExpr.nameHashCode64() == FnvHash.Constants.SYSTIMESTAMP) {
                            alterSql.append(" ").append("default").append(" ").append("current_timestamp");
                        }
                    } else {
                        alterSql.append(" ").append("default").append(" ").append(defaultExpr.toString().replaceAll("\"", "").toLowerCase());
                    }
                }
                if (map != null && map.containsKey(fieldName)) {
                    alterSql.append(" comment ").append(map.get(fieldName));
                }
            }
            if (isModify) {
                alterSql.append(";");
            } else {
                alterSql.append(");");
            }
        }
        alterSql.append("\n");
        return Pair.of(tableName, alterSql.toString());
    }

    private static Pair<String, String> createIndex(SQLStatement statement) {
        StringBuilder indexSb = new StringBuilder();
        OracleCreateIndexStatement indexStatement = (OracleCreateIndexStatement) statement;
        SQLIndexDefinition indexDefinition = indexStatement.getIndexDefinition();
        String tableName = indexDefinition.getTable().toString().toLowerCase().replaceAll("\"", "");
        String indexType = indexDefinition.getType();
        indexSb.append("create ");
        if (StringUtils.hasText(indexType)) {
            indexSb.append(indexType.replaceAll("\"", "").toLowerCase()).append(" ");
        }
        indexSb.append("index ");
        indexSb.append(indexDefinition.getName().toString().toLowerCase()).append(" on `").append(tableName).append("` (");
        for (SQLSelectOrderByItem item : indexDefinition.getColumns()) {
            indexSb.append("`").append(item.getExpr().toString().replaceAll("\"", "").toLowerCase()).append("`,");
        }
        indexSb.deleteCharAt(indexSb.length() - 1);
        indexSb.append(");\n");
        return Pair.of(tableName, indexSb.toString());
    }

    private static Map<String, HashMap<String, String>> getTableFiledCommentMap(List<SQLStatement> statementList) {
        Map<String, HashMap<String, String>> ret = new HashMap<>();
        for (SQLStatement statement : statementList) {
            if (statement instanceof SQLCommentStatement) {
                SQLCommentStatement commentStatement = (SQLCommentStatement) statement;
                SQLExprTableSource tableSource = commentStatement.getOn();
                if (tableSource != null && tableSource.getExpr() != null && tableSource.getExpr() instanceof SQLPropertyExpr) {
                    SQLPropertyExpr propertyExpr = (SQLPropertyExpr) tableSource.getExpr();
                    HashMap<String, String> fieldCommentMap = ret.computeIfAbsent(propertyExpr.getOwnerName().toLowerCase().replaceAll("\"", ""), key -> new HashMap<>());
                    fieldCommentMap.put(propertyExpr.getName().toLowerCase().replaceAll("\"", ""), commentStatement.getComment().toString());
                }
            }
        }
        return ret;
    }

    private static List<String> readFile(String path) {
        File file = new File(path);
        List<String> result = new ArrayList<>();
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
            String s = null;
            while ((s = br.readLine()) != null) {
                result.add(s);
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    private static void writeFile(String filePath, List<String> dataList) throws IOException {
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        //写入中文字符时解决中文乱码问题
        FileOutputStream fos = new FileOutputStream(file);
        OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
        BufferedWriter bw = new BufferedWriter(osw);

        for (String arr : dataList) {
            bw.write(arr + "\t\n");
        }
        //注意关闭的先后顺序，先打开的后关闭，后打开的先关闭
        bw.close();
        osw.close();
        fos.close();
    }

    static class TransformModel {
        protected String sql;
        protected LinkedHashMap<String, List<String>> createMap = new LinkedHashMap<>();
        protected LinkedHashMap<String, List<String>> insertMap = new LinkedHashMap<>();
        protected LinkedHashMap<String, List<String>> updateMap = new LinkedHashMap<>();
        protected LinkedHashMap<String, List<String>> deleteMap = new LinkedHashMap<>();
        protected LinkedHashMap<String, List<String>> alterMap = new LinkedHashMap<>();
        protected LinkedHashMap<String, List<String>> indexMap = new LinkedHashMap<>();
        protected LinkedHashSet<String> unknownSqls = new LinkedHashSet<>();
        protected LinkedHashSet<String> notSupportSqls = new LinkedHashSet<>();
    }

}