Movatterモバイル変換

The Information SchemaFILES table provides metadata about allInnoDB tablespace types includingfile-per-table tablespaces,general tablespaces, thesystem tablespace,temporary table tablespaces, andundo tablespaces (if present).

This section providesInnoDB-specific usage examples. For more information about data provided by the Information SchemaFILES table, seeSection 28.3.15, “The INFORMATION_SCHEMA FILES Table”.

This query retrieves metadata about theInnoDB system tablespace from fields of the Information SchemaFILES table that are pertinent toInnoDB tablespaces.FILES columns that are not relevant toInnoDB always returnNULL, and are excluded from the query.

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,    ->     TOTAL_EXTENTS,  EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE    ->     FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system' \G*************************** 1. row ***************************        FILE_ID: 0      FILE_NAME: ./ibdata1      FILE_TYPE: TABLESPACETABLESPACE_NAME: innodb_system   FREE_EXTENTS: 0  TOTAL_EXTENTS: 12    EXTENT_SIZE: 1048576   INITIAL_SIZE: 12582912   MAXIMUM_SIZE: NULLAUTOEXTEND_SIZE: 67108864      DATA_FREE: 4194304         ENGINE: NORMAL

This query retrieves theFILE_ID (equivalent to the space ID) and theFILE_NAME (which includes path information) forInnoDB file-per-table and general tablespaces. File-per-table and general tablespaces have a.ibd file extension.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES    ->     WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID;    +---------+---------------------------------------+    | FILE_ID | FILE_NAME                             |    +---------+---------------------------------------+    |       2 | ./mysql/plugin.ibd                    |    |       3 | ./mysql/servers.ibd                   |    |       4 | ./mysql/help_topic.ibd                |    |       5 | ./mysql/help_category.ibd             |    |       6 | ./mysql/help_relation.ibd             |    |       7 | ./mysql/help_keyword.ibd              |    |       8 | ./mysql/time_zone_name.ibd            |    |       9 | ./mysql/time_zone.ibd                 |    |      10 | ./mysql/time_zone_transition.ibd      |    |      11 | ./mysql/time_zone_transition_type.ibd |    |      12 | ./mysql/time_zone_leap_second.ibd     |    |      13 | ./mysql/innodb_table_stats.ibd        |    |      14 | ./mysql/innodb_index_stats.ibd        |    |      15 | ./mysql/slave_relay_log_info.ibd      |    |      16 | ./mysql/slave_master_info.ibd         |    |      17 | ./mysql/slave_worker_info.ibd         |    |      18 | ./mysql/gtid_executed.ibd             |    |      19 | ./mysql/server_cost.ibd               |    |      20 | ./mysql/engine_cost.ibd               |    |      21 | ./sys/sys_config.ibd                  |    |      23 | ./test/t1.ibd                         |    |      26 | /home/user/test/test/t2.ibd           |    +---------+---------------------------------------+

This query retrieves theFILE_ID andFILE_NAME for theInnoDB global temporary tablespace. Global temporary tablespace file names are prefixed byibtmp.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES       WHERE FILE_NAME LIKE '%ibtmp%';+---------+-----------+| FILE_ID | FILE_NAME |+---------+-----------+|      22 | ./ibtmp1  |+---------+-----------+

Similarly,InnoDB undo tablespace file names are prefixed byundo. The following query returns theFILE_ID andFILE_NAME forInnoDB undo tablespaces.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES       WHERE FILE_NAME LIKE '%undo%';