MySQL在哪里存储关于表属于哪个表空间的信息?

vfh0ocws  于 2023-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(159)

MySQL在哪里存储关于表属于哪个表空间的信息?
也就是说,如果我将city表设置为存储在ts2表空间中:

alter table city tablespace ts2;

显示表状态不说明此表属于哪个表空间:

mysql> show table status where name = 'city'\G;
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4150
 Avg_row_length: 98
    Data_length: 409600
Max_data_length: 0
   Index_length: 81920
      Data_free: 3145728
 Auto_increment: 4080
    Create_time: 2023-06-11 20:19:15
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

也不是information_schema.tables

mysql> select * from information_schema.tables where table_name = 'city'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: world
     TABLE_NAME: city
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4150
 AVG_ROW_LENGTH: 98
    DATA_LENGTH: 409600
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 81920
      DATA_FREE: 3145728
 AUTO_INCREMENT: 4080
    CREATE_TIME: 2023-06-11 20:19:15
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

我是否忘记了数据字典中的任何表?

wfsdck30

wfsdck301#

Tablespaces只适用于InnoDB表,所以这个属性在INFORMATION_SCHEMA.INNODB_TABLES中。

mysql> select name, space from INNODB_TABLES where name like 'test/%';
+-----------------+-------+
| name            | space |
+-----------------+-------+
| test/table1     |   453 |
| test/table2     |   454 |
...

参见https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-system-tables.html
从MySQL 8.0开始,InnoDB数据字典存储了关于表空间之类的元数据。不能直接查询数据字典,只能通过INFORMATION_SCHEMA间接查询。请参考我链接到的文档,以了解数据字典的其他I_S视图。

相关问题