mysql 数据字典 php_PHP MYSQL数据字典

* 生成mysql数据字典*/

header ( "Content-type: text/html; charset=utf-8");//配置数据库

$dbserver = "localhost:3306";$dbusername = "root";$dbpassword = "root";$database = isset($_GET['db'])?$_GET['db']:'mydb';//其他配置

$title = '数据字典';$mysql_conn = mysqli_connect ( $dbserver, $dbusername, $dbpassword,$database ) or die ( "错误！MYSQL连接失败.");mysqli_query ( $mysql_conn,'SET NAMES utf8');$table_result = mysqli_query ( $mysql_conn,'show tables');//取得所有的表名

while ( $row = mysqli_fetch_array ( $table_result) ) {$tables [] ['TABLE_NAME'] = $row [0];

}//循环取得所有表的备注及表中列消息

foreach ( $tables as $k => $v) {$sql = 'SELECT * FROM ';$sql .= 'INFORMATION_SCHEMA.TABLES ';$sql .= 'WHERE ';$sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";$table_result = mysqli_query ( $mysql_conn,$sql);while ( $t = mysqli_fetch_array ( $table_result) ) {$tables [$k] ['TABLE_COMMENT'] = $t ['TABLE_COMMENT'];

}$sql = 'SELECT * FROM ';$sql .= 'INFORMATION_SCHEMA.COLUMNS ';$sql .= 'WHERE ';$sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";$fields = array();$field_result = mysqli_query ( $mysql_conn,$sql);while ( $t = mysqli_fetch_array ( $field_result) ) {$fields [] = $t;

}$tables [$k] ['COLUMN'] = $fields;

}mysqli_close ( $mysql_conn);$html = '';//循环所有表

foreach ( $tables as $k => $v) {$html .= '

' . $v ['TABLE_NAME'] . ' ' . $v ['TABLE_COMMENT'] . '

字段名	数据类型	默认值	允许非空	自动递增	备注
' . $f ['COLUMN_NAME'] . '	' . $f ['COLUMN_TYPE'] . '	' . $f ['COLUMN_DEFAULT'] . '	' . $f ['IS_NULLABLE'] . '	' . ($f ['EXTRA'] == 'auto_increment' ? '是' : ' ') . '	' . $f ['COLUMN_COMMENT'] . '

}$html .= '

';

}echo '

自动生成数据字典

body,td,th {font-family:"宋体"; font-size:12px;}

table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;}

table caption{text-align:left; background-color:#ffd; line-height:2em; font-size:14px; font-weight:bold; color:#040;}

table th{text-align:left; height:26px; font-weight:normal;font-size:12px; border:1px solid #CCC;}

table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;}

.c1{ width: 120px;}

.c2{ width: 120px;}

.c3{ width: 70px;}

.c4{ width: 80px;}

.c5{ width: 80px;}

.c6{ width: 270px;}

'.$title.' -- '.$database.'

';echo $html;echo '';?>