先日、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

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)

information_schema_tables

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>

information_schema_tables_csv

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>

詳細はまた次回再確認確認

Comments are closed.

Post Navigation