【MySQL建表语句转PostgreSQL建表语句】MySQL建表语句转PostgreSQL建表语句

你笨算我输

已于 2022-03-10 20:21:33 修改

阅读量1.9k

点赞数

文章标签： postgresql mysql 数据库

于 2022-03-10 20:17:59 首次发布

本文链接：https://blog.csdn.net/weixin_42979574/article/details/123410213

版权

该博客介绍了一个用于将MySQL的DDL语句转换为PostgreSQL兼容格式的Java工具。通过读取MySQL的DDL文件，应用正则表达式替换特定的数据类型和约束，以适应PostgreSQL的要求。同时，它还处理了注释的处理，确保转换后的SQL语句正确无误。

摘要由CSDN通过智能技术生成

1.首先加一下依赖
<dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>1.2</version> </dependency>
2.加下脚本
/**

@author Ben.Zhou
@version 1.0
@description

@data 2022/3/10 16:51
*/
public class MysqlDdl2PgDdlUtil {
public static void main(String[] args) throws IOException, JSQLParserException {
// 你的MySQL DDL路径
String mysqlDDLPath = “C:\Users\wb_xg20\Desktop\finance_super1.sql”;
String dDLs = FileUtils.readFileToString(new File(mysqlDDLPath));

 System.out.println(dDLs);
 dDLs = dDLs.replaceAll("current_timestamp\\(\\) ON UPDATE current_timestamp\\(\\)", "current_timestamp")
         .replaceAll("current_timestamp\\(\\)", "current_timestamp")
         .replaceAll("current_timestamp\\(\\)", "current_timestamp")
         .replaceAll("int\\([0-9]*\\)", "INT")
         .replaceAll("tinyint\\([0-9]*\\)", "INT")
         .replaceAll("tinyINT","INT")
         .replaceAll("double\\([0-9]*,[0-9]*\\)","float8")
         .replaceAll("DEFAULT 00000000000","DEFAULT 0")
         .replaceAll("unsigned zerofill","")
         .replaceAll("bigint\\([0-9]*\\) NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
         .replaceAll("bigint\\([0-9]*\\)","INT8");
 //dDLs = dDLs.replaceAll("bigint\\(19\\)","int8");
 System.out.println("++++++++++开始转换SQL语句+++++++++++++");

 Statements statements = CCJSqlParserUtil.parseStatements(dDLs);
 statements.getStatements()
         .stream()
         .map(statement -> (CreateTable) statement).forEach(ct -> {
     Table table = ct.getTable();
     List<ColumnDefinition> columnDefinitions = ct.getColumnDefinitions();
     List<String> comments = new ArrayList<>();
     List<ColumnDefinition> collect = columnDefinitions.stream()
             .peek(columnDefinition -> {
                 List<String> columnSpecStrings = columnDefinition.getColumnSpecStrings();

                 int commentIndex = getCommentIndex(columnSpecStrings);

                 if (commentIndex != -1) {
                     int commentStringIndex = commentIndex + 1;
                     String commentString = columnSpecStrings.get(commentStringIndex);

                     String commentSql = genCommentSql(table.toString(), columnDefinition.getColumnName(), commentString);
                     comments.add(commentSql);
                     columnSpecStrings.remove(commentStringIndex);
                     columnSpecStrings.remove(commentIndex);
                 }
                 columnDefinition.setColumnSpecStrings(columnSpecStrings);
             }).collect(Collectors.toList());
     ct.setColumnDefinitions(collect);