Help us understand the problem. What is going on with this article?

MySQLのテーブル定義をマークダウン(Qiita用)で出力する

More than 3 years have passed since last update.

php echo_mysql_table_structure.php database table で単一テーブルの定義を出力できます。
table の部分を all に変更してやると全てのテーブル定義を出力します。

Qiita:Teamでテーブル定義とか共有することが多いのでつくりました。

動作確認環境

PHP: 5.5.36
MySQL: 5.6.23

スクリプト

$host $user $pass はいい感じに入れてください。

echo_mysql_table_structure.php
<?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 = [];

// データベース接続設定
$host = '';
$user = '';
$pass = '';
$dsn  = "mysql:dbname={$database};host={$host};charset=utf8";

// データベースオプション:Exception吐く、フェッチの結果はカラム名のみ
$options = [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 = ['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";
}
snowrobin-inc
こんなのみたことない!を世界中に。
https://www.snowrobin.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした