使用PHP生成MySQL数据字典

<?php
    $dbhost = '127.0.0.1:3306';                         // mysql服务器主机地址
    $dbuser = 'root';                                 // mysql用户名
    $dbpass = 'root';                         // mysql用户名密码
    $dbname = "shopxo";
    //$mysql = mysqli_connect($dbhost, $dbuser, $dbpass);//这是mysql的链接方法
    $mysql = new mysqli("localhost", $dbuser,$dbpass,$dbname, "3306"); //这是mysqli的链接方法
    if(! $mysql )
    {
        die('连接失败: ' . mysqli_error($mysql));
    }
    $html = expertDirectory($mysql,$dbname);
    echo "<pre>";
    var_dump($html);
    /**
     * 导出数据库字典
     */
    function expertDirectory($mysql,$dbname)
    {
        mysqli_query($mysql, "set names 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 = '{$dbname}'";
            $table_result = $mysql->query($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 = '{$dbname}'";
            $fields = array();
            $field_result = $mysql->query($sql);
            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']. '）'."
";
            $html .= '  '."
";
            $html .= '    '."
";
          $html .= '      '."
";
          $html .= '        字段名'."
";
          $html .= '        数据类型'."
";
          $html .= '        默认值'."
";
          $html .= '        允许非空'."
";
          $html .= '        自动递增'."
";
          $html .= '        备注'."
";
          $html .= '      '."
";
            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 .= '      '."
";
              $html .= '        ' . $f['COLUMN_NAME'] . ''."
";
              $html .= '        ' . $f['COLUMN_TYPE'] . ''."
";
              $html .= '        ' . $f['COLUMN_DEFAULT'] . ''."
";
              $html .= '        ' . $f['IS_NULLABLE'] . ''."
";
              $html .= '        ' . ($f['EXTRA']=='auto_increment'?'是':' ') . ''."
";
              $html .= '        ' . $f['COLUMN_COMMENT'] . ''."
";
              $html .= '      '."
";
            }
            }
            $html .= '    '."
";
          $html .= '  '."
";
        }
        return $html;
    }