个人习惯用MySQL workbench EER数据建模,然后生成SQL语句到数据库中执行,这样表之间的关系比较直观。
像下面这样:
画图
正向工程,生成DDL语句:
忽略生成外键,以及外键索引啥的:
生成的DDL语句:
到数据库执行。
踩坑了
最近团队微调,我被调整到另一个小团队。前两天接了个新需求,于是我依然使用MySQL workbench EER建模,结果好不容易建模完成了,却被告知这个项目用的数据库是PostgreSQL!
于是就面临如下几种选择:
重新找个支持导出PostgreSQL DDL语句的建模软件,再弄一遍。据我所知,macOS平台里没啥好的数据建模软件…
PowerDesigner用不了(除非装虚拟机,或者Wine);
Navicat太难用了(居然有人说Navicat是最好的数据库客户端,我只能给一个大写的服,在我看来,这货连IDEA自带数据库管理都比不上……这观点可能有点偏激,但现状是我做个查询,Navicat把查询按钮藏得很深);
IDEA宣布会开发类似功能,但一直没有动静;
开源的PDMan,体验挺不错,但也得连个数据库控制版本。
依然用MySQL workbench导出DDL,然后自己将MySQL DDL转换成PostgreSQL DDL。
我选择了自己转换SQL语句。
开源的DDL转换工具
既然要转换SQL语句,我心想,业界肯定有相关的工具啊。于是上万能的GayHub搜了下,还真有,列出来:
mysql-to-postgres:https://github.com/maxlapshin/mysql2postgres
mysql-postgresql-converter:https://github.com/lanyrd/mysql-postgresql-converter
多款工具配合使用:https://yq.aliyun.com/articles/241 (不得不佩服这兄弟真有耐心啊!)
然而试用后,内心是崩溃的……生成出来的DDL要么有误,要么没有注释。
自己开发工具
考虑到我的诉求其实非常简单,只是个DDL语句转换而已,自己开发一个也不难。而且之前研读Mybatis通用Mapper源码时,知道Java世界里有个 jsqlparser 的工具。
花了10分钟简单了解了下 jsqlparser 后,就开撸开发工具了……花了20分钟,初版写完了,然后和该项目的同事又花了20分钟验证了下,最终确定了如下的版本。代码贴出来:
加依赖:
com.github.jsqlparser
jsqlparser
1.2
写代码:
publicclassMysqlDdl2PgDdlUtil{
publicstaticvoidmain(String[]args)throwsIOException,JSQLParserException{
// 你的MySQL DDL路径
StringmysqlDDLPath="/Users/reno/Downloads/mysql.sql";
StringdDLs=FileUtils.readFileToString(newFile(mysqlDDLPath));
System.out.println(dDLs);
System.out.println("++++++++++开始转换SQL语句+++++++++++++");
Statementsstatements=CCJSqlParserUtil.parseStatements(dDLs);
statements.getStatements()
.stream()
.map(statement->(CreateTable)statement).forEach(ct->{
Tabletable=ct.getTable();
ListcolumnDefinitions=ct.getColumnDefinitions();
Listcomments=newArrayList<>();
Listcollect=columnDefinitions.stream()
.peek(columnDefinition->{
ListcolumnSpecStrings=columnDefinition.getColumnSpecStrings();
intcommentIndex=getCommentIndex(columnSpecStrings);
if(commentIndex!=-1){
intcommentStringIndex=commentIndex+1;
StringcommentString=columnSpecStrings.get(commentStringIndex);
StringcommentSql=genCommentSql(table.toString(),columnDefinition.getColumnName(),commentString);
comments.add(commentSql);
columnSpecStrings.remove(commentStringIndex);
columnSpecStrings.remove(commentIndex);
}
columnDefinition.setColumnSpecStrings(columnSpecStrings);
}).collect(Collectors.toList());
ct.setColumnDefinitions(collect);
StringcreateSQL=ct.toString()
.replaceAll("`","\"")
.replaceAll("BIGINT UNIQUE NOT NULL AUTO_INCREMENT","BIGSERIAL PRIMARY KEY")
.replaceAll("BIGINT NULL AUTO_INCREMENT","BIGSERIAL PRIMARY KEY")
.replaceAll("BIGINT NOT NULL AUTO_INCREMENT","BIGSERIAL PRIMARY KEY")
.replaceAll("INT NOT NULL AUTO_INCREMENT","BIGSERIAL PRIMARY KEY")
.replaceAll("INT NULL AUTO_INCREMENT","BIGSERIAL PRIMARY KEY")
.replaceAll("IF NOT EXISTS","")
.replaceAll("TINYINT","SMALLINT")
.replaceAll("DATETIME","TIMESTAMP")
.replaceAll(", PRIMARY KEY \\(\"id\"\\)","");
// 如果存在表注释
if(createSQL.contains("COMMENT")){
createSQL=createSQL.substring(0,createSQL.indexOf("COMMENT"));
}
System.out.println(createSQL+";");
comments.forEach(t->System.out.println(t.replaceAll("`","\"")+";"));
});
}
/**
* 获得注释的下标
*
* @param columnSpecStrings columnSpecStrings
* @return 下标
*/
privatestaticintgetCommentIndex(ListcolumnSpecStrings){
for(inti=0;i
if("COMMENT".equalsIgnoreCase(columnSpecStrings.get(i))){
returni;
}
}
return-1;
}
/**
* 生成COMMENT语句
*
* @param table 表名
* @param column 字段名
* @param commentValue 描述文字
* @return COMMENT语句
*/
privatestaticStringgenCommentSql(Stringtable,Stringcolumn,StringcommentValue){
returnString.format("COMMENT ON COLUMN %s.%s IS %s",table,column,commentValue);
}
}
如代码所示,目前是借助 jsqlparser 的SQL解析能力配合字符串替换的方式生成PostgreSQL的。
效果演示
转换前的DDL:
-------------------------------------------------------
--Table`user`
-------------------------------------------------------
CREATE TABLE IF NOT EXISTS`user`(
`id`INT NOT NULL AUTO_INCREMENT COMMENT'id',
`username`VARCHAR(16)NOT NULL COMMENT'用户名',
`email`VARCHAR(255)NULL COMMENT'邮件',
`password`VARCHAR(32)NOT NULL COMMENT'密码',
`create_time`TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT'创建时间',
PRIMARY KEY(`id`));
-------------------------------------------------------
--Table`movie`
-------------------------------------------------------
CREATE TABLE IF NOT EXISTS`movie`(
`id`INT NOT NULL AUTO_INCREMENT COMMENT'Id',
`name`VARCHAR(255)NOT NULL COMMENT'名称',
`user_id`INT NOT NULL COMMENT'user.id',
PRIMARY KEY(`id`))
COMMENT='电影表';
转换后的DDL:
CREATE TABLE"user"
(
"id"BIGSERIAL PRIMARY KEY,
"username"VARCHAR(16)NOT NULL,
"email"VARCHAR(255)NULL,
"password"VARCHAR(32)NOT NULL,
"create_time"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON COLUMN"user"."id"IS'id';
COMMENT ON COLUMN"user"."username"IS'用户名';
COMMENT ON COLUMN"user"."email"IS'邮件';
COMMENT ON COLUMN"user"."password"IS'密码';
COMMENT ON COLUMN"user"."create_time"IS'创建时间';
CREATE TABLE"movie"
(
"id"BIGSERIAL PRIMARY KEY,
"name"VARCHAR(255)NOT NULL,
"user_id"INT NOT NULL
);
COMMENT ON COLUMN"movie"."id"IS'Id';
COMMENT ON COLUMN"movie"."name"IS'名称';
COMMENT ON COLUMN"movie"."user_id"IS'user.id';
效果还是不错的,基本达到了我的要求。
不足
目前工具代码比较屎,如果想要改进,应该是要让工具理解MySQL DDL的词法,然后构建成例如Table、Column、Comment、Constraint、Index等对象例如:
classTable{
privateStringname;
privateColumncolumn;
}
classColumn{
privateStringname;
privateStringtype;
// 约束,例如非空等
privateSetconstraints;
// 索引
privateIndexindex;
}
classIndex{
privateStringname;
privateStringtype;
}
enumConstraint{
NOT_NULL,...;
}
然后抽象一个方言枚举,并为不同的方言制作一个DDL Generator Handler,然后根据不同的方言生成不同数据库平台的DDL语句。
为什么不改进?因为没有时间,工具是为工作服务的,目前能达到我的目的,就没动力修改了,未来有需求再改进吧。