1、故事背景
最近在搞一个项目所以下班有点晚，坐在地铁上习惯性的拿出Ipad刷一会研究生课程。突然手机响了，一看原来是组织语音会议， 讨论客户需要系统的er图，有400多张要求晚上搞定明天发出去。由于我们没设置外键约束，因此用powerdesigner无法把表之间的关联关系展示出来。由于表数量多、时间紧急，大家讨论说晚上可能时间来不及，建议和用户沟通延后一下。
2、冷静分析
然后我沉默了一会，突然灵感闪现，说把所有的表结构sql给我，到家后看看（没有当场给出答复，毕竟谋定而后动！）
2.1、难点
- 时间紧急、工作量大；
- 画关联关系比较复杂，繁琐；
2.2、思维发散
我能找到一些规律吗？是否可以用程序实现？毕竟用计算机能干的工作，就别让人做。
分析sql提取特征
下面给出sql demo
create table `term_relationships` ( `object_id` bigint(20) unsigned not null default 0 comment '对应文章ID/链接ID', `term_taxonomy_id` bigint(20) unsigned not null default 0 comment '对应分类方法ID', `term_order` int(11) not null default 0 comment '排序', primary key (`object_id`, `term_taxonomy_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; alter table term_relationships comment '文章属性关系表'; /*==============================================================*/ /* Table: term_taxonomy */ /*==============================================================*/ create table `term_taxonomy` ( `term_taxonomy_id` bigint(20) unsigned not null auto_increment comment 'ID', `description` longtext comment '说明', `parent` bigint(20) unsigned not null default 0 comment '属父分类方法ID', `count` bigint(20) not null default 0 comment '文章数统计', `site_id` bigint(20) comment '站点id', primary key (`term_taxonomy_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; alter table term_taxonomy comment '栏目';
经过分析，大部分表外键的命名方式是：被引用表的表名+下划线+id
2.3、解决办法
既然有规律，而且领导意思是准确度可以没那么高，那我就用Python写一个文本解析工具，自动生成外键sql然后再逆向生成er图。
3、方案设计
3.1、数据结构设计
遍历所有行，把表名和键名存储在字典中，数据设计如下：
其中绿色的是key
3.2、遍历全部sql文本，把表名和主键放在dic里面
处理流程如下
3.3、再次遍历sql文本，生成外键sql.
处理流程如下
4、talk is cheap,show me the code
上代码
import math import re primayKeyTableMap = {} class Stack(object): def __init__(self): self.stack = [] def push(self, data): """ 进栈函数 """ self.stack.append(data) def pop(self): """ 出栈函数， """ return self.stack.pop() def gettop(self): """ 取栈顶 """ return self.stack[-1] def __len__(self): return len(self.stack) stack1 = Stack() list1 = [] def findTableAndPrimayKey(): p1 = re.compile(r'[`](.*?)[`]', re.S) with open('D:/1.sql','r', encoding='utf-8') as infile: for line in infile: if 'CREATE TABLE' in line : tableName = re.findall(p1, line)[0] if len(stack1) != 0 : tempTableKey = stack1.pop() tempTableName = stack1.pop() if len(tempTableKey) > 2: # print(tempTableKey) primayKeyTableMap[tempTableKey] = [tempTableName,tempTableKey] else: # print(tempTableName+'_'+tempTableKey) primayKeyTableMap[removePre(tempTableName)+'_'+tempTableKey] = [tempTableName,tempTableKey] stack1.push(tableName) if 'PRIMARY KEY' in line : keyName = re.findall(p1, line) stack1.push(keyName[0]) def addForeignKey(): tableName = '' keyName = '' p1 = re.compile(r'[`](.*?)[`]', re.S) with open('D:/1.sql','r', encoding='utf-8') as infile: for line in infile: if 'CREATE TABLE' in line : tableName = re.findall(p1, line)[0] # stack1.push(tableName) elif '_USER_ID' in line or '_user_id' in line: foreignCloumn = re.findall(p1, line) sql = 'alter table '+tableName+' add foreign key('+foreignCloumn[0]+') references Z_IS_USER(USER_ID) on delete cascade on update cascade; ' print(sql) else : foreignCloumn = re.findall(p1, line) if len(foreignCloumn) > 0 and foreignCloumn[0] in primayKeyTableMap and primayKeyTableMap[foreignCloumn[0]][0]!=tableName : sql = 'alter table '+tableName+' add foreign key('+foreignCloumn[0]+') references '+primayKeyTableMap[foreignCloumn[0]][0]+'('+primayKeyTableMap[foreignCloumn[0]][1]+') on delete cascade on update cascade; ' print(sql) def removePre(tableName): return tableName.replace("IS_", "").replace("is_", "").replace("P_", '').replace('QRTZ_','').replace('Z_IS_','').replace('MS_','').replace('acl_','') def main(): findTableAndPrimayKey() # print(primayKeyTableMap) addForeignKey() main()
5、总结
- 本文章利用了python的dic，对应数据结构是map，同时利用了stack存储临时值。
- 利用了正则来匹配文本
- 使用了python
6、题外话
敢于打破常规、敢于尝试。
利用程序，2小时我完成了工作，避免了繁琐的画图和加班，哈哈！
