数据字典

最新推荐文章于 2022-05-12 10:21:23 发布

ChasChan

最新推荐文章于 2022-05-12 10:21:23 发布

阅读量649

点赞数

CC 4.0 BY-SA版权

分类专栏：代码文章标签：数据字典数据表

本文链接：https://blog.youkuaiyun.com/qq_29582277/article/details/80881299

代码专栏收录该内容

9 篇文章

订阅专栏

<?php
header('content-type:text/html;charset=utf-8');
date_default_timezone_set("PRC");
define('DB_CONNECTION','mysql');
define('DB_HOST','localhost');
define('DB_USERNAME','root');
define('DB_PASSWORD','root');
define('DB_DATABASE','test');
define('DB_PORT',3306);
define('DB_CHARSET','utf8');
$title = '数据字典';
$html = '';
//连库
try {
    $pdo=new PDO(DB_CONNECTION.":host=".DB_HOST.";dbname=".DB_DATABASE,DB_USERNAME,DB_PASSWORD);
}catch (PDOException $e){
    echo '数据库连接失败：'.$e->getMessage();
    exit;
}
$table_result=$pdo->query('show tables');
$arr=$table_result->fetchAll(PDO::FETCH_ASSOC);
//取得所有的表名
foreach ($arr as $val){
    $tables[]['TABLE_NAME'] =$val['Tables_in_test'];
}
//循环取得所有表的备注及表中列消息
foreach ($tables AS $k=>$v) {
    $fields = array();
    $sql1  = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '{$v['TABLE_NAME']}'";
    $sql2  = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{$v['TABLE_NAME']}'";
    $t1=$pdo->query($sql1)->fetchAll(PDO::FETCH_ASSOC);
    $t2=$pdo->query($sql2)->fetchAll(PDO::FETCH_ASSOC);
    foreach($t1 as $v1) {
        $tables[$k]['TABLE_COMMENT'] = $v1['TABLE_COMMENT'];
    }
    foreach ($t2 as $v2) {
        $fields[] = $v2;
    }
    $tables[$k]['COLUMN'] = $fields;
}
    $html = '<div style="text-align:right"><h3>查询时间:'.date("Y/m/d H:i:s",time()).'</h3></div>';
//循环所有表
foreach ($tables AS $k=>$v) {
    $html .= '<table  border="1" cellspacing="0" cellpadding="0" align="center">';
    $html .= '<caption>' . $v['TABLE_NAME'] .'  '. $v['TABLE_COMMENT']. '</caption>';
    $html .= '<tbody>
                <tr>
                    <th>字段名</th>
                    <th>数据类型</th>
                    <th>默认值</th>
                    <th>允许非空</th>
                    <th>自动递增</th>
                    <th>备注</th>
                </tr>';
    foreach ($v['COLUMN'] AS $f) {
        $html .= '<tr>
                    <td class="c1">' . $f['COLUMN_NAME'] . '</td>
                    <td class="c2">' . $f['COLUMN_TYPE'] . '</td>
                    <td class="c3"> ' . $f['COLUMN_DEFAULT'] . '</td>
                    <td class="c4"> ' . $f['IS_NULLABLE'] . '</td>
                    <td class="c5">' . ($f['EXTRA']=='auto_increment'?'是':' ') . '</td>
                    <td class="c6"> ' . $f['COLUMN_COMMENT'] . '</td>
                   </tr>';
    }
    $html .= '</tbody></table>';
}
//输出
echo '<html>
         <head>
         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
         <title>'.$title.'</title>
            <style>
             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:#fff; line-height:2em; font-size:14px; font-weight:bold; }
             table th{font-weight:bold;height:26px; line-height:26px; font-size:12px; border:1px solid #CCC;text-align:center}
             table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;text-align:center}
             .c1{ width: 120px;}.c2{ width: 120px;}.c3{ width: 70px;}.c4{ width: 80px;}.c5{ width: 80px;}.c6{ width: 270px;}
         </style>
         </head>
         <body>
            <h1 style="text-align:center;">'.$title.'</h1>'.$html.'
         </body>
       </html>';