資料庫字典生成-php教程-PHP中文網

資料庫字典生成

WBOY

發布： 2016-07-25 08:48:01

原創

1327 人瀏覽過

php產生mysql資料庫的資料字典

/**
* 產生mysql資料字典
*/
header("Content-type: text/html; charset=utf-8");
//設定資料庫
$dbserver = "127.0.0.1";
$dbusername = "root";
$dbpassword = "";
$database = "think_team";
//其他設定
$mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
mysql_select_db($data,select_db($data, $mysql_conn);
mysql_query('SET NAMES utf8', $mysql_conn);
$table_result = mysql_query('show tables', $mysql_conn);
$no__table; /不需要顯示的表格
$no_show_field = array(); //不需要顯示的欄位
//取得所有的表名
while($row = mysql_fetch_array($table_result)) {
if(!in_array($row[0],$no_show_table)){
$tables[]['TABLE_NAME'] = $row[0];
}
}
//替換所以表的表前綴
if($_GET['prefix']){
$prefix = 'czzj';
foreach($tables as $key => $val){
$tableName = $val['TABLE_NAME'];
$string = explode('_',$tableName);
if($string[0] != $prefix){
$string[ 0] = $prefix;
$newTableName = implode('_', $string);
mysql_query('rename table '.$tableName.' TO '.$newTableName);
}
}
echo "替換成功！ ";exit();
}
//循環取得所有表的備註及表中列訊息
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 = mysql_query($sql, $mysql_conn);
while ($t = mysql_fetch_array($table_result) ) {
$tables[$table_result) ) {
$tables[$ k]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];
}
$sql = 'SELECT * FROM ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql .= 'INFORMATION_SCHEMA.COLUMNS ';
$sql. > $sql .= 'WHERE ';
$sql .= "table_name = '{$v['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[$k]['COLUMN'] = $fields;
}
mysql_close($mysql_conn);
$html = '';
//循環所有表格
foreach ($tables as $k=>$v) {
$html .= '
' . ($k 1) . '、' . $v['TABLE_COMMENT'] . ' （'. $v['TABLE_NAME']. '）
'."n";

$html .= ' '."n";

$html .= '

'."n";

$html .= '

'."n";

$ html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n ";

foreach ($v['COLUMN'] as $f) {

if(!is_array($no_show_field[$v['TABLE_NAME']])){

$no_show_field['TABLE_NAME']])){

$no_show_field[ $v['TABLE_NAME']] = array();

}

if(!in_array($f['COLUMN_NAME'],$no_show_field[$v['TABLE_NAME']])){

$html .= '

'."n";

$html .= '

'."n" ;

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n";

$html .= '

'."n"; $html .= ' '."n"; } } $html .= ' '."n"; $html . = '

欄位名稱	資料型別	預設值	允許非空	自動遞增	備註
' . $f['COLUMN_NAME'] . '	' . $f['COLUMN_TYPE'] . '	' . $f['COLUMN_DEFAULT'] . '	' . $f['IS_NULLABLE'] . '	' . ($f['EXTRA']=='auto_increment'?'是':' ' ) . '	' . $f['COLUMN_COMMENT'] . '