自动生成数据库字典php版

<?php /** * 生成html数据字典 */ $db_name = $_GET['name']; $db = new DBdict("localhost", $db_name, "root", "root"); switch ($_GET['type']) { case 1: //输出字典 $db->outForBrowser(); break; case 2: //输出word $db->outForWord(); break; default: // 带菜单输出字典 $db->outForBrowserWithMenu(); break; } class DBdict { public $database = array(); //数据库配置 public $tables = array(); //读取的表信息数组 public $htmlTable = ''; //表格内容 public $html = ''; public $exportTables = array(); // 要导出的表 public $menu = array(); //左侧表名的菜单 public static function ini($host, $dbname, $user, $pwd) { return new self($host, $dbname, $user, $pwd); } function __construct($host, $dbname, $user, $pwd) { // 配置数据库 $this->database['DB_HOST'] = $host; $this->database['DB_NAME'] = $dbname; $this->database['DB_USER'] = $user; $this->database['DB_PWD'] = $pwd; } public function build() { //链接MySQL $mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 2); //超时2s $mysqli->options(MYSQLI_INIT_COMMAND, "set names utf8mb4;"); $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."); // 取得所有表名 $rs = $mysqli->query('show tables'); $arrTableName = array_column($rs->fetch_all(), $value = 0); // 取得所有表信息 foreach ($arrTableName as $name) { $isExport = $this->isNeedExport($name); if (!$isExport) { continue; } //表注释 $sql = "select * from information_schema.tables where table_schema = '{$this->database['DB_NAME']}' and table_name = '{$name}' "; //查询表信息 $rs = $mysqli->query($sql); $arrTableInfo = $rs->fetch_assoc(); //各字段信息 $sql = "select * from information_schema.columns where table_schema ='{$this->database['DB_NAME']}' and table_name = '{$name}' "; //查询字段信息 $rs = $mysqli->query($sql); $arrColumnInfo = $rs->fetch_all(MYSQLI_ASSOC); //索引信息 $sql = "show index from {$name}"; $rs = $mysqli->query($sql); if (!empty($rs->num_rows)) { $arrIndexInfo = $rs->fetch_all(MYSQLI_ASSOC); } else { $arrIndexInfo = array(); } $this->tables[] = array( 'TABLE' => $arrTableInfo, 'COLUMN' => $arrColumnInfo, 'INDEX' => $this->getIndexInfo($arrIndexInfo) ); } //组装HTML $html = ''; foreach ($this->tables as $k => $v) { //左侧菜单信息 $this->menu[$k] = $v['TABLE']['TABLE_NAME']; //主要内容 $html .= '<table align="center">'; $html .= '<caption id="menu_' . $k . '"><h3>' . $v['TABLE']['TABLE_NAME'] . ' ' . $v['TABLE']['TABLE_COMMENT'] . '</h3></caption>'; $html .= '<tbody><tr><th>字段名</th><th>数据类型</th><th>默认值</th><th>允许非空</th><th>索引/自增</th><th>备注(字段数: ' . count($v['COLUMN']) . ')</th></tr>'; foreach ($v['COLUMN'] as $f) { $html .= '<tr>' . PHP_EOL; $html .= '<td class="c1">' . $f['COLUMN_NAME'] . '</td>' . PHP_EOL; $html .= '<td class="c2">' . $f['COLUMN_TYPE'] . '</td>' . PHP_EOL; $html .= '<td class="c3">' . $f['COLUMN_DEFAULT'] . '</td>' . PHP_EOL; $html .= '<td class="c4">' . $f['IS_NULLABLE'] . '</td>' . PHP_EOL; $html .= '<td class="c5">' . $f['COLUMN_KEY'] . ' ' . $f['EXTRA'] . '</td>' . PHP_EOL; $html .= '<td class="c6">' . $f['COLUMN_COMMENT'] . '</td>' . PHP_EOL; $html .= '</tr>' . PHP_EOL; } if (!empty($v['INDEX'])) { $html .= '<tr><th colspan="2">索引名</th><th colspan="4">索引顺序</th></tr>'; foreach ($v['INDEX'] as $indexName => $indexContent) { $html .= '<tr>' . PHP_EOL; $html .= '<td class="c7" colspan="2">' . $indexName . '</td>' . PHP_EOL; $html .= '<td class="c8" colspan="4">' . implode(' > ', $indexContent) . '</td>' . PHP_EOL; $html .= '</tr>' . PHP_EOL; } } $html .= '</tbody></table><br>' . PHP_EOL; } $this->htmlTable = $html; return $this; } //设置需要导出的表, 参数为单个表 function setExportTable($tableName) { $this->exportTables[] = $tableName; return $this; } //设置需要导出的表, 参数为数组 function setExportTableArray($arrTableName) { $this->exportTables = $arrTableName; return $this; } public function isNeedExport($tname) { //判断当前表是否要导出 $isExport = TRUE; if (!empty($this->exportTables)) { if (in_array($tname, $this->exportTables)) { //当前表在导出列表中 $isExport = TRUE; } else { $isExport = FALSE; //正则匹配 foreach ($this->exportTables as $tableName) { if (strpos($tableName, '*') !== FALSE || strpos($tableName, '+') !== FALSE || strpos($tableName, '\\') !== FALSE || strpos($tableName, '[') !== FALSE || strpos($tableName, '(') !== FALSE || strpos($tableName, '{') !== FALSE ) { if (preg_match("/$tableName/", $tname, $matches) == 1) { $isExport = TRUE; break; } } } } } return $isExport; } //整合单个表的所有索引(将复合索引归纳到一起) function getIndexInfo($arrIndexInfo) { $index = array(); foreach ($arrIndexInfo as $v) { $unique = ($v['Non_unique'] == 0) ? '(unique)' : ''; // $index[$v['Key_name']][] = $v['Seq_in_index'].': '.$v['Column_name'].$unique; $index[$v['Key_name']][] = $v['Column_name'] . $unique; } return $index; } function getHtmlMenu() { $html = '<div id="menu"><ul>'; foreach ($this->menu as $k => $v) { $id = 'menu_' . $k; $html .= '<li><a href="#' . $id . '">' . $v . '</a></li>'; } $html .= '</ul></div>'; return $html; } //输出到浏览器, 左侧有目录 function outForBrowserWithMenu() { $this->build(); header("Content-type:text/html;charset=utf-8"); $html = '<html> <meta charset="utf-8"> <title>' . $this->database['DB_NAME'] . '数据字典</title> <style> ::-webkit-scrollbar {display:none} header {display: block; width: 90%; align-content: center} #menu {position: fixed;float: left; width: 20%; height: 2000px; overflow-y: scroll} a:link,a:visited {color:#000;text-decoration:none;} #content {float: right; width: 80%; height: 2000px; overflow-y: scroll} tab