1、故事背景

2、冷静分析

## 2.1、难点

• 时间紧急、工作量大；
• 画关联关系比较复杂，繁琐；

## 2.2、思维发散

• 我能找到一些规律吗？是否可以用程序实现？毕竟用计算机能干的工作，就别让人做。

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 '栏目';

3、方案设计

## 3.3、再次遍历sql文本，生成外键sql.

4、talk is cheap,show me the code

import mathimport reprimayKeyTableMap = {}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、题外话
• 敢于打破常规、敢于尝试。