ということをやったので、メモ。

環境


  • MySQL 5.5.43

基本


information_schemaというスキーマにデータベースの情報が保存されているのでそちらを見る。

各テーブルのカラムの情報を取得する


各テーブルのカラムの情報を取得するには下記のようなSQLで取得できる。

1
2
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT, extra from information_schema.COLUMNS
where TABLE_SCHEMA = 'schema_name'

さらに、今回はデータ型に絞って取得したかった。
例えばdatetime型のカラムを確認したければ、下記のSQLで取得できる。

1
2
3
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT, extra from information_schema.COLUMNS
where TABLE_SCHEMA = 'schema_name'
and DATA_TYPE = 'datetime'

ただ、Nullableかどうかの取得の方法はわからなかった。

テーブルの情報を取得する


下記のSQLでテーブルの情報を取得することができる。

1
2
select * from information_schema.TABLES
where TABLE_SCHEMA = 'schema_name'

最近定義を更新したテーブルを確認したい


例えばALTER TABLE文で最近定義を更新したテーブルの情報を取得したい場合があると思います。
だが、少し試してみた範囲ではこれを調べる方法はなかった。

ただALTER TABLE実行後はinformation_schema.TABLESCREATE_TIMEの値が更新されているので下記のようなSQLで最近更新されたテーブルを上の方に表示することは可能です。

1
2
3
select * from information_schema.TABLES
where TABLE_SCHEMA = 'schema_name'
order by CREATE_TIME desc

よくわからないのが、なぜかUPDATE_TIMEは更新されずにCREATE_TIMEが更新されている。ALTER TABLE実行だとUPDATE_TIMEが更新されそうな感じなんだけど、なぜか更新されていない。う~ん、正直これはよくわからないな~。

おまけでMySQLで使用されている日付関連のデータ型を調べてみた


もともと、これをやろうとしたきっかけがとあるカラムの日付型をtimestampからdatetimeに変更したいというものでした。
ついでですので、MySQLのスキーマ。つまりinformation_schemaで使用されている日付関連の型は何かということを調べました。

結果、information_schemaで使用されている日付関連の型はすべてdatetime型でした。
information_schema内でdatetime型を使用していたのは下記テーブルです。

テーブル カラム デフォルト値
EVENTS EXECUTE_AT 指定なし
EVENTS STARTS 指定なし
EVENTS ENDS 指定なし
EVENTS CREATED 0000-00-00 00:00:00
EVENTS LAST_ALTERED 0000-00-00 00:00:00
EVENTS LAST_EXECUTED 指定なし
FILES CREATION_TIME 指定なし
FILES LAST_UPDATE_TIME 指定なし
FILES LAST_ACCESS_TIME 指定なし
FILES CREATE_TIME 指定なし
FILES UPDATE_TIME 指定なし
FILES CHECK_TIME 指定なし
PARTITIONS CREATE_TIME 指定なし
PARTITIONS UPDATE_TIME 指定なし
PARTITIONS CHECK_TIME 指定なし
ROUTINES CREATED 0000-00-00 00:00:00
ROUTINES LAST_ALTERED 0000-00-00 00:00:00
TABLES CREATE_TIME 指定なし
TABLES UPDATE_TIME 指定なし
TABLES CHECK_TIME 指定なし
TRIGGERS CREATED 指定なし
INNODB_TRX trx_started 0000-00-00 00:00:00
INNODB_TRX trx_wait_started 指定なし

以上。