Go to Qiita Advent Calendar 2024 Top
LoginSignup
9
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

ペライチアドベントカレンダーAdvent Calendar 2021

Day 5
@nagami_naoki

MySQLのテーブル定義をMarkdown形式に出力するシェル

Last updated at Posted at 2021-12-04

※ペライチアドベントカレンダー (12/5)の記事になります！

背景

今所属している株式会社ペライチ では
esaというサービスでドキュメント管理をしています。
Qiitaと同じくMarkdown形式で記載します。
DB定義のページの更新を楽にしたく、ちょっとシェルを組んでみました。

サンプルDB

CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL COMMENT '部署No',
  `dept_name` varchar(40) NOT NULL COMMENT '部署名',
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部署テーブル';

CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL COMMENT '社員No',
  `dept_no` char(4) NOT NULL COMMENT '部署No',
  `from_date` date NOT NULL COMMENT '開始日',
  `to_date` date NOT NULL COMMENT '終了日',
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配属テーブル';

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL COMMENT '社員No',
  `birth_date` date NOT NULL COMMENT '生年月日',
  `first_name` varchar(14) NOT NULL COMMENT '姓',
  `last_name` varchar(16) NOT NULL COMMENT '名',
  `gender` enum('M','F') NOT NULL COMMENT '性別',
  `hire_date` date NOT NULL COMMENT '雇用日',
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='社員テーブル';

CREATE TABLE `schema_migrations` (
  `version` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL公式サンプル
https://dev.mysql.com/doc/index-other.html
employee dataのテーブルデータを一部拝借しました。

シェルスクリプト

ファイル名はご自由に。
仮にdb_ddl_markdown.shとします。

#!/bin/sh

# MySQL接続情報
DB_USER='root'
DB_PASS='root'
DB_HOST='127.0.0.1'
DB_PORT='3306'
DB_SCHEMA='employees'
MYSQL_CONNECT="mysql --default-character-set=utf8 -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT} ${DB_SCHEMA}"
# 出力不要のテーブル
IGNORE_TABLES=(schema_migrations others)

# Markdown形式で出力
echo "## DB構成"
echo
{
for TABLE in $(sh -c "${MYSQL_CONNECT} -N -e 'show tables'");do
    for IGNORE in "${IGNORE_TABLES[@]}"; do
        if [[ "$IGNORE" = "$TABLE" ]]; then
            continue 2
        fi
    done

    echo "### ${TABLE}"
    echo
    COLUMNS=$(sh -c "${MYSQL_CONNECT} -t -e 'show full columns from ${TABLE}'")
    echo "${COLUMNS}" | sed -e '1d;$d;s/+/|/g' | awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'
    echo
done
} 2> /dev/null

出力結果

$ sh db_ddl_markdown.sh

## DB構成

### departments

| Field     | Type        | Null | Key | Default | Extra | Comment   |
|-----------|-------------|------|-----|---------|-------|-----------|
| dept_no   | char(4)     | NO   | PRI | NULL    |       | 部署No    |
| dept_name | varchar(40) | NO   | UNI | NULL    |       | 部署名    |

### dept_emp

| Field     | Type    | Null | Key | Default | Extra | Comment   |
|-----------|---------|------|-----|---------|-------|-----------|
| emp_no    | int(11) | NO   | PRI | NULL    |       | 社員No    |
| dept_no   | char(4) | NO   | PRI | NULL    |       | 部署No    |
| from_date | date    | NO   |     | NULL    |       | 開始日    |
| to_date   | date    | NO   |     | NULL    |       | 終了日    |

### employees

| Field      | Type          | Null | Key | Default | Extra | Comment      |
|------------|---------------|------|-----|---------|-------|--------------|
| emp_no     | int(11)       | NO   | PRI | NULL    |       | 社員No       |
| birth_date | date          | NO   |     | NULL    |       | 生年月日     |
| first_name | varchar(14)   | NO   |     | NULL    |       | 姓           |
| last_name  | varchar(16)   | NO   |     | NULL    |       | 名           |
| gender     | enum('M','F') | NO   |     | NULL    |       | 性別         |
| hire_date  | date          | NO   |     | NULL    |       | 雇用日       |

解説

MYSQL_CONNECT="mysql --default-character-set=utf8 -u${DB_USER} -p${DB_PASS} -h${DB_HOST} -P${DB_PORT} ${DB_SCHEMA}"

まずはベースのMySQL接続コマンドを用意します。
サンプルなので愚直にユーザ名やパスワードをオプションに入れてますが
--defaults-extra-fileオプションを使ってソースコードには書かない方がいいでしょうね。

for TABLE in $(sh -c "${MYSQL_CONNECT} -N -e 'show tables'");do

-eオプションに実行させるSQLを書き、先のMySQL接続コマンドとつなげてコマンドの文字列を作成
sh -cで文字列の内容を実行します。

-Nオプションでカラム名無しでデータだけ出力されます。この部分だけの出力内容はこんな感じ。

departments
dept_emp
employees
schema_migrations

ここでは罫線は入ってきませんので、実行結果を
for 変数名 in $(...) とループさせてテーブルごとに処理していきます。

    for IGNORE in "${IGNORE_TABLES[@]}"; do
        if [[ "$IGNORE" = "$TABLE" ]]; then
            continue 2
        fi
    done

フレームワークで使うマイグレーションやログ用のテーブルなど、出力不要なテーブルだった場合、処理をスキップします
設定部分は IGNORE_TABLES=(schema_migrations others) です。

    COLUMNS=$(sh -c "${MYSQL_CONNECT} -t -e 'show full columns from ${TABLE}'")

テーブルごとにカラム情報を取得します。
descでもいいのでは？と思うかもしれませんが、カラムのCOMMENTの内容を撮りたいので show full columns fromで行います。
-tオプションで今度は罫線も含めて結果を得ます。

    echo "${COLUMNS}" | sed -e '1d;$d;s/+/|/g' | awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'

${COLUMNS}にカラム情報が入っているので Markdown形式に加工していきます。
echo "${COLUMNS}"だとテキストはまだ下記の感じです。
これをパイプ|で次のコマンドへと渡します。

+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+
| Field     | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment   |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+
| dept_no   | char(4)     | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references | 部署No    |
| dept_name | varchar(40) | utf8mb4_general_ci | NO   | UNI | NULL    |       | select,insert,update,references | 部署名    |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+-----------+

sed -e '1d;$d;s/+/|/g'
sedはテキスト変換によく使われるコマンド。
呪文めいてますが、、 1d;は1行目削除、$d;は最後の行を削除、s/+/|/gは+を|に変換するパラメータです。この段階を通るとテキストの内容はこうなります。

| Field     | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment   |
|-----------|-------------|--------------------|------|-----|---------|-------|---------------------------------|-----------|
| dept_no   | char(4)     | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references | 部署No    |
| dept_name | varchar(40) | utf8mb4_general_ci | NO   | UNI | NULL    |       | select,insert,update,references | 部署名    |

Markdown形式のテーブルの書き方になりましたね。
ただ、情報量が多いので絞りましょう。
awk -F"|" '{print "|"$2"|"$3"|"$5"|"$6"|"$7"|"$8"|"$10"|" }'
awkは先のsedと同じくテキスト処理でよく使われますが、
プログラミング言語としても使えて、演算やループなど凝ったことができます。
今回のは -Fオプションで区切り文字を指定し、printで列番号を指定して出力する、よく使われる書き方です。
|区切りだと一番左がパイプの外が1番目（空値)、Fieldが2番目になります。

