MySQL表可见但查询提示不存在、information_schema查不到如何解决？

排查步骤 #

• 首先确认查询时是否指定了正确的数据库，执行SELECT DATABASE();查看当前会话默认库，如果不是smartex_develop，要么执行USE smartex_develop;切换库后再查询，要么直接执行带库名的查询语句SELECT * FROM smartex_develop.company;验证是否还报错。
• 检查表名大小写敏感配置，执行SHOW VARIABLES LIKE 'lower_case_table_names';：
  • 参数值为0时表名区分大小写，需确认company的实际大小写和查询语句拼写完全一致，SHOW TABLES返回的表名字母大小写不一定和数据字典存储的完全匹配，可尝试带上反引号匹配实际大小写查询验证。
  • 参数值为1或2时不区分大小写，可跳过此步。
• 刷新表缓存，执行FLUSH TABLES;后重新查询information_schema.TABLES，排查是否是缓存异常导致的元数据不一致。
• 检查表是否损坏，执行CHECK TABLE smartex_develop.company;，如果返回错误提示表文件损坏或元数据不匹配，就是核心问题所在：SHOW TABLES是直接扫描数据库目录下的表物理文件展示结果，而information_schema.TABLES需要读取完整的表元数据，SELECT操作也需要加载完整元数据和表空间，物理文件不完整、元数据损坏时就会出现你遇到的矛盾情况。
• 排查关联外键，执行SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA='smartex_develop' AND REFERENCED_TABLE_NAME='company';找到所有关联company表的外键，记录所属表和外键名称。

解决方法 #

• 如果是元数据轻微异常，MySQL 8.0及以上版本可执行ALTER TABLE smartex_develop.company FORCE;触发元数据重建，低版本可尝试先新建一张同结构的临时表，再重命名覆盖问题表。
• 如果是物理文件损坏，先备份现有company表的相关物理文件（.frm/.ibd后缀文件），如果是InnoDB引擎可通过表空间导入方式恢复：
  1. 新建一张和company结构完全一致的空表，命名为company
  2. 执行ALTER TABLE company DISCARD TABLESPACE;
  3. 将备份的.ibd文件替换到对应数据库目录下
  4. 执行ALTER TABLE company IMPORT TABLESPACE;
• 上述操作前先删除之前查到的所有关联外键，操作完成后再重新创建外键即可。
• 如果物理文件已经不可恢复，从最近的全量/增量备份中恢复company表即可。

内容的提问来源于stack exchange，提问作者Daniele Scalco