PHP生成MYSQL 数据字典

<?php
/**
 * 生成mysql数据字典
 */
header("Content-type: text/html; charset=utf-8");
//配置数据库
$dbserver   = "127.0.0.1";
$dbusername = "root";
$dbpassword = "root";
$database   = "posp";

//其他配置
$mysql&nbsp;=&nbsp;new&nbsp;MySQLi(&quot;$dbserver", "$dbusername&quot;,&nbsp;&quot;$dbpassword", "$database") or die("Mysql connect is error.");
$mysql -> set_charset('utf8');
$table_result&nbsp;=&nbsp;$mysql->query('show tables');

$no_show_table = array();    //不需要显示的表
$no_show_field = array();   //不需要显示的字段

//取得所有的表名
while($row&nbsp;=&nbsp;mysqli_fetch_array($table_result)){
    if(!in_array($row[0],$no_show_table)){
    $tables[][&#39;TABLE_NAME&#39;]&nbsp;=&nbsp;$row[0];
  }
}
//替换所以表的表前缀
// if($_GET['prefix']){
//  $prefix = 'sent_';
//  foreach($tables&nbsp;as&nbsp;$key => $val){
//    $tableName&nbsp;=&nbsp;$val['TABLE_NAME'];
//    $string&nbsp;=&nbsp;explode(&#39;_&#39;,$tableName);
//    if($string[0]&nbsp;!=&nbsp;$prefix){  
//      $string[0]&nbsp;=&nbsp;$prefix;  
//      $newTableName&nbsp;=&nbsp;implode(&#39;_&#39;,&nbsp;$string);  
//      $mysql-&gt;query(&#39;rename&nbsp;table&nbsp;&#39;.$tableName.' TO '.$newTableName);  
//    }
//  }
//  echo "替换成功!";exit();
// }

//循环取得所有表的备注及表中列消息
foreach ($tables&nbsp;as&nbsp;$k=>$v) {
    $sql  = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.TABLES ';
    $sql .= 'WHERE ';
    $sql&nbsp;.=&nbsp;&quot;table_name&nbsp;=&nbsp;&#39;{$v['TABLE_NAME']}'  AND table_schema = '{$database}'";
    $table_result&nbsp;=&nbsp;$mysql->query($sql,&nbsp;$mysql_conn);
    while ($t&nbsp;=&nbsp;mysqli_fetch_array($table_result) ) {
        $tables[$k]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];
    }

    $sql  = 'SELECT * FROM ';
    $sql .= 'INFORMATION_SCHEMA.COLUMNS ';
    $sql .= 'WHERE ';
    $sql&nbsp;.=&nbsp;&quot;table_name&nbsp;=&nbsp;&#39;{$v['TABLE_NAME']}' AND table_schema = '{$database}'";

    $fields = array();
    $field_result&nbsp;=&nbsp;$mysql->query($sql,&nbsp;$mysql_conn);
    while ($t&nbsp;=&nbsp;mysqli_fetch_array($field_result) ) {
        $fields[]&nbsp;=&nbsp;$t;
    }
    $tables[$k]['COLUMN'] = $fields;
}
$mysql-&gt;close($mysql_conn);


$html = '';
//循环所有表
foreach ($tables&nbsp;as&nbsp;$k=>$v) {
    $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&lt;h3&gt;&#39;&nbsp;.&nbsp;($k + 1) . '、' . $v[&#39;TABLE_COMMENT&#39;]&nbsp;.&#39;&nbsp;&nbsp;(&#39;.&nbsp;$v['TABLE_NAME']. ')</h3>'."n";
    $html .= '  <table border="1" cellspacing="0" cellpadding="0" width="100%">'."n";
    $html .= '    <tbody>'."n";
  $html .= '      <tr>'."n";
  $html .= '        <th>字段名</th>'."n";
  $html .= '        <th>数据类型</th>'."n";
  $html .= '        <th>默认值</th>'."n";
  $html .= '        <th>允许非空</th>'."n";
  $html .= '        <th>自动递增</th>'."n";
  $html .= '        <th>备注</th>'."n";
  $html .= '      </tr>'."n";

    foreach ($v[&#39;COLUMN&#39;]&nbsp;as&nbsp;$f) {
    if(!@is_array($no_show_field[$v['TABLE_NAME']])){
      $no_show_field[$v['TABLE_NAME']] = array();
    }
    if(!in_array($f[&#39;COLUMN_NAME&#39;],$no_show_field[$v['TABLE_NAME']])){
      $html .= '      <tr>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c1&quot;&gt;&#39;&nbsp;.&nbsp;$f['COLUMN_NAME'] . '</td>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c2&quot;&gt;&#39;&nbsp;.&nbsp;$f['COLUMN_TYPE'] . '</td>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c3&quot;&gt;&#39;&nbsp;.&nbsp;$f['COLUMN_DEFAULT'] . '</td>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c4&quot;&gt;&#39;&nbsp;.&nbsp;$f['IS_NULLABLE'] . '</td>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c5&quot;&gt;&#39;&nbsp;.&nbsp;($f['EXTRA']=='auto_increment'?'是':'&nbsp;') . '</td>'."n";
      $html&nbsp;.=&nbsp;&#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;td&nbsp;class=&quot;c6&quot;&gt;&#39;&nbsp;.&nbsp;$f['COLUMN_COMMENT'] . '</td>'."n";
      $html .= '      </tr>'."n";
    }
    }
    $html .= '    </tbody>'."n";
  $html .= '  </table>'."n";
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>数据库数据字典</title>
<style>
body, td, th { font-family: "微软雅黑"; font-size: 14px; }
.warp{margin:auto; width:900px;}
.warp h3{margin:0px; padding:0px; line-height:30px; margin-top:10px;}
table { border-collapse: collapse; border: 1px solid #CCC; background: #efefef; }
table th { text-align: left; font-weight: bold; height: 26px; line-height: 26px; font-size: 14px; text-align:center; border: 1px solid #CCC; padding:5px;}
table td { height: 20px; font-size: 14px; border: 1px solid #CCC; background-color: #fff; padding:5px;}
.c1 { width: 120px; }
.c2 { width: 120px; }
.c3 { width: 150px; }
.c4 { width: 80px; text-align:center;}
.c5 { width: 80px; text-align:center;}
.c6 { width: 270px; }
</style>
</head>
<body>
<div class="warp">
  <h1 style="text-align:center;">数据库数据字典</h1>
<?php echo $html; ?>
</div>
</body>
</html>
最后修改:2018 年 10 月 23 日 04 : 29 PM
如果觉得我的文章对你有帮助,请随意赞赏

发表评论