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

環境


  • MySQL 5.5.43

基本


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

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


各テーブルのカラムの情報を取得するには下記のような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'

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

1
2
3
4
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型を使用していたのは下記テーブルです。

テーブルカラムデフォルト値
EVENTSEXECUTE_AT指定なし
EVENTSSTARTS指定なし
EVENTSENDS指定なし
EVENTSCREATED0000-00-00 00:00:00
EVENTSLAST_ALTERED0000-00-00 00:00:00
EVENTSLAST_EXECUTED指定なし
FILESCREATION_TIME指定なし
FILESLAST_UPDATE_TIME指定なし
FILESLAST_ACCESS_TIME指定なし
FILESCREATE_TIME指定なし
FILESUPDATE_TIME指定なし
FILESCHECK_TIME指定なし
PARTITIONSCREATE_TIME指定なし
PARTITIONSUPDATE_TIME指定なし
PARTITIONSCHECK_TIME指定なし
ROUTINESCREATED0000-00-00 00:00:00
ROUTINESLAST_ALTERED0000-00-00 00:00:00
TABLESCREATE_TIME指定なし
TABLESUPDATE_TIME指定なし
TABLESCHECK_TIME指定なし
TRIGGERSCREATED指定なし
INNODB_TRXtrx_started0000-00-00 00:00:00
INNODB_TRXtrx_wait_started指定なし

以上。