数据字典生成器-PHP源码-php.cn

Home

php教程

PHP源码

数据字典生成器

PHP中文网

May 26, 2016 am 08:20 AM

自动生成数据字典

只需要修改
//配置数据库
$dbserver = "localhost";
$dbusername = "root";
$dbpassword = "";
$database = "test";
然后访问这个php文件即可

1. [代码][PHP]代码

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

//其他配置
$mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
mysql_select_db($database, $mysql_conn);
mysql_query(&#39;SET NAMES utf8&#39;, $mysql_conn);
$table_result = mysql_query(&#39;show tables&#39;, $mysql_conn);

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

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

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

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

    $fields = array();
    $field_result = mysql_query($sql, $mysql_conn);
    while ($t = mysql_fetch_array($field_result) ) {
        $fields[] = $t;
    }
    $tables[$k][&#39;COLUMN&#39;] = $fields;
}
mysql_close($mysql_conn);


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

    foreach ($v[&#39;COLUMN&#39;] as $f) {
		if(!is_array($no_show_field[$v[&#39;TABLE_NAME&#39;]])){
			$no_show_field[$v[&#39;TABLE_NAME&#39;]] = array();
		}
		if(!in_array($f[&#39;COLUMN_NAME&#39;],$no_show_field[$v[&#39;TABLE_NAME&#39;]])){
			$html .= &#39;			<tr>&#39;."\n";
			$html .= &#39;				<td class="c1">&#39; . $f[&#39;COLUMN_NAME&#39;] . &#39;</td>&#39;."\n";
			$html .= &#39;				<td class="c2">&#39; . $f[&#39;COLUMN_TYPE&#39;] . &#39;</td>&#39;."\n";
			$html .= &#39;				<td class="c3">&#39; . $f[&#39;COLUMN_DEFAULT&#39;] . &#39;</td>&#39;."\n";
			$html .= &#39;				<td class="c4">&#39; . $f[&#39;IS_NULLABLE&#39;] . &#39;</td>&#39;."\n";
			$html .= &#39;				<td class="c5">&#39; . ($f[&#39;EXTRA&#39;]==&#39;auto_increment&#39;?&#39;是&#39;:&#39; &#39;) . &#39;</td>&#39;."\n";
			$html .= &#39;				<td class="c6">&#39; . $f[&#39;COLUMN_COMMENT&#39;] . &#39;</td>&#39;."\n";
			$html .= &#39;			</tr>&#39;."\n";
		}
    }
    $html .= &#39;		</tbody>&#39;."\n";
	$html .= &#39;	</table>&#39;."\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>
<p class="warp">
<?php echo $html; ?>
</p>
</body>
</html>

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn