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; } ```