php生成markdown格式的数据库字典

发表于 分类于

每次开发项目时，总是会被要求提供数据字典
如果一个表一个表的写那就太麻烦

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
<?php

/**  
 * 生成mysql数据字典  
 */

//数据库配置
$config = [
    'host'     => 'localhost',
    'user'     => 'root',
    'password' => '111111',
];

function characet($data){
    if( !empty($data) ){
        $fileType = mb_detect_encoding($data , array('UTF-8','GBK','LATIN1','BIG5')) ;
        if( $fileType != 'UTF-8'){
            $data = mb_convert_encoding($data ,'utf-8' , $fileType);
        }
    }
    return $data;
}

function export_dict($dbname, $config) {
    $title = $dbname.' 数据字典';
    $dsn = 'mysql:dbname='.$dbname.';host='.$config['host'];
    //数据库连接
    try {
        $con = new PDO($dsn, $config['user'], $config['password'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    } catch (PDOException $e) {
        die('Connection failed: ' . $e->getMessage());
    }

    $con->query('SET NAMES utf8');
    $tables = $con->query('SHOW tables')->fetchAll(PDO::FETCH_COLUMN);
    
    //取得所有的表名
    foreach ($tables as $table) {
        $_tables[]['TABLE_NAME'] = $table;
    }

    //循环取得所有表的备注及表中列消息  
    foreach ($_tables as $k => $v) {  

        $sql = 'SELECT * FROM ';  
        $sql .= 'INFORMATION_SCHEMA.TABLES ';  
        $sql .= 'WHERE ';  
        $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$dbname}'";  
        $tr = $con->query($sql)->fetch(PDO::FETCH_ASSOC);
        $_tables[$k]['TABLE_COMMENT'] = $tr['TABLE_COMMENT'];

        $sql = 'SELECT * FROM ';  
        $sql .= 'INFORMATION_SCHEMA.COLUMNS ';  
        $sql .= 'WHERE ';  
        $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$dbname}'";  
        $fields = [];  
        $field_result = $con->query($sql)->fetchAll(PDO::FETCH_ASSOC);  

        foreach ($field_result as $fr)
        {
            $fields[] = $fr;
        }
        $_tables[$k]['COLUMN'] = $fields;
    }  
    unset($con);

    $mark = '';

    //循环所有表  
    foreach ($_tables as $k => $v) {  

        $mark .= '## '.$v['TABLE_NAME'].'  '. characet($v['TABLE_COMMENT']) .PHP_EOL;
        $mark .= ''.PHP_EOL;
        $mark .= '|  字段名  |  数据类型  |  默认值  |  允许非空  |  自动递增  |  备注  |'.PHP_EOL;
        $mark .= '| ------ | ------ | ------ | ------ | ------ | ------ |'.PHP_EOL;
        foreach ($v['COLUMN'] as $f) {  
            $mark .= '| '.$f['COLUMN_NAME'].' | '.$f['COLUMN_TYPE'].' | '.$f['COLUMN_DEFAULT'].' | '.$f['IS_NULLABLE'].' | '.($f['EXTRA'] == 'auto_increment' ? '是' : '').' | '.(empty($f['COLUMN_COMMENT']) ? '-' : str_replace('|', '/', $f['COLUMN_COMMENT'])).' |'.PHP_EOL;
        }  
        $mark .= ''.PHP_EOL;

    }  

    //markdown输出
    $md_tplt = <<<EOT
# {$title}
>   本数据字典由PHP脚本自动导出,字典的备注来自数据库表及其字段的注释(`comment`).开发者在增改库表及其字段时,请在 `migration` 时写明注释,以备后来者查阅.

{$mark}
EOT;

    //html输出
    $marked_text = htmlentities($md_tplt);
    $html_tplt = <<<EOT
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>{$title} - Powered By Markdown Viewer</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <link rel="stylesheet" type="text/css" href="http://s1.ystatic.cn/41345695beaa9b2e/css/github-markdown.css">
    <script src="http://s1.ystatic.cn/lib/marked/marked.js"></script>
    <script src="http://s1.ystatic.cn/lib/highlight.js/highlight.pack.js?v=9.6.0"></script>
    <link href="http://s1.ystatic.cn/lib/highlight.js/styles/github.css?v=9.6.0" rel="stylesheet">
</head>
<body>
<div class="markdown-body" id="content" style="margin:auto; width: 1024px;">

</div>
<div id="marked_text" style="display:none;">
{$marked_text}
</div>
<script>
var marked_text = document.getElementById('marked_text').innerText;
var renderer = new marked.Renderer();
renderer.table = function(header, body) {
    return '<table class="table table-bordered table-striped">\\n'
            + '<thead>\\n'
            + header
            + '</thead>\\n'
            + '<tbody>\\n'
            + body
            + '</tbody>\\n'
            + '</table>\\n';
};
marked.setOptions({
    renderer: renderer,
    gfm: true,
    tables: true,
    breaks: false,
    pedantic: false,
    sanitize: true,
    smartLists: true,
    smartypants: false,
    langPrefix: 'language-',
    //这里使用了highlight对代码进行高亮显示
    highlight: function (code) {
        return hljs.highlightAuto(code).value;
    }
});
document.getElementById('content').innerHTML = marked(marked_text);
  </script>
</body>
</html>
EOT;

    file_put_contents($dbname.'.md', $md_tplt);
    file_put_contents($dbname.'.html', $html_tplt);
}

$dbs = ['databases'];//库名
foreach ($dbs as $db) {
    export_dict($db, $config);
}
?>

转载