mysql 数据字典生成工具 php版
普通代码
<?php
/**
* 生成mysql数据字典
*/
header('Content-type: text/html; charset=utf-8');
//配置数据库
$dbserver = "127.0.0.1";
$dbusername = "";
$dbpassword = "";
$database = "";
//其他配置
$title = '数据字典';
$type = intval($_GET['t']);
$mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
mysql_select_db($database, $mysql_conn);
mysql_query('SET NAMES utf8', $mysql_conn);
$table_result = mysql_query('show tables', $mysql_conn);
//取得所有的表名
while ($row = mysql_fetch_array($table_result)) {
$tables[]['TABLE_NAME'] = $row[0];
}
//数据字典生成
if($type == 0){
//循环取得所有表的备注及表中列消息
foreach ($tables AS $key=>$var) {
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.TABLES ';
$sql .= 'WHERE ';
$sql .= "table_name = '{$var['TABLE_NAME']}' AND table_schema = '{$database}'";
$table_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($table_result) ) {
$tables[$key]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];
}
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'WHERE ';
$sql .= "table_name = '{$var['TABLE_NAME']}' AND table_schema = '{$database}'";
$fields = array();
$field_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($field_result) ) {
$fields[] = $t;
}
$tables[$key]['COLUMN'] = $fields;
}
mysql_close($mysql_conn);
$html = '';
//循环所有表
foreach ($tables AS $key=>$var) {
$html .= '<h3>' . $var['TABLE_NAME'] .' '. $var['TABLE_COMMENT']. '</h3>';
$html .= '<table class="table table-hover table-bordered table-condensed">';
$html .= '<thead>
<tr>
<th>字段名</th>
<th>数据类型</th>
<th>默认值</th>
<th>允许非空</th>
<th>自动递增</th>
<th>备注</th>
</tr>
</thead>';
$html .= '<tbody>';
foreach ($var['COLUMN'] AS $v) {
$html .= '<tr>';
$html .= '<td>' . $v['COLUMN_NAME'] . '</td>';
$html .= '<td>' . $v['COLUMN_TYPE'] . '</td>';
$html .= '<td>' . $v['COLUMN_DEFAULT'] . '</td>';
$html .= '<td>' . $v['IS_NULLABLE'] . '</td>';
$html .= '<td>' . ($v['EXTRA']=='auto_increment'?'是':'') . '</td>';
$html .= '<td>' . $v['COLUMN_COMMENT'] . '</td>';
$html .= '</tr>';
}
$html .= '</tbody>';
$html .= '</table>';
}
}
//表创建语句
else if($type == 1){
foreach($tables AS $key=>$var){
$sql = 'show create table `'.$var['TABLE_NAME'].'`';
$table_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($table_result) ) {
$tables[$key]['TABLE_CREATE'] = $t[1];
}
}
$html = 'SET FOREIGN_KEY_CHECKS=0;';
foreach($tables as $var){
$html .= "\r\n\r\n\r\n".'DROP TABLE IF EXISTS `'.$var['TABLE_NAME'].'`;'."\r\n";
$html .= $var['TABLE_CREATE'];
}
$html = '<pre><code class="sql">'.htmlspecialchars($html).'</code></pre>';
}
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title><?php echo $title?></title>
<link href="http://cdn.bootcss.com/twitter-bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
<?php
if($type == 1){
echo '<link href="http://cdn.bootcss.com/highlight.js/7.4/styles/googlecode.min.css" rel="stylesheet">';
echo '<script src="http://cdn.bootcss.com/highlight.js/7.4/highlight.min.js"></script>';
echo '<script src="http://cdn.bootcss.com/highlight.js/7.4/lang/sql.min.js"></script>';
echo '<script>hljs.initHighlightingOnLoad();</script>';
}
?>
</head>
<body>
<div class="container">
<?php
echo '<h1 style="text-align:center;">'.$title.'[<a href="?t=1">查看创建语句</a>]</h1>';
echo $html;
?>
</div>
</body>
</html>
## mediawiki 版
<?php
/**
* 生成mysql mediawiki 版
*/
header('Content-type: text/html; charset=utf-8');
//配置数据库
$dbserver = "";
$dbusername = "";
$dbpassword = "";
$database = "";
//其他配置
$type = intval($_GET['t']);
$mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
mysql_select_db($database, $mysql_conn);
mysql_query('SET NAMES utf8', $mysql_conn);
$table_result = mysql_query('show tables', $mysql_conn);
//取得所有的表名
while ($row = mysql_fetch_array($table_result)) {
$tables[]['TABLE_NAME'] = $row[0];
}
//循环取得所有表的备注及表中列消息
foreach ($tables AS $key=>$var) {
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.TABLES ';
$sql .= 'WHERE ';
$sql .= "table_name = '{$var['TABLE_NAME']}' AND table_schema = '{$database}'";
$table_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($table_result) ) {
$tables[$key]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];
}
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'WHERE ';
$sql .= "table_name = '{$var['TABLE_NAME']}' AND table_schema = '{$database}'";
$fields = array();
$field_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($field_result) ) {
$fields[] = $t;
}
$tables[$key]['COLUMN'] = $fields;
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.statistics ';
$sql .= 'WHERE ';
$sql .= "table_name = '{$var['TABLE_NAME']}' AND table_schema = '{$database}'";
$index = array();
$index_result = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($index_result) ) {
$index[$t['INDEX_NAME']][] = $t['COLUMN_NAME'];
}
$tables[$key]['INDEX'] = $index;
}
mysql_close($mysql_conn);
$html = array();
//循环所有表
foreach ($tables AS $key=>$var) {
$html[] = '===='.$var['TABLE_NAME'].(empty($var['TABLE_COMMENT']) ? '' : '('.$var['TABLE_COMMENT'].')' ).'====';
$html[] = '{| class="wikitable"';
$html[] = '|-';
$html[] = '! width="150px" | 字段名 || width="150px"| 数据类型 || width="150px"| 默认值 || width="150px" | 允许非空 || width="150px" | 自动递增 || width="200px" | 备注';
foreach ($var['COLUMN'] AS $v) {
$v['COLUMN_NAME'] = empty($v['COLUMN_NAME']) ? ' ' : $v['COLUMN_NAME'];
$v['COLUMN_TYPE'] = empty($v['COLUMN_TYPE']) ? ' ' : $v['COLUMN_TYPE'];
$v['COLUMN_DEFAULT'] = empty($v['COLUMN_DEFAULT']) ? ' ' : $v['COLUMN_DEFAULT'];
$v['IS_NULLABLE'] = empty($v['IS_NULLABLE']) ? ' ' : $v['IS_NULLABLE'];
$v['IS_AUTO_INCREMENT'] = $v['EXTRA'] != 'auto_increment' ? ' ' : '是';
$v['COLUMN_COMMENT'] = empty($v['COLUMN_COMMENT']) ? ' ' : $v['COLUMN_COMMENT'];
$html[] = '|-';
$html[] = '|'.$v['COLUMN_NAME'].'||'.$v['COLUMN_TYPE'].'||'.$v['COLUMN_DEFAULT'].'||'.$v['IS_NULLABLE'].'||'.$v['IS_AUTO_INCREMENT'].'||'.$v['COLUMN_COMMENT'];
}
$html[] = '|}';
if(!empty($var['INDEX'])){
$html[] = '{| class="wikitable"';
$html[] = '|-';
$html[] = '! width="150px" | 索引名称 || width="300px" | 索引字段';
foreach($var['INDEX'] as $key=>$v){
$html[] = '|-';
$html[] = "|".$key."||".implode(',', $v);
}
$html[] = '|}';
}
}
echo implode("\n", $html);