php mysql 数据字典_php 导出Mysql 数据字典

1 <?php2 /**3 * 生成mysql数据字典4 */

5

6 //配置数据库

7 $dbserver = "localhost";8 $dbusername = "root";9 $dbpassword = "root";10 $database = "secomtel";11 //其他配置

12 $title = '数据字典';13

14 $mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");15 mysql_select_db($database, $mysql_conn);16 mysql_query('SET NAMES utf8', $mysql_conn);17 $table_result = mysql_query('show tables', $mysql_conn);18 //取得所有的表名

19 while ($row = mysql_fetch_array($table_result)) {20 $tables[]['TABLE_NAME'] = $row[0];21 }22

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

24 foreach ($tables AS $k=>$v) {25 $sql = 'SELECT * FROM ';26 $sql .= 'INFORMATION_SCHEMA.TABLES ';27 $sql .= 'WHERE ';28 $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";29 $table_result = mysql_query($sql, $mysql_conn);30 while ($t = mysql_fetch_array($table_result) ) {31 $tables[$k]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];32 }33

34 $sql = 'SELECT * FROM ';35 $sql .= 'INFORMATION_SCHEMA.COLUMNS ';36 $sql .= 'WHERE ';37 $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";38

39 $fields = array();40 $field_result = mysql_query($sql, $mysql_conn);41 while ($t = mysql_fetch_array($field_result) ) {42 $fields[] = $t;43 }44 $tables[$k]['COLUMN'] = $fields;45 }46 mysql_close($mysql_conn);47

48

49 $html = '';50 //循环所有表

51 foreach ($tables AS $k=>$v) {52 //$html .= '

'. $v['TABLE_COMMENT'] . '

';

53 $html .= '

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

字段名	数据类型	默认值	允许非空	自动递增	备注

60 foreach ($v['COLUMN'] AS $f) {61 $html .= '

' . $f['COLUMN_NAME'] . '';62 $html .= '' . $f['COLUMN_TYPE'] . '';63 $html .= ' ' . $f['COLUMN_DEFAULT'] . '';64 $html .= ' ' . $f['IS_NULLABLE'] . '';65 $html .= '' . ($f['EXTRA']=='auto_increment'?'是':' ') . '';66 $html .= ' ' . $f['COLUMN_COMMENT'] . '';67 $html .= '';68 }69 $html .= '';70 }71

72 //输出

73 echo '74

75 '.$title.'76 89 90 ';91 echo '

'.$title.'

';92 echo $html;93 echo '';94

95 ?>