| Field     | Type        | Null | Key | Default | Extra | Comment   |
|-----------|-------------|------|-----|---------|-------|-----------|
| dept_no   | char(4)     | NO   | PRI | NULL    |       | 部署No    |
| dept_name | varchar(40) | NO   | UNI | NULL    |       | 部署名    |

無事、Markdown形式にできました。
先の出力結果を貼り付けると↓になります。

DB構成

departments

Field Type Null Key Default Extra Comment
dept_no char(4) NO PRI NULL 部署No
dept_name varchar(40) NO UNI NULL 部署名

dept_emp

Field Type Null Key Default Extra Comment
emp_no int(11) NO PRI NULL 社員No
dept_no char(4) NO PRI NULL 部署No
from_date date NO NULL 開始日
to_date date NO NULL 終了日

employees

Field Type Null Key Default Extra Comment
emp_no int(11) NO PRI NULL 社員No
birth_date date NO NULL 生年月日
first_name varchar(14) NO NULL 姓
last_name varchar(16) NO NULL 名
gender enum('M','F') NO NULL 性別
hire_date date NO NULL 雇用日 
} 2> /dev/null

シェルのテクニックの一つ。
{}で複数行を囲んで一括りの処理とします。
mysqlコマンドを実行したときに、サンプルの書き方だとパスワードに関して警告が出るので、
エラー出力を無視するリダイレクトを入れてます。
デバッグ時には外してください。

最後に

シェルはもう少し改良の余地があるなーと思いつつ。。

次は 12/7 ペライチCTO @katsukii さんの「ペライチ開発チームのご紹介」になります！

9
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?