前言
DBML(數據庫標記語言)是一種簡單易讀的 DSL 語言,用于定義數據庫結構。
因為需要分析商品模塊的表設計是否合理,所以需要圖形化表,并顯示表之前的關系。
想來想去,找到了DBML。所以就需要將數據庫結構,導出成DBML格式。
方法
用的laravel框架。
有兩種方法
- 使用
desc tableName
輸入如下:
array:13 [0 => array:6 ["Field" => "id""Type" => "bigint(20)""Null" => "NO""Key" => "PRI""Default" => null"Extra" => "auto_increment"]1 => array:6 ["Field" => "parent_id""Type" => "bigint(20)""Null" => "YES""Key" => "MUL""Default" => "0""Extra" => ""]
- 使用
show create table tableName
輸出如下:
CREATE TABLE `category` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分類ID',`parent_id` bigint(20) DEFAULT '0' COMMENT '父分類ID(0表示頂級分類)',`category_name` varchar(255) NOT NULL COMMENT '分類名稱',`category_alias` varchar(255) NOT NULL DEFAULT '' COMMENT '分類別名',`category_code` varchar(50) DEFAULT NULL COMMENT '分類編碼',`category_sort` int(11) NOT NULL DEFAULT '1' COMMENT '分類排序值',`level` int(11) NOT NULL DEFAULT '1' COMMENT '分類層級(1=一級分類)',`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '狀態(1=啟用,0=停用)',`user_created` varchar(255) NOT NULL DEFAULT '' COMMENT '創建人',`user_cid` int(11) NOT NULL DEFAULT '0' COMMENT '創建人id',`user_updated` varchar(255) NOT NULL DEFAULT '' COMMENT '更新人',`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',PRIMARY KEY (`id`) USING BTREE,KEY `idx_parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=777 DEFAULT CHARSET=utf8mb4 COMMENT='分類表'
因此就需要結合這兩種命令得到DBML格式所需要的數據。,腳本如下。
<?phpnamespace App\Http\Controllers;use Illuminate\Support\Facades\DB;class TestController extends Controller
{public function dbtableToDbml(){$tables = ['category','category_attribute','attribute','attribute_option','product','product_attribute','product_attribute_option','process','process_resource','combo','combo_item','combo_item_attribute','customer_subtotal','customer_product','customer_combo','customer_combo_item','customer_combo_attribute','customer_combo_material','customer_combo_material_attribute','customer_product_process','customer_product_process_resource',];$res = $this->dbtableStruToDbml($tables);return $res;}//數據庫表結構轉為DBML格式public function dbtableStruToDbml($tables){$tableArr = is_string($tables) ? explode(',', $tables) : $tables;$res = '';//獲取字段名及備注$extractFieldAndComment = function ($str) {$result = ['field' => null,'comment' => null];// 1. 提取字段名(匹配反引號包裹的內容)if (preg_match('/`([^`]+)`/', $str, $fieldMatches)) {$result['field'] = trim($fieldMatches[1]);}// 2. 提取COMMENT后的內容(如果存在)if (preg_match('/COMMENT\s+\'([^\']*)\'/i', $str, $commentMatches)) {$result['comment'] = trim($commentMatches[1]);}return $result;};$getTableComment = function ($str) {// 正則表達式解析:// COMMENT\s*=\s* 匹配"COMMENT"及可能的空格、等號、空格(不區分大小寫)// '([^']*)' 匹配單引號內的內容(捕獲組1),[^']*表示非單引號的任意字符(包括空)$pattern = '/COMMENT\s*=\s*\'([^\']*)\'/i';// 執行匹配if (preg_match($pattern, $str, $matches)) {// 匹配成功,返回捕獲到的內容(trim處理避免意外空格,保留空內容)return trim($matches[1]);}// 無COMMENT時返回空字符串return '';};foreach ($tableArr as $table) {$tableComment = '';$tableDesc = DB::select("desc {$table}");$filedArr = [];foreach ($tableDesc as $item) {$filedArr[$item->Field] = ['field' => $item->Field,'type' => $item->Type,'comment' => ''];}$createTable = DB::select("show create table {$table}");$createTable = json_decode(json_encode($createTable), true);$createTable = $createTable[0]['Create Table'];$fieldInfos = explode("\n", $createTable);foreach ($fieldInfos as $fieldInfo) {$fieldInfo = trim($fieldInfo, ' '); //去掉前面的空格$pattern = '/^`/'; //是否以 ` 號開頭,是的話,才是字段,否則就是其他的,其他的不考慮if (preg_match($pattern, $fieldInfo)) {//是字段才處理$fieldAndComment = $extractFieldAndComment($fieldInfo);$filedArr[$fieldAndComment['field']]['comment'] = $fieldAndComment['comment'];} elseif (preg_match('/^\)/', $fieldInfo)) {$tableComment = $getTableComment($fieldInfo);}}$str = '';$str = "Table {$table} {\n";foreach ($filedArr as $fieldItem) {$str .= " {$fieldItem['field']} {$fieldItem['type']} [note: '{$fieldItem['comment']}'] \n";}$str .= " Note: '{$tableComment}'\n";$str .= "}\n";strlen($res) ? $res = $res . "\n\n\n" : '';$res .= $str;}return $res;}}
導出的內容如下:
Table category {id bigint(20) [note: '分類ID'] parent_id bigint(20) [note: '父分類ID(0表示頂級分類)'] category_name varchar(255) [note: '分類名稱'] category_alias varchar(255) [note: '分類別名'] category_code varchar(50) [note: '分類編碼'] category_sort int(11) [note: '分類排序值'] level int(11) [note: '分類層級(1=一級分類)'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_cid int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] Note: '分類表'
}Table category_attribute {id bigint(20) [note: '記錄ID'] category_id bigint(20) [note: '分類ID'] attribute_id bigint(20) [note: '屬性ID'] limit_ids text [note: '限定屬性值id'] Note: '分類屬性關聯表'
}Table attribute {id bigint(20) [note: '屬性ID'] attribute_name varchar(255) [note: '屬性名稱(如顏色,尺寸)'] attribute_code varchar(50) [note: '屬性編碼(程序識別唯一值)'] sort mediumint(9) [note: '排序'] remark varchar(255) [note: '屬性說明'] input_type varchar(50) [note: '輸入類型(single=單選,double=多選)'] unit varchar(50) [note: '屬性單位'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_id int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] Note: '屬性定義表'
}Table attribute_option {id bigint(20) [note: '選項ID'] attribute_id bigint(20) [note: '所屬屬性ID'] option_value varchar(255) [note: '屬性選項值'] option_code varchar(50) [note: '屬性選項編碼'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] sort_order int(11) [note: '排序值(用于前端展示順序)'] remark varchar(255) [note: '備注'] Note: '屬性選項表(全局)'
}Table product {id bigint(20) [note: '商品ID(主鍵,自增)'] category_id bigint(20) [note: '分類ID(外鍵)'] category_top_ids varchar(255) [note: '上級分類id'] category_top_name varchar(255) [note: '上級分類名稱'] product_code varchar(50) [note: '商品編碼(內部唯一)'] product_name varchar(255) [note: ''] product_image varchar(255) [note: '商品圖片'] product_type int(11) [note: '物料類型:100-原料 200-輔料 201-紙箱 202-套袋'] short_name varchar(255) [note: '商品簡稱'] scientific_name varchar(255) [note: '商品學名'] label_alias varchar(255) [note: '標簽別名'] description text [note: '商品描述'] unit_name varchar(50) [note: '基礎單位名稱'] inspection_standard text [note: '質檢標準'] shelf_life int(11) [note: '保質期,單位:天'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_id int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] safe_stock int(11) [note: '安全庫存值'] Note: '商品主數據表'
}Table product_attribute {id bigint(20) [note: '記錄ID'] product_id bigint(20) [note: '商品ID'] attribute_id bigint(20) [note: '屬性ID'] is_must tinyint(4) [note: '是否必填:0-否 1-是'] Note: '商品屬性值表'
}Table product_attribute_option {id bigint(20) [note: '記錄ID'] product_id bigint(20) [note: '商品ID'] attribute_id bigint(20) [note: '屬性ID'] option_id bigint(20) [note: '屬性值ID'] Note: '商品屬性可選值關聯表'
}Table process {id bigint(20) [note: '工藝ID'] process_code varchar(50) [note: '工藝編碼'] process_name varchar(255) [note: '工藝名稱'] process_type varchar(255) [note: '工藝類型'] process_duration int(11) [note: '工藝工期'] duration varchar(255) [note: '時間單位'] description text [note: '工藝描述'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_id int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] Note: '組合商品工藝表'
}Table process_resource {process_id bigint(20) [note: '工藝ID'] product_id bigint(20) [note: '物料ID'] Note: '工藝物料關聯表'
}Table combo {id bigint(20) [note: '組合ID'] category_id bigint(20) [note: '分類ID(外鍵)'] category_top_ids varchar(255) [note: '上級分類id'] category_top_name varchar(255) [note: '上級分類名稱'] combo_name varchar(255) [note: '組合名稱'] combo_code varchar(255) [note: '組合編碼'] combo_image text [note: '組合圖片'] combo_price decimal(10,2) [note: '組合價格'] combo_label varchar(255) [note: '組合成品標簽'] subtotal_name varchar(255) [note: '小計名稱'] unit_name varchar(50) [note: '基礎單位名稱'] unit_spec decimal(10,2) [note: '基礎單位規格'] unit_pack_name varchar(50) [note: '包裝單位名稱'] unit_pack_spec decimal(10,2) [note: '包裝單位規格'] description text [note: '組合描述'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_id int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] Note: '組合產成品主表'
}Table combo_item {id bigint(20) [note: '組合項ID'] combo_id bigint(20) [note: '組合ID'] product_id bigint(20) [note: '所屬組合的商品ID'] category_id bigint(20) [note: '所屬組合的分類ID'] category_name varchar(255) [note: '分類名稱'] item_name varchar(255) [note: '組合名稱'] item_type varchar(50) [note: '組合項類型(LABEL/PRODUCT)'] quantity int(11) [note: '數量'] label_name varchar(50) [note: '標簽名稱'] remark text [note: '備注'] Note: '組合項表'
}Table combo_item_attribute {id bigint(20) [note: '記錄ID'] item_id bigint(20) [note: '組合項ID'] attribute_id bigint(20) [note: '屬性ID'] option_id bigint(20) [note: '屬性值ID'] Note: '組合項屬性值表'
}Table customer_subtotal {id bigint(20) [note: '小計ID'] name varchar(128) [note: '小計名稱'] remark varchar(255) [note: '備注'] plan_name varchar(128) [note: '小計名稱-采購'] production_name varchar(128) [note: '小計名稱-生產'] sort_num int(11) [note: '排序值'] Note: '客戶商品小計關聯表'
}Table customer_product {id bigint(20) [note: '記錄ID'] client_id bigint(20) [note: '客戶ID'] customer_product_name varchar(255) [note: '客戶成品名稱'] customer_product_code varchar(255) [note: '客戶成品編碼'] customer_product_alias varchar(255) [note: '客戶成品代號'] customer_product_process text [note: '加工工藝描述'] customer_product_label text [note: '關聯組合成品名稱(combo名稱)'] customer_product_tag text [note: '關聯組合成品代碼(combo編碼)'] customer_product_image text [note: '客戶成品圖片'] subtotal_id bigint(20) [note: '小計關聯'] subtotal_name varchar(255) [note: '小計分類名稱'] unit_name varchar(50) [note: '單位名稱'] unit_spec int(11) [note: '單位規格'] custom_price decimal(10,2) [note: '客戶成品定價'] piece_count decimal(10,2) [note: '計件價格'] gross_weight int(11) [note: '毛重'] pure_weight int(11) [note: '凈重'] currency varchar(10) [note: '幣種'] custom_spec1 varchar(50) [note: '規格1'] custom_spec2 varchar(50) [note: '規格2'] custom_length varchar(50) [note: '客戶商品長度'] status tinyint(4) [note: '狀態(1=啟用,0=停用)'] user_created varchar(255) [note: '創建人'] user_id int(11) [note: '創建人id'] user_updated varchar(255) [note: '更新人'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] remark varchar(255) [note: '備注'] Note: '客戶商品表'
}Table customer_combo {id bigint(20) [note: '記錄ID'] customer_product_id bigint(20) [note: '客戶商品ID'] customer_combo_name varchar(255) [note: '組合名稱'] customer_combo_code varchar(255) [note: '組合代碼'] quantity int(11) [note: '組合數量'] Note: '客戶組合商品表'
}Table customer_combo_item {id bigint(20) [note: '組合項ID'] customer_combo_id bigint(20) [note: '組合ID'] product_id bigint(20) [note: '所屬組合的商品ID'] category_id bigint(20) [note: '所屬組合的分類ID'] category_name varchar(255) [note: '分類名稱'] item_name varchar(255) [note: '組合名稱'] item_type varchar(50) [note: '組合項類型(LABEL/PRODUCT)'] quantity int(11) [note: '數量'] label_name varchar(50) [note: '標簽名稱'] replace_product_id varchar(255) [note: '可替換商品ID'] replace_label varchar(255) [note: '可替換標簽'] remark text [note: '備注'] Note: '客戶組合商品明細表'
}Table customer_combo_attribute {id bigint(20) [note: '記錄ID'] item_id bigint(20) [note: '組合項ID'] attribute_id bigint(20) [note: '屬性ID'] option_id bigint(20) [note: '屬性值ID'] Note: '客戶組合商品屬性表'
}Table customer_combo_material {id bigint(20) [note: '輔料ID'] customer_product_id bigint(20) [note: '客戶商品ID'] customer_combo_id bigint(20) [note: '客戶商品組合ID'] product_id bigint(20) [note: '產品ID'] process_type varchar(255) [note: '標記所屬工藝'] product_type varchar(255) [note: '標記物料類型'] quantity int(11) [note: '數量'] remark text [note: '備注'] is_new tinyint(4) [note: '是否新數據:1-新'] Note: '客戶組合輔料表'
}Table customer_combo_material_attribute {id bigint(20) [note: '記錄ID'] customer_material_id bigint(20) [note: '輔料ID,關聯customer_combo_material表id'] attribute_id bigint(20) [note: '屬性ID'] option_id bigint(20) [note: '屬性值ID'] Note: '客戶組合輔料屬性值表'
}Table customer_product_process {id bigint(20) [note: '記錄ID'] customer_product_id bigint(20) [note: '客戶商品ID'] process_id bigint(20) [note: '工藝id'] description text [note: '描述'] source varchar(255) [note: '來源'] created_at datetime [note: '創建時間'] updated_at datetime [note: '更新時間'] Note: '客戶組合商品工藝表'
}Table customer_product_process_resource {id bigint(20) [note: 'ID'] customer_process_id bigint(20) [note: '客戶商品工藝ID,關聯customer_product_process表id'] product_id bigint(20) [note: '工藝產品id'] Note: '客戶組合商品工藝物料關聯表'
}
總結
表與表之間的關系,需要在導出的DBML中用ref定義。