【MySQL】show table status で engineで検索できる？（MyISAMエンジンのテーブルだけ抽出するなど）

Tweet

Category MySQL 2010/07/28

問題

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] |                |               |
+---------------+--------------+-----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+