【MySQL】 information_schemaを用いて効率的に探したいデータの格納場所を見つけ出す

新しいバイト先でDBに触れることが多くなり、溢れかえったデータから検索したいものを絞りだす為に、information_schemaというものの存在を知るようになった。

information_schemaとは

データベースやテーブル等に関するメタデータを格納しているデータベース。
ここに格納されているデータを利用することで取り出したいデータの絞り込みができるようになる。
(特定のカラムを含むテーブルや、該当テーブルを含むデータベースの検索など)

よく使うinfomation_schemaのテーブル

基本的に以下の二つのテーブルをよく使う。

TABLES

テーブルのメタデータを格納しているテーブル。
テーブルの中身はこんな感じ↓

mysql> select * from information_schema.TABLES where table_name = "test";

*****************************************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test_database
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2020-02-03 17:24:14
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

TABLE_SCHEMAにデータベース名、TABLE_NAMEにテーブル名が格納されている。

例として、該当テーブルを含むデータベースの検索を行いたい際は、以下のようにすることによって絞り込みができる。

select TABLE_SCHEMA from information_schema.TABLES where TABLE_NAME = '該当テーブル';

COLUMNS

カラムのメタデータを格納しているテーブル。

mysql> select * from information_schema.COLUMNS where table_name = "test”;
******************************************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test_database
              TABLE_NAME: test
             COLUMN_NAME: id
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(20)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:

例として、指定されたカラム名を持つテーブルを検索したい場合は、以下のようにすることによって絞り込みができる。

select TABLE_NAME from information_schema.COLUMNS where  COLUMN_NAME = '検索したいカラム名' (and table_schema = '該当データベース名');

他にも複数テーブルがあって、色々と活用できるのでもっと知りたい方は以下のMySQL公式ドキュメントを参考にしてみてください。

参考