2017年11月9日09:30:29
用 PHP mysqli 写的一个类文件, 用来导出MySQL数据字典
导出表信息; 字段信息, 索引信息
可以导出浏览器适用的样式, 也可以导出word文档(默认720px)宽度,字体10px
建议上线前用这个导出一份, 整体过一遍, 防止有些字段, not null, 索引等设置不到位的情况
https://gitee.com/myDcool/PHP-DBDIC
用法:
1 include('./DBdic.php'); 2 3 //浏览器显示 4 DBdic::ini('localhost', 'db_name', 'username', 'password')->outForBrowser(); 5 6 //下载word文档 7 DBdic::ini('localhost', 'db_name', 'username', 'password')->outForWord();
1 <?php 2 /** 3 * 生成mysql数据字典 4 */ 5 class DBdic 6 { 7 public $database = array(); //数据库配置 8 public $tables = array(); //读取的表信息数组 9 public $htmlTable = ''; //表格内容 10 public $html = ''; 11 12 public static function ini($host, $dbname, $user, $pwd) 13 { 14 return new self($host, $dbname, $user, $pwd); 15 } 16 17 function __construct($host, $dbname, $user, $pwd) 18 { 19 // 配置数据库 20 $this->database['DB_HOST'] = $host; 21 $this->database['DB_NAME'] = $dbname; 22 $this->database['DB_USER'] = $user; 23 $this->database['DB_PWD'] = $pwd; 24 25 //链接MySQL 26 $mysqli = mysqli_init(); 27 $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 2); //超时2s 28 $mysqli->options(MYSQLI_INIT_COMMAND, "set names utf8mb4;"); 29 $mysqli->real_connect($this->database['DB_HOST'], $this->database['DB_USER'], $this->database['DB_PWD'], $this->database['DB_NAME']) or die("Mysql connect is error."); 30 31 // 取得所有表名 32 $rs = $mysqli->query('show tables'); 33 $arrTableName = array_column($rs->fetch_all(), $value=0); 34 35 // 取得所有表信息 36 foreach ($arrTableName as $name) { 37 38 //表注释 39 $sql = "select * from information_schema.tables where table_schema = '{$this->database['DB_NAME']}' and table_name = '{$name}' "; //查询表信息 40 $rs = $mysqli->query($sql); 41 $arrTableInfo = $rs->fetch_assoc(); 42 43 //各字段信息 44 $sql = "select * from information_schema.columns where table_schema ='{$this->database['DB_NAME']}' and table_name = '{$name}' "; //查询字段信息 45 $rs = $mysqli->query($sql); 46 $arrColumnInfo = $rs->fetch_all(MYSQLI_ASSOC); 47 48 //索引信息 49 $sql = "show index from {$name}"; 50 $rs = $mysqli->query($sql); 51 $arrIndexInfo = $rs->fetch_all(MYSQLI_ASSOC); 52 53 $this->tables[] = array( 54 'TABLE' => $arrTableInfo, 55 'COLUMN' => $arrColumnInfo, 56 'INDEX' => $this->getIndexInfo($arrIndexInfo) 57 ); 58 } 59 60 //组装HTML 61 $html = ''; 62 foreach($this->tables as $v) 63 { 64 $html .= '<table border="1" cellspacing="0" cellpadding="0" align="center">'; 65 $html .= '<caption>' . $v['TABLE']['TABLE_NAME'] . ' ' . $v['TABLE']['TABLE_COMMENT'] . '</caption>'; 66 $html .= '<tbody><tr><th>字段名</th><th>数据类型</th><th>默认值</th><th>允许非空</th><th>索引/自增</th><th>备注(字段数: '. count($v['COLUMN']).')</th></tr>'; 67 68 foreach ($v['COLUMN'] AS $f) { 69 $html .= '<tr>'; 70 $html .= '<td class="c1">' . $f['COLUMN_NAME'] . '</td>'; 71 $html .= '<td class="c2">' . $f['COLUMN_TYPE'] . '</td>'; 72 $html .= '<td class="c3">' . $f['COLUMN_DEFAULT'] . '</td>'; 73 $html .= '<td class="c4">' . $f['IS_NULLABLE'] . '</td>'; 74 $html .= '<td class="c5">' . $f['COLUMN_KEY'].' '.$f['EXTRA']. '</td>'; 75 $html .= '<td class="c6">' . $f['COLUMN_COMMENT'] . '</td>'; 76 $html .= '</tr>'; 77 } 78 $html .= '</tbody></table>'; 79 80 $html .= '<table style="border-top:hidden" cellspacing="0" cellpadding="0" align="center">'; 81 $html .= '<tr><th>索引名</th><th>索引顺序</th></tr>'; 82 foreach ($v['INDEX'] as $indexName => $indexContent) { 83 $html .= '<tr>'; 84 $html .= '<td class="c7">' . $indexName . '</td>'; 85 $html .= '<td>' . implode('; ', $indexContent) . '</td>'; 86 $html .= '</tr>'; 87 } 88 $html .= '</table><br>'; 89 } 90 $this->htmlTable = $html; 91 } 92 93 //整合单个表的所有索引(将复合索引归纳到一起) 94 function getIndexInfo($arrIndexInfo) 95 { 96 $index = array(); 97 foreach ($arrIndexInfo as $v) { 98 $unique = ($v['Non_unique'] == 0) ? '(unique)' : ''; 99 $index[$v['Key_name']][] = $v['Seq_in_index'].': '.$v['Column_name'].$unique; 100 } 101 102 return $index; 103 } 104 105 //输出到浏览器, 表格宽度用百分比 106 function outForBrowser() 107 { 108 header("Content-type:text/html;charset=utf-8"); 109 $html = '<html> 110 <meta charset="utf-8"> 111 <title>自动生成数据字典</title> 112 <style> 113 body,td,th {font-family:"宋体"; font-size:14px;} 114 table,h1,p{width:80%;margin:0px auto;} 115 table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;} 116 table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; } 117 table th{text-align:left; font-weight:bold;height:26px; line-height:26px; font-size:14px; border:1px solid #CCC;padding-left:5px;} 118 table td{height:20px; font-size:14px; border:1px solid #CCC;background-color:#fff;padding-left:5px;} 119 .c1{ width: 10%;} 120 .c2{ width: 10%;} 121 .c3{ width: 5%;} 122 .c4{ width: 5%;} 123 .c5{ width: 10%;} 124 .c6{ width: 60%;} 125 .c7{ width: 10%;} 126 </style> 127 <body>'; 128 $html .= '<h1 style="text-align:center;">'.$this->database['DB_NAME'].'数据字典</h1>'; 129 $html .= '<p style="text-align:center;margin:20px auto;">生成时间：' . date('Y-m-d H:i:s') . ' 总共：' . count($this->tables) . '个数据表</p>'; 130 $html .= $this->htmlTable; 131 $html .= '</body></html>'; 132 133 $this->html = $html; 134 echo $html; 135 // return $this; 136 } 137 138 //输出到word文档, 固定宽度为720px 139 function outForWord() 140 { 141 /* 生成word */ 142 header("Content-type:text/html;charset=utf-8"); 143 header( "Content-type:application/vnd.ms-word" ); 144 header( "Content-Disposition:attachment;filename={$this->database['DB_NAME']}数据字典.doc" ); 145 146 $html = '<html> 147 <meta charset="utf-8"> 148 <title>自动生成数据字典</title> 149 <style> 150 body,td,th {font-family:"宋体"; font-size:14px;} 151 table,h1,p{width:720px;margin:0px auto;} 152 table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;} 153 table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; } 154 table th{text-align:left; font-weight:bold;height:20px; line-height:20px; font-size:11px; border:1px solid #CCC;padding-left:5px;} 155 table td{height:20px; font-size:11px; border:1px solid #CCC;background-color:#fff;padding-left:5px;} 156 .c1{ width: 100px;} 157 .c2{ width: 110px;} 158 .c3{ width: 50px;} 159 .c4{ width: 55px;} 160 .c5{ width: 100px;} 161 .c6{ width: 300px;} 162 .c7{ width: 200px;} 163 </style> 164 <body>'; 165 $html .= '<h1 style="text-align:center;">'.$this->database['DB_NAME'].'数据字典</h1>'; 166 $html .= '<p style="text-align:center;margin:20px auto;">生成时间：' . date('Y-m-d H:i:s') . ' 总共：' . count($this->tables) . '个数据表</p>'; 167 $html .= $this->htmlTable; 168 $html .= '</body></html>'; 169 170 $this->html = $html; 171 echo $html; 172 // return $this; 173 } 174 175 function out() 176 { 177 // echo $this->html; 178 } 179 180 }
