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小時我完成了作業，避免了繁瑣的畫圖和加班，哈哈！
?

最后祝大家1024快樂！