mysql生成在线数据字典_生成mysql数据字典

data_dictionary.php

set_charset('utf8');

$table_result = $mysql->query('show tables');

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

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

//取得所有的表名

while($row = mysqli_fetch_array($table_result)){

if(!in_array($row[0],$no_show_table)){

$tables[]['TABLE_NAME'] = $row[0];

}

}

//替换所以表的表前缀

// if($_GET['prefix']){

// $prefix = 'sent_';

// 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 = 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 = $mysql->query($sql, $mysql_conn);

while ($t = mysqli_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[$v['TABLE_NAME']] = array(); } if(!in_array($f['COLUMN_NAME'],$no_show_field[$v['TABLE_NAME']])){ $html .= '

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

' . $f['COLUMN_NAME'] . ''."\n"; $html .= '

' . $f['COLUMN_TYPE'] . ''."\n"; $html .= '

' . $f['COLUMN_DEFAULT'] . ''."\n"; $html .= '

' . $f['IS_NULLABLE'] . ''."\n"; $html .= '

' . ($f['EXTRA']=='auto_increment'?'是':' ') . ''."\n"; $html .= '

' . $f['COLUMN_COMMENT'] . ''."\n"; $html .= ''."\n"; } } $html .= ''."\n";

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

}

?>

数据库数据字典

数据库数据字典