当前位置：首页 > news >正文

Mysql 脚本转换为drawio ER 脚本

news 来源：原创 2024/9/21 12:31:13

Navicat 导出数据库脚本

在这里插入图片描述

通过代码转换脚本

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;/*** SQL 脚本转换为 drawio ER 脚本*/
public class SQLToMermaid {public static void main(String[] args) {String inputFilePath = "/Users/admin/Desktop/xxx.sql"; // 输入文件路径String outputFilePath = "/Users/admin/Desktop/xxx.txt"; // 输出文件路径System.out.println("Reading from: " + inputFilePath);System.out.println("Writing to: " + outputFilePath);try (BufferedReader br = new BufferedReader(new FileReader(inputFilePath));FileWriter fw = new FileWriter(outputFilePath)) {StringBuilder sqlContent = new StringBuilder();String line;// 读取整个文件内容while ((line = br.readLine()) != null) {sqlContent.append(line).append("\n");}String sql = sqlContent.toString();// 正则表达式Pattern tablePattern = Pattern.compile("CREATE TABLE `(\\w+)` \\((.*?)\\) ENGINE.*?COMMENT='(.*?)';",Pattern.CASE_INSENSITIVE | Pattern.DOTALL);// 匹配Matcher tableMatcher = tablePattern.matcher(sql);while (tableMatcher.find()) {String tableName = tableMatcher.group(1);String tableComment = tableMatcher.group(3);fw.write(tableName + "（" + tableComment + "）\n");String columnDefinitions = tableMatcher.group(2);String[] columns = columnDefinitions.split(",(?=\\s*`)");for (String column : columns) {Pattern columnPattern = Pattern.compile("`(\\w+)`\\s+\\w+.*?COMMENT\\s+'(.*?)'",Pattern.DOTALL);Matcher columnMatcher = columnPattern.matcher(column);if (columnMatcher.find()) {String columnName = columnMatcher.group(1);String columnComment = columnMatcher.group(2);fw.write("    " + columnName + ": " + columnComment + "\n");}}fw.write("\n"); // 表之间空一行}System.out.println("Output written to " + outputFilePath);} catch (IOException e) {e.printStackTrace();}}
}

得到的脚本：

custom（自定义表）id: 主键idname: 名称module: 引用类型1：报事类型模板2：报事详情ref_id: 关联表idtype: is_required: 是否必填subsidiary_rule_str: 附属规则json串is_del: 是否删除sort_code: 排序编码create_time: 创建时间creator_uid: 创建人idmodified_time: 修改时间modified_uid: 修改人ididentity（模板通知规则关联执行人）id: 主键ref_id: type区分关联键0工单类型id 1 SLA配置idtype: 关联类型 0工单类型执行1工单类型分派2SLA配置label_id: 类型标签type = 0: 0抄送；step: 步骤0非步骤 1,2,3,4,5升级步骤identity_type: 身份类型 0角色1人员identity_id: 身份id