英文:
Where does MySQL store the information about which tablespace a table belongs to?
问题
MySQL 存储表属于哪个表空间的信息在数据字典中，您所描述的情况没有显示表所属表空间的信息，您是否遗漏了数据字典中的某个表格？
英文:
Where does MySQL store the information about which tablespace a table belongs to?
Ie: if I set city table to be stored in ts2 tablespace:
alter table city tablespace ts2;
Show table status don't say anything about which tablespace this table belongs:
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)
Neither 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)
Am I forgetting any table in the data dictionary?
答案1
得分: 1
表空间仅适用于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视图，请参阅我提供的文档链接。
英文:
Tablespaces only applies to InnoDB tables, so this attribute is in INFORMATION_SCHEMA.INNODB_TABLES.
mysql> select name, space from INNODB_TABLES where name like 'test/%';
+-----------------+-------+
| name | space |
+-----------------+-------+
| test/table1 | 453 |
| test/table2 | 454 |
...
See also https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-system-tables.html
Since MySQL 8.0, the InnoDB data dictionary stores metadata about things like tablespaces. You can't query the data dictionary directly, you can only query it indirectly via the INFORMATION_SCHEMA. Refer to the documentation I linked to for other I_S views to the data dictionary.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论