package com.ruoyi.web.test;
|
|
import java.io.*;
|
import java.nio.charset.StandardCharsets;
|
import java.nio.file.*;
|
import java.util.*;
|
import java.util.regex.*;
|
|
public class SqlCleanerWithComment {
|
public static void main(String[] args) {
|
String inputFilePath = "C:\\Users\\86176\\Desktop\\smartor_lisui_jg.sql"; // 原始 MySQL 建表文件
|
String outputFilePath = "C:\\Users\\86176\\Desktop\\smartor_lisui_jg_gs.sql"; // 输出 openGauss 兼容建表文件
|
|
try {
|
// 读取 SQL 文件内容（兼容 JDK 8）
|
String originalSql = new String(Files.readAllBytes(Paths.get(inputFilePath)), StandardCharsets.UTF_8);
|
|
// 获取表名
|
String tableName = getTableName(originalSql);
|
|
// 提取 COMMENT 字段注释
|
Map<String, String> fieldComments = extractFieldComments(originalSql);
|
|
// 提取 AUTO_INCREMENT = <数字> 的值
|
Pattern autoIncrementPattern = Pattern.compile("AUTO_INCREMENT = (\\d+)");
|
Matcher matcher = autoIncrementPattern.matcher(originalSql);
|
String autoIncrementValue = null;
|
if (matcher.find()) {
|
autoIncrementValue = matcher.group(1); // 获取 AUTO_INCREMENT 后的数字
|
}
|
|
// 清理 MySQL 语法特性：字段尾部属性
|
String cleanedSql = originalSql
|
.replaceAll("(CHARACTER SET\\s+\\w+|COLLATE\\s+\\w+|DEFAULT\\s+[^,\\n]+|COMMENT\\s+'.*?')", "")
|
.replaceAll("\\s+NULL(?!\\s|,)", "")
|
.replaceAll("\\s{2,}", " ")
|
.replaceAll(",\\s*\\)", "\n)")
|
.replaceAll("(?i)ENGINE\\s*=\\s*\\w+", "")
|
.replaceAll("(?i)CHARACTER SET\\s*=\\s*\\w+", "")
|
.replaceAll("(?i)COLLATE\\s*=\\s*\\w+", "")
|
.replaceAll("(?i)COMMENT\\s*=\\s*'.*?'", "")
|
.replaceAll("(?i)ROW_FORMAT\\s*=\\s*\\w+", "")
|
.replaceAll("(?i)\\bdatetime\\b", "timestamp")
|
.replaceAll("(?i)\\btinyint\\b", "smallint") // 替换 tinyint
|
.replaceAll("AUTO_INCREMENT = \\d+", "")
|
.replaceAll("USING BTREE", "")
|
.replaceAll("longtext", "text")
|
.replaceAll("(?i)(CREATE TABLE)\\s+`([a-zA-Z_][a-zA-Z0-9_]*)`", "$1 \"public\".\"$2\"") // 只在表名上加 "public"
|
.replaceAll("`", "\"")
|
.trim();
|
|
// 构造 COMMENT ON COLUMN 语句
|
StringBuilder commentSql = new StringBuilder();
|
for (Map.Entry<String, String> entry : fieldComments.entrySet()) {
|
commentSql.append(String.format(
|
"COMMENT ON COLUMN \"%s\".\"%s\" IS '%s';\n",
|
tableName, entry.getKey(), entry.getValue()
|
));
|
}
|
|
// 替换 SERIAL 的自增字段，确保 openGauss 的兼容性
|
cleanedSql = cleanedSql.replaceAll("int NOT NULL AUTO_INCREMENT", "SERIAL NOT NULL");
|
|
// 如果找到了 AUTO_INCREMENT 值，添加 ALTER SEQUENCE 来设置自增起始值
|
if (autoIncrementValue != null) {
|
cleanedSql += "\n\n-- Adjust the sequence for 'id' field starting value\n";
|
cleanedSql += "ALTER SEQUENCE ivr_liba_template_id_seq RESTART WITH " + autoIncrementValue + ";";
|
}
|
|
// 提取并移除 INDEX 子句（包括 ASC/DESC/USING BTREE 等）
|
Pattern indexPattern = Pattern.compile("INDEX\\s+\"(\\w+)\"\\s*\\(\\s*\"(\\w+)\"(?:\\s+(ASC|DESC))?\\s*\\)", Pattern.CASE_INSENSITIVE);
|
Matcher indexMatcher = indexPattern.matcher(originalSql);
|
StringBuilder indexStatements = new StringBuilder();
|
|
while (indexMatcher.find()) {
|
String indexName = indexMatcher.group(1);
|
String columnName = indexMatcher.group(2);
|
// 移除 ASC/DESC，直接使用列名创建索引
|
indexStatements.append("CREATE INDEX ")
|
.append(indexName)
|
.append("_idx ON \"public\".\"")
|
.append(tableName)
|
.append("\" (\"")
|
.append(columnName)
|
.append("\");\n");
|
}
|
|
// 合并建表语句 + 注释语句 + 索引语句
|
String finalOutput = cleanedSql + "\n\n" + commentSql.toString() + indexStatements.toString();
|
|
// 写入输出文件
|
Files.write(Paths.get(outputFilePath), finalOutput.getBytes(StandardCharsets.UTF_8));
|
System.out.println("✅ 转换完成！输出文件为：" + outputFilePath);
|
} catch (IOException e) {
|
System.err.println("❌ 文件读取/写入失败: " + e.getMessage());
|
}
|
}
|
|
// 提取表名
|
private static String getTableName(String sql) {
|
Pattern pattern = Pattern.compile("CREATE TABLE\\s+\"[^\"]+\"\\.\"([^\"]+)\"", Pattern.CASE_INSENSITIVE);
|
Matcher matcher = pattern.matcher(sql);
|
return matcher.find() ? matcher.group(1) : "unknown_table";
|
}
|
|
// 提取字段注释
|
private static Map<String, String> extractFieldComments(String sql) {
|
Map<String, String> comments = new LinkedHashMap<>();
|
Pattern pattern = Pattern.compile("\"(\\w+)\"\\s+\\w+(\\([^)]*\\))?[^\\n]*?COMMENT\\s+'([^']+)'", Pattern.CASE_INSENSITIVE);
|
Matcher matcher = pattern.matcher(sql);
|
while (matcher.find()) {
|
comments.put(matcher.group(1), matcher.group(3));
|
}
|
return comments;
|
}
|
}
|
