db2markdown 数据表结构markdown格式

db2markdown.php

<?php
/**
 * 数据表结构markdown格式
 * @author iamsujun <[email protected]>
 * 
 * @example 返回markdown格式示例： 
 * ### picture
 * | Field | Type | Null | Default | Comment |
 * |--|--|--|--|--|
 * | iAutoID | int(11) | NO |  |  |
 * | sImgKey | char(40) | NO |  | Key |
 * | iCreateTime | int(10) unsigned | NO | 0 | 创建时间 |
 * ``` sql
 * CREATE TABLE `picture` (
 *   `iAutoID` int(11) NOT NULL AUTO_INCREMENT,
 *   `sImgKey` char(40) NOT NULL COMMENT 'Key',
 *   `iCreateTime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
 *   PRIMARY KEY (`iAutoID`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图片'
 * ```
 */

$ip = '127.0.0.1';
$port = '3306';
$user = 'admin';
$pwd = '******';
$db = 'db';

$link = new mysqli($ip . ":" . $port, $user, $pwd, $db);

$data = [];
$colums = [];
$re = $link->query('show table status');
while ($row = $re->fetch_array()) {
	$table = $row["Name"];
	$comment = $row["Comment"];
	$result = $link->query('SHOW FULL COLUMNS FROM ' . $table);
	$field = [];
	while ($r = $result->fetch_array(MYSQLI_ASSOC)) {
		unset($r['Key'], $r['Privileges'], $r['Extra'], $r['Collation']);
		$field[] = $r;
		if (empty($colums)) {
			$colums = array_keys($r);
		}
	}
	$data[$table]['comment'] = $comment;
	$data[$table]['field'] = $field;

	$ret = $link->query('SHOW create table ' . $table);
	$data[$table]['create'] = $ret->fetch_row()[1];
}
// print_r($data);

$num = count($colums);
$scolums = sprintf('| %s |', implode(' | ', $colums));
foreach ($data as $table => $value) {
	array_walk($value['field'], function (&$v) {
		$v = sprintf("| %s |", implode(' | ', $v));
	});
	$field = implode("\n", $value['field']);

	printf("\n### %s\ %s\n%s\n%s\n%s\n``` sql\n%s\n```\n", $table, $value['comment'], $scolums, str_repeat("|--", $num) . '|', $field, $value['create']);
	// printf("%s;\n\n",$value['create']);
}