【MySQL】show table status で engineで検索できる?(MyISAMエンジンのテーブルだけ抽出するなど)
問題
show table status は、show table status like ‘%xxxx%’ によって、name(テーブル名)で絞込みが可能。
では、engineで検索はできる?InnoDBエンジンのテーブルだけ抽出するなどは可能?
答え
MySQL5ならINFORMATION_SCHEMAを使えば可能。
例
mysql> SELECT * FROM information_schema.tables where table_schema = 'test' and engine = 'InnoDB'; +---------------+--------------+-----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+-----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | [NULL] | test | b | BASE TABLE | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 24117248 | 6 | 2010-05-25 19:53:43 | [NULL] | [NULL] | utf8_general_ci | [NULL] | | | | [NULL] | test | postaldata | BASE TABLE | InnoDB | 10 | Compact | 119723 | 171 | 20496384 | 0 | 0 | 24117248 | 122750 | 2009-08-26 13:44:06 | [NULL] | [NULL] | utf8_general_ci | [NULL] | | | | [NULL] | test | t | BASE TABLE | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 24117248 | 6 | 2010-08-30 00:41:36 | [NULL] | [NULL] | utf8_general_ci | [NULL] | | | +---------------+--------------+-----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
コメント