SOFTELメモ Developer's blog

会社概要 ブログ 調査依頼 社員募集 ...

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

問題

show table status は、show table status like ‘%xxxx%’ によって、name(テーブル名)で絞込みが可能。

では、engineで検索はできる?InnoDBエンジンのテーブルだけ抽出するなどは可能?

engine-24274_640

答え

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

関連するメモ

コメント