达梦数据库建表语句转mysql建表语句

新项目要复用一部分表结构，需要从达梦切换到mysql，没法直接传输。也没有搜到比较合适的方法，写了个简单的转化工具，将达梦数据库的建表语句转换为mysql的建表语句。

没有处理引擎、字符集等问题，有需求自己改代码喽，🙂

大致逻辑

第一步：解析达梦建表语句，获取表名、表注释、字段名、字段类型、长度、默认值、是否非空、索引、主键等内容。
第二步：将第一步获取的内容拼接为MySQL的建表语句。

代码

public static void main(String[] args) {
		//达梦数据库名
		String databaseName = "databaseName".toLowerCase();
		//达梦数据库的建表语句，可以从达梦数据库导出
		String fileName = "C:\\Users\\sdxms\\Desktop\\新建 文本文档.txt";
		List<List<String>> list = new ArrayList<>();
		try (BufferedReader reader = new BufferedReader(new FileReader(fileName))) {
			String line;
			List<String> tableList = new ArrayList<>();
			list.add(tableList);
			while ((line = reader.readLine()) != null) {
				String lowerCase = line.toLowerCase().replaceAll(" ", "");
				//判断建表语句的开始
				if(lowerCase.startsWith("createtable")){
					tableList = new ArrayList<>();
					list.add(tableList);
				}
				tableList.add(line);
				//System.out.println(line);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
  		//System.out.println("list = " + list);
		//循环表
		for (List<String> stringList : list) {
			if(stringList.size() == 0){
				continue;
			}
			if(!stringList.get(0).toLowerCase().startsWith("create")){
				continue;
			}
			String tableName = "";
			String primaryKey = "";
			String tableComment = "";
			List<Map<String,String>> columnList = new ArrayList<>();
			Map<String,String> commentMap = new HashMap<>();
			Map<String,String> indexMap = new HashMap<>();
			//循环表结构语句
			for (String s : stringList) {
				//单行语句按空格拆分
				ArrayList<String> singleStr = new ArrayList<>(Arrays.asList(s.toLowerCase().split(" +")));
				//小于等1 跳过
				if (singleStr.size() <= 1) {
					continue;
				}
				//读取表名
				if(singleStr.size() > 2 && singleStr.get(0).equals("create") && singleStr.get(1).equals("table")){
					tableName = singleStr.get(2).replaceAll("\"|\\.","").replaceAll(databaseName,"");
				}
				//读取字段
				if(singleStr.get(1).startsWith("varchar")){
					Map<String,String> column = getColumnMap(singleStr,"varchar");
					columnList.add(column);
				}
				if(singleStr.get(1).startsWith("int")){
					Map<String,String> column = getColumnMap(singleStr,"int");
					columnList.add(column);
				}
				if(singleStr.get(1).startsWith("long")){
					Map<String,String> column = getColumnMap(singleStr,"long");
					columnList.add(column);
				}
				if(singleStr.get(1).startsWith("text")){
					Map<String,String> column = getColumnMap(singleStr,"text");
					columnList.add(column);
				}
				if(singleStr.get(1).startsWith("timestamp")){
					Map<String,String> column = getColumnMap(singleStr,"datetime");
					columnList.add(column);
				}

				//读取主键
				if (singleStr.contains("primary")) {
					int i = singleStr.indexOf("primary");
					String[] split = singleStr.get(i + 1).split("\\(|\\)");
					primaryKey = split[1].replaceAll("\"","");
				}

				//读取表注释
				if(singleStr.contains("comment") && singleStr.contains("table")){
					int i = singleStr.indexOf("is");
					tableComment = singleStr.get(i + 1).replaceAll("'|;","");
				}

				//读取字段注释
				if(singleStr.contains("comment") && singleStr.contains("column")){
					int i = singleStr.indexOf("is");
					String columnName = singleStr.get(i - 1).replaceAll("\"", "").split("\\.")[2];
					List<String> stringList1 = singleStr.subList(i + 1 , singleStr.size());
					String comment = String.join(" ",stringList1).replaceAll("'|';", "");
					commentMap.put(columnName,comment);
				}

				//读取字索引
				if(singleStr.contains("create") && singleStr.contains("index")){
					int i = singleStr.indexOf("on");
					String indexName = singleStr.get(i - 1).replaceAll("\"", "");
					String columnName = singleStr.get(i + 1).replaceAll("\"", "").split("\\(")[1];
					indexMap.put(columnName,indexName);
				}
			}

			//合成为mysql建表语句
			StringBuilder sqlBuilder = new StringBuilder();
			//表名
			sqlBuilder.append("create table ").append(tableName).append("(\n");
			for (Map<String, String> map : columnList) {
				//字段名，数据类型
				sqlBuilder.append(map.get("name")).append(" ").append(map.get("type")).append(" ");
				//长度
				if(map.containsKey("length")){
					sqlBuilder.append("(").append(map.get("length")).append(") ");
				}
				//默认值
				if(map.containsKey("default")){
					sqlBuilder.append("default ").append(map.get("default")).append(" ");
				}
				//是否可以为null
				if(map.containsKey("notNull")){
					sqlBuilder.append("not null ");
				}
				//注释以及字段行的结尾
				if(commentMap.containsKey(map.get("name"))){
					sqlBuilder.append("comment '").append(commentMap.get(map.get("name"))).append("',\n");
				}else{
					sqlBuilder.append(",\n");
				}
			}
			//主键
			sqlBuilder.append("primary key (").append(primaryKey).append("),\n");
			//索引
			for (Map.Entry<String, String> entry : indexMap.entrySet()) {
				sqlBuilder.append("key ").append(entry.getValue()).append(" (").append(entry.getKey()).append(") using btree,\n");
			}
			//去掉最后一个逗号
			sqlBuilder.deleteCharAt(sqlBuilder.length() - 2);
			sqlBuilder.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ");
			if(StringUtils.isNotEmpty(tableComment)){
				sqlBuilder.append("comment = '").append(tableComment).append("';");
			}else{
				sqlBuilder.append(";");
			}
			System.out.println(sqlBuilder);
			System.out.println();
			System.out.println();
			System.out.println();
		}
	}

	private static Map<String, String> getColumnMap(ArrayList<String> singleStr, String typeName) {
		Map<String,String> column = new HashMap<>();
		String name = singleStr.get(0).replaceAll("\"", "");
		//获取长度
		if(singleStr.get(1).contains("(")){
			String[] split = singleStr.get(1).split("\\(|\\)");
			column.put("length",split[1]);
		}
		//有默认值
		if(singleStr.contains("default")){
			column.put("default",singleStr.get(3).substring(0,singleStr.get(3).length() - 1));
		}
		//不为空
		if(singleStr.contains("not")){
			column.put("notNull","not null");
		}
		column.put("name",name);
		column.put("type",typeName);
		return column;

	}