先日、MYSQLを5.5.27に更新したのでinformation_schemaを確認すると同時に
information_schema.tablesを改めて確認してみる。
MYSQL Version 5.5.27のinformation_schema
mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.5.27 | +-----------+ 1 row in set (0.00 sec) mysql> use information_schema; Database changed mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_CMP_RESET | | INNODB_TRX | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_CMPMEM | | INNODB_CMP | | INNODB_LOCKS | +---------------------------------------+ 37 rows in set (0.00 sec) mysql>
information_schema.tablesの基本構造
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
DBインスタンスで利用されているENGINEの種類
mysql> select distinct engine from information_schema.tables; +--------------------+ | engine | +--------------------+ | MEMORY | | MyISAM | | InnoDB | | CSV | | PERFORMANCE_SCHEMA | +--------------------+ 5 rows in set (0.00 sec) mysql>
InnoDBは自分で作成したテーブルがメインなので今回はあえて、
MYSQL、MEMORYエンジンなどを確認してみる。
MyISAMを利用しているTABLE
mysql> select table_schema, table_name -> from information_schema.tables -> where engine='MyISAM'; +--------------------+---------------------------+ | table_schema | table_name | +--------------------+---------------------------+ | information_schema | COLUMNS | | information_schema | EVENTS | | information_schema | PARAMETERS | | information_schema | PARTITIONS | | information_schema | PLUGINS | | information_schema | PROCESSLIST | | information_schema | ROUTINES | | information_schema | TRIGGERS | | information_schema | VIEWS | | mysql | columns_priv | | mysql | db | | mysql | event | | mysql | func | | mysql | help_category | | mysql | help_keyword | | mysql | help_relation | | mysql | help_topic | | mysql | host | | mysql | ndb_binlog_index | | mysql | plugin | | mysql | proc | | mysql | procs_priv | | mysql | proxies_priv | | mysql | servers | | mysql | tables_priv | | mysql | time_zone | | mysql | time_zone_leap_second | | mysql | time_zone_name | | mysql | time_zone_transition | | mysql | time_zone_transition_type | | mysql | user | +--------------------+---------------------------+ 31 rows in set (0.01 sec)
CSVを利用しているテーブル
mysql> select table_schema, table_name -> from information_schema.tables -> where engine='CSV'; +--------------+-------------+ | table_schema | table_name | +--------------+-------------+ | mysql | general_log | | mysql | slow_log | +--------------+-------------+ 2 rows in set (0.01 sec) mysql>
MEMORYを利用しているテーブル
mysql> select table_schema, table_name -> from information_schema.tables -> where engine='MEMORY'; +--------------------+---------------------------------------+ | table_schema | table_name | +--------------------+---------------------------------------+ | information_schema | CHARACTER_SETS | | information_schema | COLLATIONS | | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | | information_schema | COLUMN_PRIVILEGES | | information_schema | ENGINES | | information_schema | FILES | | information_schema | GLOBAL_STATUS | | information_schema | GLOBAL_VARIABLES | | information_schema | KEY_COLUMN_USAGE | | information_schema | PROFILING | | information_schema | REFERENTIAL_CONSTRAINTS | | information_schema | SCHEMATA | | information_schema | SCHEMA_PRIVILEGES | | information_schema | SESSION_STATUS | | information_schema | SESSION_VARIABLES | | information_schema | STATISTICS | | information_schema | TABLES | | information_schema | TABLESPACES | | information_schema | TABLE_CONSTRAINTS | | information_schema | TABLE_PRIVILEGES | | information_schema | USER_PRIVILEGES | | information_schema | INNODB_CMP_RESET | | information_schema | INNODB_TRX | | information_schema | INNODB_CMPMEM_RESET | | information_schema | INNODB_LOCK_WAITS | | information_schema | INNODB_CMPMEM | | information_schema | INNODB_CMP | | information_schema | INNODB_LOCKS | +--------------------+---------------------------------------+ 28 rows in set (0.01 sec) mysql>
詳細はまた次回再確認確認