search

Home >Backend Development >PHP Tutorial >在Thinkphp5中如何生成数据库字典

在Thinkphp5中如何生成数据库字典

PHPz
PHPzOriginal
2016-06-06 20:33:331570browse

在Thinkphp5中如何生成数据库字典

如何生成数据库字典？

示例代码使用PHP框架：Thinkphp5.0

PHP代码：

/**
     * 生成数据库字典html
     * 可直接另存为再copy到word文档中使用
     *
     * @return mixed
     */
    public function dataDictionary()
    {
        $tables = Db::query('SHOW TABLE STATUS');
        $table_list = array_map('array_change_key_case', $tables);
        $table_data = [];
        foreach ($table_list as $item) {
            $table_name = str_replace(config('database.prefix'),'',$item['name']);
            $table_fields = $this->showColumns($table_name);
            foreach ($table_fields as &$fieldItem) {
                $fieldItem['comment'] = $this->getDbColumnComment($table_name, $fieldItem['name']);
            }
            $table_data[] = [
                'table_name' => $item['name'],
                'table_comment' => $item['comment'],
                'table_fields' => $table_fields
            ];
        }
        $this->assign('table_data', $table_data);

        return $this->fetch('');
    }

    /**
     * 显示表结构信息
     *
     * @param string $table
     * @return array
     */
    private function showColumns($table){

        $sql = 'SHOW COLUMNS FROM `'.config('database.prefix').$table.'`';
        $result = Db::query($sql);
        if ($result === false) return array();
        $array = array();
        if (!empty($result)) {
            foreach ($result as $k=>$v) {
                $array[$v['Field']] = [
                    'name'    => $v['Field'],
                    'type'    => $v['Type'],
                    'null'       => $v['Null'],
                    'default' => $v['Default'],
                    'primary' => (strtolower($v['Key']) == 'pri'),
                    'autoinc' => (strtolower($v['Extra']) == 'auto_increment'),
                ];
            }
        }

        return $array;
    }

    /**
     * 获取数据库字段注释
     *
     * @param string $table_name 数据表名称(必须，不含前缀)
     * @param string|boolean $field 字段名称(默认获取全部字段,单个字段请输入字段名称)
     * @param string $table_schema 数据库名称(可选)
     * @return string
     */
    private function getDbColumnComment($table_name = '', $field = true, $table_schema = ''){
        // 接收参数
        $database = config('database');

        $table_schema = empty($table_schema) ? $database['database'] : $table_schema;
        $table_name = $database['prefix'] . $table_name;

        // 处理参数
        $param = [
            $table_name,
            $table_schema
        ];

        // 字段
        $columnName = '';
        if($field !== true){
            $param[] = $field;
            $columnName = "AND COLUMN_NAME = ?";
        }

        // 查询结果
        $result = Db::query("SELECT COLUMN_NAME as field,column_comment as comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ? AND table_schema = ? $columnName", $param);
        if(empty($result) && $field !== true){
            return $table_name . '表' . $field . '字段不存在';
        }

        // 处理结果
        foreach($result as $k => $v){
            $data[$v['field']] = $v['comment'];
            if(strpos($v['comment'], '#*#') !== false){
                $tmpArr = explode('#*#', $v['comment']);
                $data[$v['field']] = json_decode(end($tmpArr), true);
            }
        }
        // 字段注释格式不正确
        if(empty($data)){
            return $table_name . '表' . $field . '字段注释格式不正确';
        }
        return count($data) == 1 ? reset($data) : $data;
    }

Html代码：

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>快速生成数据库字典</title>
    <style type="text/css">
        .table-name {
            text-align: center;
            margin: 15px auto;
            font-weight: bold;
            font-size: 20px;
        }

        table {
            border-collapse: collapse;
            margin: 0 auto;
            text-align: center;
            width: 550px;
        }

        table caption {
            margin: 15px auto;
        }

        table td, table th {
            border: 1px solid #cad9ea;
            color: #666;
            height: 30px;
        }

        table thead th {
            background-color: #CCE8EB;
            width: 100px;
        }

        table tr:nth-child(odd) {
            background: #fff;
        }

        table tr:nth-child(even) {
            background: #F5FAFA;
        }
    </style>
</head>
<body>

{foreach $table_data as $tableInfo}
<div class="table-name">{$tableInfo.table_name}(表注释：{$tableInfo.table_comment})</div>
<table class="table">
    <thead>
    <tr>
        <th>字段</th>
        <th>类型</th>
        <th>空</th>
        <th>默认</th>
        <th>注释</th>
    </tr>
    </thead>
    <tbody>
    {foreach $tableInfo[&#39;table_fields&#39;] as $field}
    <tr>
        <td>
            {$field.name}
        </td>
        <td>
            {$field.type}
        </td>
        <td>
            {$field.null}
        </td>
        <td style="max-width: 30px;">
            {$field.default}
        </td>
        <td>
            {$field.comment}
        </td>
    </tr>
    {/foreach}
    </tbody>
</table>
{/foreach}


</body>
</html>

注：如需保存到word文档，需网页另存为html文件并用word软件打开，将内容全选复制到新的word文档中即可。

更多相关知识，请访问PHP中文网！

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：php的超文本预处理中预处理应该怎么理解？Next article：ajax - 使用php从数据库中读取信息，在网页上创建表格，有哪些可用的库

Related articles

See more