处理sql为标准sql

将mysql数据库使用navcat导出后处理为标准的sql脚本

/**
 * 将navicat导出的mysql数据库脚本处理为标准脚本
 */
public class StandardSql {

    public static void make() {
        try {
            String str = FileUtils.readFileToString(new File(PathKit.getWebRootPath() + "/src/main/webapp/WEB-INF/dbsql/fsm5.sql"), "utf-8");
            str = str.replaceAll("`", "").replaceAll("([ \t]+)longtext([ \t,]*)", " clob $2").replaceAll("ENGINE=InnoDB DEFAULT CHARSET=utf8", "")
                    .replaceAll("DROP TABLE IF EXISTS ([^;]*);", "-- DROP TABLE $1").replaceAll("SET FOREIGN_KEY_CHECKS=0;", "").replaceAll("/\\*([^\\*]+)\\*/", "");
            //视图处理
            str=str.replaceAll("DROP VIEW IF EXISTS ([^;]*);", "-- DROP VIEW $1").replaceAll("ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER ", "");
            // 注释处理
            str = str.replaceAll("COMMENT='([^']+)';", ";\r\n -- $1").replaceAll("COMMENT '([^']+)',", ", -- $1");
            // 处理整形
            str = str.replaceAll(" int(\\(\\d+\\)) ", " int ");
            //替换\r\n\"
            str = str.replaceAll("\\x5c\\x72", "\"").replaceAll("\\x5c\\x6e", "").replaceAll("\\x5c\\x22", "\"");
            System.out.println(str);

            String mysql=str.replaceAll("([ \t]+)clob([ \t,]*)", " longtext $2");
            FileUtils.writeStringToFile(new File(PathKit.getWebRootPath() + "/src/main/webapp/WEB-INF/dbsql/mysql.sql"), mysql, "utf-8");

            String oracle=str.replaceAll("\\x5c\\x27", "\\'\\'");
            FileUtils.writeStringToFile(new File(PathKit.getWebRootPath() + "/src/main/webapp/WEB-INF/dbsql/oracle.sql"), oracle, "utf-8");

            String sqlserver=str.replaceAll("([ \t]+)clob([ \t,]*)", " text $2").replaceAll("\\x5c\\x27", "\\'\\'");
            FileUtils.writeStringToFile(new File(PathKit.getWebRootPath() + "/src/main/webapp/WEB-INF/dbsql/sqlserver.sql"), sqlserver, "utf-8");

            String dm=str.replaceAll("\\x5c\\x27", "\\'\\'");
            FileUtils.writeStringToFile(new File(PathKit.getWebRootPath() + "/src/main/webapp/WEB-INF/dbsql/dm.sql"), dm, "utf-8");


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        make();
    }
}