SQL轉化為markdown語法 ``` public function sql2md() { /* 從文件中取出SQL語句字符串 */ $string = file_get_contents(SITE_PATH.'weibo.sql'); /* STEP1 將CREATE TABLE正則匹配出來放入數組,匹配完成后每張表的數據都儲存到數組的一個元素 */ $result = preg_match_all('/CREATE\s+TABLE.+?`(?P<name>\w+)`.+?\((?P<fields_str>.+?)PRIMARY\s+KEY\s+\(`(?P<primary_key>\w+)`\)(?P<keys>.+?)\)\sENGINE=(?P<engine>\w+).+?CHARSET=(?P<charset>[\w\d]+).+?COMMENT=\'(?P<comment>.+?)\'.+?AUTO_INCREMENT=\d+\s+;/is', $string, $matches, PREG_SET_ORDER); $tables = array(); /* STEP2 遍歷表數據數組,每一次循環處理一張表 */ foreach ($matches as $item) { /* STEP2-1 初始化表數據 */ $table = array( 'name' => $item['name'], 'primary_key' => $item['primary_key'], 'engine' => $item['engine'], 'charset' => $item['charset'], 'comment' => $item['comment'], 'fields' => array(), ); /* STEP2-2 處理字段部分,即CREATE TABLE括號中的部分,每一次循環處理一個字段 */ $fields_array = explode(',', $item['fields_str']); array_pop($fields_array); foreach ($fields_array as $fields_array_item) { /* STEP2-2-1 匹配字段的屬性 */ preg_match_all('/`(?P<name>\w+)`\s+(?P<type>\w+(?=\(|\s))(?P<length>\(\d+\))*.+?COMMENT\s\'(?P<comment>.+?)\'.*?/is', $fields_array_item, $matches, PREG_SET_ORDER); $field = array( 'name' => $matches[0]['name'], 'type' => $matches[0]['type'].$matches[0]['length'], 'comment' => $matches[0]['comment'], ); /* STEP2-2-2 判斷生成當前字段的屬性 */ $field['prop'] = array(); $field['prop']['auto_increment'] = TRUE && stripos($fields_array_item, 'auto_increment'); $field['prop']['unsigned'] = TRUE && stripos($fields_array_item, 'unsigned'); $field['prop']['zerofill'] = TRUE && stripos($fields_array_item, 'zerofill'); $field['not_null'] = TRUE && stripos($fields_array_item, 'not null'); $field['primary'] = $table['primary_key'] == $field['name']; /* STEP2-2-3 當前字段是否有默認值,有則對其進行正則匹配 */ $has_default = TRUE && stripos($fields_array_item, 'default'); if ($has_default) { preg_match_all('/DEFAULT\s+\'(?P<default>.*?)\'/is', $fields_array_item, $match, PREG_SET_ORDER); $field['default'] = $match[0]['default']; } /* STEP2-2-4 存入返回的變量中 */ $table['fields'][] = $field; } /* STEP2-3 處理表的索引 */ preg_match_all('/(?P<keyname>KEY|INDEX|UNIQUE)\s+`(?P<indexname>\w+)`\s+\((?P<indexvalues>.+?)\)/is', $item['keys'], $match_keys, PREG_SET_ORDER); if (!empty($match_keys)) { foreach ($match_keys as $match_key) { $match_key['indexvalues'] = str_replace('`', '', $match_key['indexvalues']); //處理聯合索引 if (strpos($match_key['indexvalues'], ',')){ $match_key['indexvalues'] = explode(',', $match_key['indexvalues']); } $table['index'][] = array( 'indexname' => $match_key['indexname'], 'unique' => strtolower($match_key['keyname']) == 'unique', 'indexvalues' => $match_key['indexvalues'] ); } } /* STEP2-4 */ $tables[] = $table; } /* STEP4 輸出Markdown,每次循環處理一張表 */ $th = array('字段名', '類型', '空', '默認值', '屬性', '備注'); $th = '|'.implode('|', $th).'|'."\n"; $th .= '|:---:|:---:|:---:|:---:|:---:|:---:|'."\n"; foreach ($tables as $table) { /* STEP4-1 輸出表結構 */ echo '#'.$table['name'].'('.$table['comment'].')'."\n"; echo $th; foreach ($table['fields'] as $field) { $prop_str_arr = ''; $field['primary'] && $prop_str_arr[] = '主鍵'; $field['prop']['auto_increment'] && $prop_str_arr[] = '自增'; $field['prop']['unsigned'] && $prop_str_arr[] = '非標記'; $field['prop']['zerofill'] && $prop_str_arr[] = '非標記并用零填充'; $arr = array($field['primary'] ? '**'.$field['name'].'**' : $field['name'], $field['type'], $field['not_null'] ? '非空' : '可為空', $field['default'], implode(',', $prop_str_arr), $field['comment'], ); echo '|'.implode('|', $arr).'|'."\n"; } /* STEP4-2 輸出表索引 */ if (!empty($table['index'])) { $index_th = array('鍵名', '唯一', '字段'); $index_th = '|'.implode('|', $index_th).'|'."\n"; $index_th .= '|:---:|:---:|:---:|'."\n"; echo "\n"; echo "###索引\n"; echo $index_th; foreach ($table['index'] as $index){ is_array($index['indexvalues']) && $index['indexvalues'] = implode(',', $index['indexvalues']); $arr = array($index['indexname'], $index['unique'] ? '是' : '否', $index['indexvalues']); echo '|'.implode('|', $arr).'|'."\n"; //print_r($index);echo "\n"; } } echo "\n-------------------------------------------\n\n"; } exit; } ```