ぽこやかざん雑記

データエンジニア / 下町モルモット / 広島カープファン / 深夜の馬鹿力 / おくやま

MySQLのスキーマ情報をサクッと取得する

MySQL

MySQL で「どのテーブルがどんな型のなんのカラムを持つのか」というスキーマ情報を取得したい場合のメモ。
ユースケースとしては、MySQL のテーブルデータを BigQuery に連携する際に、MySQL テーブルのスキーマ情報を元に BigQuery テーブルの DDL を作成する時など。
mysqldump や SHOW CREATE TABLE 文で DDL を取得することもできるが、スキーマ情報のみの取得で良い場合は、INFORMATION_SCHEMA.COLUMNS テーブルに対してクエリを実行するだけで取得することができる。

検証用のテーブルを準備

以下のように docker-compose.yml を作成

version: '3'
services:
  mysql:
    image: mysql
    tty: true
    container_name: mysql
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=password
    volumes:
      - .:/work
    working_dir: /work

Docker 起動

$ docker-compose up -d
# 確認
$ docker ps
# mysql という名前のコンテナが立っていればOK

MySQL テーブル(test_database.test_table)を作成

CREATE DATABASE IF NOT EXISTS test_database;
CREATE TABLE IF NOT EXISTS  test_database.test_table (
  id INT,
  num tinyint,
  flag tinyint(1),
  short_msg varchar(10),
  long_msg varchar(255),
  dt datetime
);

DDL 実行

$ docker exec -it mysql bash
# コンテナ内部
$ mysql -uroot -p < ddl.sql
# パスワード求められるので docker-compose.yml に記載のパスワードを入力

スキーマ情報を取得してみる

以下のように INFORMATION_SCHEMA.COLUMNS テーブルに対してクエリを実行することでスキーマ情報を取得することができる。

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test_database'
AND TABLE_NAME = 'test_table';

実際の出力

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME,
    ->   DATA_TYPE,
    ->   COLUMN_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'test_database'
    -> AND TABLE_NAME = 'test_table';
+------------+-------------+-----------+--------------+
| TABLE_NAME | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE  |
+------------+-------------+-----------+--------------+
| test_table | id          | int       | int          |
| test_table | num         | tinyint   | tinyint      |
| test_table | flag        | tinyint   | tinyint(1)   |
| test_table | short_msg   | varchar   | varchar(10)  |
| test_table | long_msg    | varchar   | varchar(255) |
| test_table | dt          | datetime  | datetime     |
+------------+-------------+-----------+--------------+
6 rows in set (0.01 sec)

気を付けるべき点としては DATA_TYPE と COLUMN_TYPE の違い。
DATA_TYPE の値は型の名前のみで他の情報は付与されないのに対して、COLUMN_TYPE の値は、型名と、精度や長さなどのその他の情報が含まれる。
この違いが重要になってくるのは tinyint と tinyint(1) の違い で、MySQL の tinyint(1) 型は他の DB でいう Boolean型 として振る舞う。

そのため DATA_TYPE だけでは tinyint と tinyint(1)(実質 Boolean) の違いを見分けることができないため、COLUMN_TYPE を見る必要がある。
ただ、DATA_TYPE が varchar の場合に COLUMN_TYPE を確認するべきかと言うと必ずしもそうではなくて、例えば MySQL 上の型を BigQuery の型に変換したい場合は、「DATA_TYPE だけ確認して varchar なら全部 STRING」といったことになると思う。
そのため、DATA_TYPE と COLUMN_TYPE は両方取得しておいて、必要に応じてどちらを確認するか選択するのが個人的にはオススメ。