簡(jiǎn)介致遠(yuǎn)OA V8.1 數(shù)據(jù)字典是面向企業(yè)信息化開發(fā)、運(yùn)維及二次開發(fā)人員整理的數(shù)據(jù)庫結(jié)構(gòu)參考文檔用于快速查閱系統(tǒng)底層表結(jié)構(gòu)與字段定義解決開發(fā)中字段含義不清、擴(kuò)展字段用途不明等問題。資源包共1個(gè)PDF文件約2.2MB內(nèi)容以表格形式系統(tǒng)羅列各數(shù)據(jù)表信息。以ADDRESSBOOK表為例文檔詳細(xì)標(biāo)注了ID、MEMBER_ID、CREATE_DATE、UPDATE_DATE等基礎(chǔ)字段的類型與是否必填并完整梳理了EXT_ATTR_1至EXT_ATTR_70等擴(kuò)展字段涵蓋文本、數(shù)字、日期、枚舉、選人、選部門、選崗位等多種業(yè)務(wù)類型同時(shí)包含ADDRESSBOOK_MEMBER等關(guān)聯(lián)表結(jié)構(gòu)。目前已有2606人學(xué)習(xí)下載適合需要理解致遠(yuǎn)OA數(shù)據(jù)庫設(shè)計(jì)、進(jìn)行數(shù)據(jù)對(duì)接或系統(tǒng)維護(hù)的開發(fā)者參考可幫助快速定位字段含義、理清表間關(guān)系為二次開發(fā)與數(shù)據(jù)操作提供可靠依據(jù)。1. 致遠(yuǎn)OA V8.1 數(shù)據(jù)字典從“表名都認(rèn)不全”到能自己寫查詢接手一套致遠(yuǎn)OA V8.1 的二次開發(fā)或數(shù)據(jù)對(duì)接任務(wù)時(shí)最先卡住人的往往不是業(yè)務(wù)邏輯而是數(shù)據(jù)庫里那幾百張表到底誰是誰。系統(tǒng)跑得好好的可一旦要寫報(bào)表、做數(shù)據(jù)同步、排查流程卡死面對(duì)formmain_0001、ctp_affair、hi_task這類表名沒有數(shù)據(jù)字典基本等于摸黑走路。致遠(yuǎn)OA V8.1 數(shù)據(jù)字典要解決的就是把“表名、字段、含義、關(guān)聯(lián)關(guān)系”這四件事固定下來讓開發(fā)、運(yùn)維、實(shí)施都能查到同一份口徑。它適合三類人要做數(shù)據(jù)抽取的集成開發(fā)、要定位流程問題的運(yùn)維、以及要基于 OA 數(shù)據(jù)做二次報(bào)表的實(shí)施。下面按“先建字典、再查字典、最后用字典”的順序講清楚。2. 先搞清楚致遠(yuǎn)OA V8.1 的庫結(jié)構(gòu)長(zhǎng)什么樣致遠(yuǎn)OA V8.1 的數(shù)據(jù)庫不是一張大寬表而是按“元數(shù)據(jù) 業(yè)務(wù)數(shù)據(jù) 流程數(shù)據(jù) 組織數(shù)據(jù)”分層的。很多人一上來就show tables看到幾百張表直接懵是因?yàn)闆]先分清哪些是系統(tǒng)表、哪些是動(dòng)態(tài)生成的表。理解這個(gè)分層是建數(shù)據(jù)字典的前提。2.1 四類核心表元數(shù)據(jù)、業(yè)務(wù)、流程、組織元數(shù)據(jù)表以ctp_開頭居多比如ctp_enum_item存枚舉項(xiàng)、ctp_affair存事項(xiàng)關(guān)聯(lián)。業(yè)務(wù)數(shù)據(jù)表最典型的是formmain_XXXX和formson_XXXX其中XXXX是表單模板編號(hào)主表和子表靠formmain_id關(guān)聯(lián)。流程數(shù)據(jù)表以hi_和wf_開頭hi_task是歷史任務(wù)、hi_process是流程實(shí)例排查流程卡住基本繞不開這兩張。組織數(shù)據(jù)表則是org_和v_開頭org_member、org_unit是人員和單位。提示formmain_XXXX里的XXXX不是固定值每建一張表單就會(huì)多一組表所以數(shù)據(jù)字典必須支持“動(dòng)態(tài)表”的登記不能只列靜態(tài)表。2.2 用 information_schema 把表清單拉出來建字典第一步不是手抄而是從數(shù)據(jù)庫自己的元數(shù)據(jù)里拉。下面這段 SQL 在 MySQL 環(huán)境下執(zhí)行能把當(dāng)前庫所有表和注釋導(dǎo)出來作為字典的初始底稿。-- 拉取表級(jí)清單表名、注釋、引擎、行數(shù)估算 SELECT TABLE_NAME AS 表名, TABLE_COMMENT AS 表注釋, ENGINE AS 引擎, TABLE_ROWS AS 估算行數(shù) FROM information_schema.TABLES WHERE TABLE_SCHEMA zt_oa -- 換成實(shí)際庫名 ORDER BY TABLE_NAME;邏輯說明information_schema.TABLES是 MySQL 自帶的元數(shù)據(jù)視圖不需要額外權(quán)限就能讀。TABLE_COMMENT就是建表時(shí)寫的注釋致遠(yuǎn)OA 的很多表注釋是中文的直接能用。TABLE_ROWS對(duì) InnoDB 是估算值只用來判斷表大小不能當(dāng)精確行數(shù)。參數(shù)上唯一要改的是TABLE_SCHEMA填你實(shí)際的庫名。2.3 字段級(jí)字典列注釋和類型一起導(dǎo)表級(jí)清單只是目錄真正查數(shù)據(jù)要靠字段級(jí)。下面這段把列名、類型、是否可空、注釋一起拉出來。-- 拉取字段級(jí)清單作為數(shù)據(jù)字典的字段頁 SELECT TABLE_NAME AS 表名, COLUMN_NAME AS 字段名, COLUMN_TYPE AS 字段類型, IS_NULLABLE AS 是否可空, COLUMN_COMMENT AS 字段注釋, COLUMN_KEY AS 鍵類型 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA zt_oa ORDER BY TABLE_NAME, ORDINAL_POSITION;邏輯說明ORDINAL_POSITION保證字段按建表順序排列讀起來和建表語句一致。COLUMN_KEY會(huì)標(biāo)出PRI主鍵、MUL普通索引建關(guān)聯(lián)關(guān)系時(shí)先看這個(gè)。COLUMN_COMMENT是字典的核心價(jià)值很多字段名如ext1、ext2只有靠注釋才能知道含義。如果注釋為空說明建表時(shí)沒寫需要結(jié)合業(yè)務(wù)反推后補(bǔ)進(jìn)字典。2.4 把動(dòng)態(tài)表單表單獨(dú)歸一類formmain_XXXX和formson_XXXX數(shù)量會(huì)隨表單增加而增長(zhǎng)建議在字典里單獨(dú)建一張“動(dòng)態(tài)表登記表”記錄模板編號(hào)、表名、對(duì)應(yīng)業(yè)務(wù)模塊。-- 找出所有動(dòng)態(tài)表單主表并提取模板編號(hào) SELECT TABLE_NAME AS 表名, SUBSTRING(TABLE_NAME, 10) AS 模板編號(hào) -- formmain_ 長(zhǎng)度為 9 FROM information_schema.TABLES WHERE TABLE_SCHEMA zt_oa AND TABLE_NAME LIKE formmain_%;邏輯說明SUBSTRING(TABLE_NAME, 10)從第 10 個(gè)字符開始截取正好跳過formmain_這 9 個(gè)字符拿到模板編號(hào)。拿到編號(hào)后再去業(yè)務(wù)配置里對(duì)照表單名稱就能把“模板編號(hào)—表名—業(yè)務(wù)含義”三者對(duì)上。這一步是致遠(yuǎn)OA 數(shù)據(jù)字典區(qū)別于普通字典的關(guān)鍵因?yàn)閯?dòng)態(tài)表不登記字典就是殘缺的。3. 把數(shù)據(jù)字典落成可查詢的本地庫拉出來的清單如果只存成 Excel查起來還是靠眼睛找。更實(shí)用的做法是在本地建一張字典表把表級(jí)和字段級(jí)信息灌進(jìn)去之后用 SQL 查字典比翻文檔快得多。3.1 建字典表兩張表搞定表級(jí)和字段級(jí)-- 字典-表級(jí) CREATE TABLE dict_table ( id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(128) NOT NULL, table_comment VARCHAR(512), table_type VARCHAR(32), -- static / dynamic_main / dynamic_son UNIQUE KEY uk_table (table_name) ); -- 字典-字段級(jí) CREATE TABLE dict_column ( id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(128) NOT NULL, column_name VARCHAR(128) NOT NULL, column_type VARCHAR(64), is_nullable VARCHAR(8), column_key VARCHAR(8), column_comment VARCHAR(512), KEY idx_table (table_name) );邏輯說明dict_table里加table_type是為了區(qū)分靜態(tài)表和動(dòng)態(tài)表單表查的時(shí)候可以按類型過濾。dict_column用table_name建普通索引因?yàn)樽畛Ｒ姷牟樵兪恰敖o我某張表的所有字段”。兩張表都不設(shè)外鍵字典庫追求的是導(dǎo)入方便不追求強(qiáng)約束。3.2 用 Python 把元數(shù)據(jù)灌進(jìn)字典表手工 INSERT 幾百張表不現(xiàn)實(shí)用腳本從information_schema讀出來再寫進(jìn)字典表最省事。import pymysql # 連接源庫致遠(yuǎn)OA 所在庫和字典庫 src pymysql.connect(host127.0.0.1, userreadonly, password***, databasezt_oa) dst pymysql.connect(host127.0.0.1, userroot, password***, databasedict_db) with src.cursor() as sc, dst.cursor() as dc: # 1. 導(dǎo)表級(jí) sc.execute( SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA zt_oa ) for name, comment in sc.fetchall(): # 判斷表類型動(dòng)態(tài)主表 / 動(dòng)態(tài)子表 / 靜態(tài)表 if name.startswith(formmain_): ttype dynamic_main elif name.startswith(formson_): ttype dynamic_son else: ttype static dc.execute( INSERT IGNORE INTO dict_table(table_name, table_comment, table_type) VALUES(%s,%s,%s), (name, comment, ttype) ) # 2. 導(dǎo)字段級(jí) sc.execute( SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA zt_oa ) for row in sc.fetchall(): dc.execute( INSERT INTO dict_column (table_name, column_name, column_type, is_nullable, column_key, column_comment) VALUES(%s,%s,%s,%s,%s,%s), row ) dst.commit() print(字典導(dǎo)入完成)邏輯說明腳本分兩段先導(dǎo)表級(jí)再導(dǎo)字段級(jí)。INSERT IGNORE保證重復(fù)執(zhí)行不會(huì)報(bào)主鍵沖突方便反復(fù)跑。table_type的判斷邏輯放在 Python 里而不是 SQL 里是因?yàn)橐?guī)則簡(jiǎn)單、改起來直觀。參數(shù)上要改的是兩個(gè)連接串源庫建議用只讀賬號(hào)避免誤操作。跑完后dict_table和dict_column就是你的本地字典。3.3 查字典的三個(gè)常用 SQL字典建好后日常最常用的查詢其實(shí)就三類。-- 場(chǎng)景一按業(yè)務(wù)關(guān)鍵詞找表 SELECT table_name, table_comment, table_type FROM dict_table WHERE table_comment LIKE %流程% OR table_comment LIKE %任務(wù)%; -- 場(chǎng)景二看某張表的所有字段 SELECT column_name, column_type, column_comment FROM dict_column WHERE table_name hi_task ORDER BY id; -- 場(chǎng)景三找所有含某個(gè)字段的表比如找 formmain_id 的關(guān)聯(lián)表 SELECT DISTINCT table_name FROM dict_column WHERE column_name formmain_id;邏輯說明場(chǎng)景一解決“不知道表名”的問題靠注釋反查。場(chǎng)景二解決“知道表名但不知道字段”的問題。場(chǎng)景三最實(shí)用做表關(guān)聯(lián)時(shí)先找哪些表有同一個(gè)字段能快速定位主外鍵關(guān)系。這三個(gè)查詢覆蓋了日常八成的字典使用場(chǎng)景。4. 用數(shù)據(jù)字典反查流程與表單的關(guān)聯(lián)關(guān)系字典不只是查字段更重要的是把表之間的關(guān)聯(lián)關(guān)系固化下來。致遠(yuǎn)OA V8.1 里流程和表單的關(guān)聯(lián)是最容易繞暈的部分靠字典能把它拆清楚。4.1 流程實(shí)例、任務(wù)、事項(xiàng)三張表的關(guān)聯(lián)流程排查的核心鏈路是hi_process流程實(shí)例→hi_task任務(wù)節(jié)點(diǎn)→ctp_affair事項(xiàng)。三張表通過process_id、activity_id、affair_id這類字段串起來。表名作用關(guān)鍵字段關(guān)聯(lián)方向hi_process流程實(shí)例主表id, process_template_id被 hi_task 引用hi_task任務(wù)節(jié)點(diǎn)歷史id, process_id, activity_id引用 hi_processctp_affair事項(xiàng)與表單關(guān)聯(lián)id, form_id, process_id關(guān)聯(lián)表單和流程邏輯說明排查“流程卡住”時(shí)先用hi_process找到實(shí)例再用process_id去hi_task查當(dāng)前節(jié)點(diǎn)最后用ctp_affair找到對(duì)應(yīng)表單數(shù)據(jù)。這張關(guān)聯(lián)表建議直接寫進(jìn)數(shù)據(jù)字典的“關(guān)系頁”比記在腦子里可靠。4.2 動(dòng)態(tài)表單主表和子表的關(guān)聯(lián)formmain_XXXX和formson_XXXX的關(guān)聯(lián)靠formmain_id子表里存的是主表記錄的 ID。-- 查某張主表及其子表的數(shù)據(jù) SELECT m.id, m.field1, s.field2 FROM formmain_0001 m LEFT JOIN formson_0001 s ON s.formmain_id m.id WHERE m.id 12345;邏輯說明formmain_id是子表指向主表的外鍵字段這個(gè)命名規(guī)則在致遠(yuǎn)OA 里是統(tǒng)一的。參數(shù)上要注意主表和子表的模板編號(hào)必須一致formmain_0001對(duì)應(yīng)formson_0001不能跨編號(hào)關(guān)聯(lián)。如果查不到子表數(shù)據(jù)先確認(rèn)子表是否存在、formmain_id是否有值。4.3 把關(guān)聯(lián)關(guān)系寫進(jìn)字典的“關(guān)系頁”建議在字典庫里再加一張dict_relation表記錄“主表—從表—關(guān)聯(lián)字段—關(guān)系類型”。CREATE TABLE dict_relation ( id INT AUTO_INCREMENT PRIMARY KEY, main_table VARCHAR(128), ref_table VARCHAR(128), ref_field VARCHAR(128), relation_type VARCHAR(16) -- one_to_many / one_to_one );邏輯說明這張表不追求自動(dòng)生成而是人工維護(hù)。因?yàn)殛P(guān)聯(lián)關(guān)系里有業(yè)務(wù)語義腳本猜不出來。維護(hù)方式很簡(jiǎn)單每搞清一組關(guān)聯(lián)就插一條日積月累就是一份可復(fù)用的關(guān)系圖譜。查詢時(shí)用main_table或ref_table都能命中。5. 避坑致遠(yuǎn)OA V8.1 數(shù)據(jù)字典落地時(shí)的五個(gè)常見問題這一章按“現(xiàn)象 → 原因 → 解決”寫都是實(shí)際建字典時(shí)容易翻車的地方。5.1 字段注釋為空字典查了等于沒查現(xiàn)象導(dǎo)出的字段清單里ext1、ext2、field3這類字段注釋是空的根本不知道存什么。原因建表時(shí)開發(fā)沒寫注釋或者動(dòng)態(tài)表單的擴(kuò)展字段本來就沒有固定含義。解決對(duì)空注釋字段結(jié)合業(yè)務(wù)數(shù)據(jù)反查比如SELECT DISTINCT ext1 FROM formmain_0001 LIMIT 20看實(shí)際值再人工補(bǔ)注釋到dict_column里。補(bǔ)注釋這件事沒有捷徑但補(bǔ)一次就能一直用。5.2 動(dòng)態(tài)表數(shù)量對(duì)不上字典漏表現(xiàn)象字典里formmain_開頭的表比實(shí)際業(yè)務(wù)里看到的表單少。原因動(dòng)態(tài)表是隨表單創(chuàng)建而生成的如果字典是某個(gè)時(shí)間點(diǎn)導(dǎo)的之后新建的表單不會(huì)自動(dòng)進(jìn)來。解決把字典導(dǎo)入腳本做成可重復(fù)執(zhí)行的定期跑一次INSERT IGNORE保證已有記錄不重復(fù)、新表能補(bǔ)進(jìn)來。建議在腳本里加一行日志打印本次新增了多少張表。5.3 用 TABLE_ROWS 判斷表大小結(jié)果被誤導(dǎo)現(xiàn)象字典里顯示某張表只有幾百行實(shí)際查出來幾百萬行。原因InnoDB 的TABLE_ROWS是采樣估算不是精確值尤其在大表上偏差很大。解決字典里保留TABLE_ROWS作為參考但不要用它做分頁或容量判斷。需要精確行數(shù)時(shí)用SELECT COUNT(*)或者查information_schema.TABLES的DATA_LENGTH看數(shù)據(jù)體積。5.4 關(guān)聯(lián)查詢沒走索引字典查得慢現(xiàn)象用dict_column按column_name查表時(shí)數(shù)據(jù)量一大就慢。原因dict_column只建了table_name索引按字段名查是全表掃描。解決如果經(jīng)常按字段名反查表給column_name也加索引。ALTER TABLE dict_column ADD INDEX idx_column (column_name);邏輯說明加索引后WHERE column_name formmain_id這類查詢能從全表掃描變成索引查找。代價(jià)是插入稍慢但字典庫寫入頻率低完全可接受。5.5 直接在生產(chǎn)庫上跑字典腳本影響業(yè)務(wù)現(xiàn)象導(dǎo)入腳本跑的時(shí)候生產(chǎn)庫出現(xiàn)慢查詢告警。原因information_schema查詢?cè)诒矶嗟臅r(shí)候本身有開銷加上腳本一次性拉全量可能和業(yè)務(wù)查詢搶資源。解決字典導(dǎo)入放在業(yè)務(wù)低峰期執(zhí)行或者從只讀從庫拉元數(shù)據(jù)。如果只能連生產(chǎn)庫把腳本拆成“先拉表級(jí)、再拉字段級(jí)”兩段中間加sleep降低瞬時(shí)壓力。6. 讓字典自己更新一個(gè)定時(shí)校驗(yàn)的小技巧字典建好只是開始真正省心的是讓它能自己發(fā)現(xiàn)“變了”。我一般會(huì)在字典庫里加一張dict_snapshot表每次導(dǎo)入前先存一份當(dāng)前的表名和字段名快照導(dǎo)入后做 diff把新增、刪除、改名的表列出來。這樣不用每次手動(dòng)比對(duì)跑完腳本看 diff 結(jié)果就知道結(jié)構(gòu)有沒有變。-- 快照表記錄每次導(dǎo)入時(shí)的表名和字段名 CREATE TABLE dict_snapshot ( id INT AUTO_INCREMENT PRIMARY KEY, snap_time DATETIME, table_name VARCHAR(128), column_name VARCHAR(128) ); -- 導(dǎo)入前先存快照 INSERT INTO dict_snapshot(snap_time, table_name, column_name) SELECT NOW(), table_name, column_name FROM dict_column; -- 導(dǎo)入后對(duì)比找出新增的字段 SELECT c.table_name, c.column_name FROM dict_column c LEFT JOIN dict_snapshot s ON c.table_name s.table_name AND c.column_name s.column_name WHERE s.id IS NULL;邏輯說明快照表存的是“上一次導(dǎo)入后”的狀態(tài)導(dǎo)入新數(shù)據(jù)后再查L(zhǎng)EFT JOIN ... IS NULL就能拿到本次新增的字段。同理把主從表反過來就能查刪除的字段。snap_time用來區(qū)分不同批次的快照如果快照表太大可以只保留最近三次。這個(gè)技巧的價(jià)值在于致遠(yuǎn)OA 升級(jí)或表單調(diào)整后結(jié)構(gòu)變化能第一時(shí)間被發(fā)現(xiàn)而不是等業(yè)務(wù)報(bào)錯(cuò)才回頭查。我自己的習(xí)慣是每周跑一次這個(gè)校驗(yàn)diff 結(jié)果為空就跳過有變化就人工確認(rèn)后補(bǔ)注釋。堅(jiān)持下來字典的準(zhǔn)確率能維持在九成以上剩下那一成是業(yè)務(wù)語義層面的本來也得靠人判斷。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取