【声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

MySQL ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint错误解决

【MySQL】ERROR 1109 (42S02): Unknown table ‘INNODB_SYS_TABLES’ in information_schema错误原因和解决

错误消息

在MySQL中查询表的信息时候遇到如下错误：

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.35-0ubuntu0.22.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select
    ->     name as 'table_name',
    ->     space_type
    -> from
    ->     information_schema.INNODB_SYS_TABLES\G
ERROR 1109 (42S02): Unknown table 'INNODB_SYS_TABLES' in information_schema
mysql>

错误原因

在MySQL 8.0.3及更高版本中，一些过时的的InnoDB系统表相关的代码被移除，所以某些旧版本的系统表不再是一个有效的INFORMATION_SCHEMA表。
基于InnoDB系统表的INFORMATION_SCHEMA视图被内部数据字典表上的系统视图所取代，受影响的INFORMATION_SCHEMA视图也被重命名。

以下是InnoDB INFORMATION_SCHEMA视图的旧名称和新名称的对应关系：

旧名称	新名称
INNODB_SYS_COLUMNS	INNODB_COLUMNS
INNODB_SYS_DATAFILES	INNODB_DATAFILES
INNODB_SYS_FIELDS	INNODB_FIELDS
INNODB_SYS_FOREIGN	INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS	INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES	INNODB_INDEXES
INNODB_SYS_TABLES	INNODB_TABLES
INNODB_SYS_TABLESPACES	INNODB_TABLESPACES
INNODB_SYS_TABLESTATS	INNODB_TABLESTATS
INNODB_SYS_VIRTUAL	INNODB_VIRTUAL

参考：
Table 1.1 Renamed InnoDB Information Schema Views
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

解决方案

使用新的内部系统视图来访问InnoDB相关的元数据信息。例如，可以使用以下查询来获取表的信息：

mysql> select
    ->     name as 'table_name',
    ->     space_type
    -> from
    ->     information_schema.innodb_tables
    -> where
    ->     name like '%test%' \g
+--------------------------------------+------------+
| table_name                           | space_type |
+--------------------------------------+------------+
| testdb/sales_table                   | Single     |
| testdb/yourTableName                 | Single     |
| testdb/test                          | Single     |
| testdb/test_file_pertable            | System     |
| testdb/test_file_pertable_tablespace | Single     |
+--------------------------------------+------------+
5 rows in set (0.00 sec)

INFORMATION_SCHEMA中的表列表

可以使用SHOW TABLES获取INFORMATION_SCHEMA中的表列表。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS     |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMNS_EXTENSIONS                    |
| COLUMN_PRIVILEGES                     |
| COLUMN_STATISTICS                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CACHED_INDEXES                 |
| INNODB_CMP                            |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_CMP_RESET                      |
| INNODB_COLUMNS                        |
| INNODB_DATAFILES                      |
| INNODB_FIELDS                         |
| INNODB_FOREIGN                        |
| INNODB_FOREIGN_COLS                   |
| INNODB_FT_BEING_DELETED               |
| INNODB_FT_CONFIG                      |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_DELETED                     |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_INDEXES                        |
| INNODB_METRICS                        |
| INNODB_SESSION_TEMP_TABLESPACES       |
| INNODB_TABLES                         |
| INNODB_TABLESPACES                    |
| INNODB_TABLESPACES_BRIEF              |
| INNODB_TABLESTATS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_TRX                            |
| INNODB_VIRTUAL                        |
| KEYWORDS                              |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| RESOURCE_GROUPS                       |
| ROLE_COLUMN_GRANTS                    |
| ROLE_ROUTINE_GRANTS                   |
| ROLE_TABLE_GRANTS                     |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMATA_EXTENSIONS                   |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| ST_GEOMETRY_COLUMNS                   |
| ST_SPATIAL_REFERENCE_SYSTEMS          |
| ST_UNITS_OF_MEASURE                   |
| TABLES                                |
| TABLESPACES                           |
| TABLESPACES_EXTENSIONS                |
| TABLES_EXTENSIONS                     |
| TABLE_CONSTRAINTS                     |
| TABLE_CONSTRAINTS_EXTENSIONS          |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_ATTRIBUTES                       |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| VIEW_ROUTINE_USAGE                    |
| VIEW_TABLE_USAGE                      |
+---------------------------------------+
79 rows in set (0.00 sec)

参考

https://dev.mysql.com/doc/mysql-errors/8.0/en/
https://dev.mysql.com/doc/refman/8.0/en/error-handling.html