本教程将教你快速生成数据库字典
示例代码使用PHP框架：Thinkphp5.0
PHP代码：
1 /** 2 * 生成数据库字典html 3 * 可直接另存为再copy到word文档中使用 4 * 5 * @return mixed 6 */ 7 public function dataDictionary() 8 { 9 $tables = Db::query('SHOW TABLE STATUS'); 10 $table_list = array_map('array_change_key_case', $tables); 11 $table_data = []; 12 foreach ($table_list as $item) { 13 $table_name = str_replace(config('database.prefix'),'',$item['name']); 14 $table_fields = $this->showColumns($table_name); 15 foreach ($table_fields as &$fieldItem) { 16 $fieldItem['comment'] = $this->getDbColumnComment($table_name, $fieldItem['name']); 17 } 18 $table_data[] = [ 19 'table_name' => $item['name'], 20 'table_comment' => $item['comment'], 21 'table_fields' => $table_fields 22 ]; 23 } 24 $this->assign('table_data', $table_data); 25 26 return $this->fetch(''); 27 } 28 29 /** 30 * 显示表结构信息 31 * 32 * @param string $table 33 * @return array 34 */ 35 private function showColumns($table){ 36 37 $sql = 'SHOW COLUMNS FROM `'.config('database.prefix').$table.'`'; 38 $result = Db::query($sql); 39 if ($result === false) return array(); 40 $array = array(); 41 if (!empty($result)) { 42 foreach ($result as $k=>$v) { 43 $array[$v['Field']] = [ 44 'name' => $v['Field'], 45 'type' => $v['Type'], 46 'null' => $v['Null'], 47 'default' => $v['Default'], 48 'primary' => (strtolower($v['Key']) == 'pri'), 49 'autoinc' => (strtolower($v['Extra']) == 'auto_increment'), 50 ]; 51 } 52 } 53 54 return $array; 55 } 56 57 /** 58 * 获取数据库字段注释 59 * 60 * @param string $table_name 数据表名称(必须，不含前缀) 61 * @param string|boolean $field 字段名称(默认获取全部字段,单个字段请输入字段名称) 62 * @param string $table_schema 数据库名称(可选) 63 * @return string 64 */ 65 private function getDbColumnComment($table_name = '', $field = true, $table_schema = ''){ 66 // 接收参数 67 $database = config('database'); 68 69 $table_schema = empty($table_schema) ? $database['database'] : $table_schema; 70 $table_name = $database['prefix'] . $table_name; 71 72 // 处理参数 73 $param = [ 74 $table_name, 75 $table_schema 76 ]; 77 78 // 字段 79 $columnName = ''; 80 if($field !== true){ 81 $param[] = $field; 82 $columnName = "AND COLUMN_NAME = ?"; 83 } 84 85 // 查询结果 86 $result = Db::query("SELECT COLUMN_NAME as field,column_comment as comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ? AND table_schema = ? $columnName", $param); 87 if(empty($result) && $field !== true){ 88 return $table_name . '表' . $field . '字段不存在'; 89 } 90 91 // 处理结果 92 foreach($result as $k => $v){ 93 $data[$v['field']] = $v['comment']; 94 if(strpos($v['comment'], '#*#') !== false){ 95 $tmpArr = explode('#*#', $v['comment']); 96 $data[$v['field']] = json_decode(end($tmpArr), true); 97 } 98 } 99 // 字段注释格式不正确 100 if(empty($data)){ 101 return $table_name . '表' . $field . '字段注释格式不正确'; 102 } 103 return count($data) == 1 ? reset($data) : $data; 104 }
Html代码：
1 <html> 2 <head> 3 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 4 <title>快速生成数据库字典</title> 5 <style type="text/css"> 6 .table-name { 7 text-align: center; 8 margin: 15px auto; 9 font-weight: bold; 10 font-size: 20px; 11 } 12