Skip to content

Instantly share code, notes, and snippets.

@shyazusa

shyazusa/0_README.md

Last active Dec 21, 2016
Embed
What would you like to do?
MySQLのテーブル定義をマークダウン(Qiita用)で出力する - Qiita http://qiita.com/tomoyuki_okawa/items/1db3e7bca4fa44cb7cba

Usage

  1. $ git clone https://gist.github.com/c551b29ac9a42e55c2de86ad333db882.git /tmp/mysql-database-to-md
  2. Edit settings
  3. $ vim /tmp/mysql-database-to-md/mysql-database-to-md.php
$host = '';
$user = '';
$pass = '';
  1. $ php /tmp/mysql-database-to-md/mysql-database-to-md.php database_name all > /tmp/mysql-database-to-md/mysql-database-to-md.md
  2. $ rm -rf /tmp/mysql-database-to-md/
<?php
// 引数がアレな場合はusage表示
if (count($argv) !== 3) {
echo "usage: php ./{$argv[0]} database_name table_name|all \n";
exit();
}
// 引数取得 1:データベース 2:テーブル名
$database = $argv[1];
$table_names = array();
// データベース接続設定
$host = '';
$user = '';
$pass = '';
$dsn = "mysql:dbname={$database};host={$host};charset=utf8";
// データベースオプション:Exception吐く、フェッチの結果はカラム名のみ
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
try {
$pdo = new PDO($dsn, $user, $pass, $options);
}
catch (PDOException $e) {
exit($e->getMessage() . "\n");
}
if ($argv[2] === 'all') {
$list_table_stmt = $pdo->query("SHOW TABLES FROM {$database}");
$list_table_stmt->execute();
while ($row = $list_table_stmt->fetch()) {
$table_names[] = $row["Tables_in_{$database}"];
}
}
else {
$table_names[] = $argv[2];
}
// 表示するshow full columnsの結果
$outputColumns = array('Field', 'Type', 'Null', 'Key', 'Default', 'Extra', 'Comment');
ob_start();
$table_stmt = $pdo->prepare("SHOW TABLE STATUS LIKE :table_name");
foreach ($table_names as $table_name) {
// テーブルコメント出力
$table_stmt->bindValue(':table_name', $table_name);
$table_stmt->execute();
while ($row = $table_stmt->fetch()) {
echo "# {$table_name}\n";
echo "{$row['Comment']}\n";
}
echo "## {$table_name} Fields\n\n";
echo '|' . implode('|', $outputColumns) . "|\n";
echo '|';
foreach ($outputColumns as $dummny) {
echo ':--|';
}
echo "\n";
// カラムの定義出力
try {
$stmt = $pdo->query("SHOW FULL COLUMNS FROM {$table_name}");
$stmt->execute();
}
catch (PDOException $e) {
ob_clean();
exit($e->getMessage() . "\n");
}
while ($row = $stmt->fetch()) {
echo '|';
foreach ($outputColumns as $column_name) {
echo "{$row[$column_name]}|";
}
echo "\n";
}
echo "\n";
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.