mysql ERROR 1050 (42S01): Table already exists

怼烎@ 2022-06-15 12:14 688阅读 0赞

在数据库启动的时候不小心删了数据文件，然后一直操作这张表一直报错，查询报不存在，但重新建立一张一样的表名报已经存在。解决办法就是在其他的数据库中建一张同名的表，然后把它的数据文件拷贝过去。

mysql> show databases;

\+--------------------+  
| Database           |  
\+--------------------+  
| information\_schema |  
| mysql              |  
| performance\_schema |  
| test               |  
\+--------------------+  
mysql> use test  
mysql> create table t1(id int);  
ERROR 1813 (HY000): Tablespace for table '\`test\`.\`t1\`' exists. Please DISCARD the tablespace before IMPORT.  
mysql> alter table t1 DISCARD tablespace;  
ERROR 1146 (42S02): Table 'test.t1' doesn't exist  
  
mysql> create database gg;  
mysql> show databases;  
\+--------------------+  
| Database           |  
\+--------------------+  
| information\_schema |  
| gg                 |  
| mysql              |  
| performance\_schema |  
| test               |  
\+--------------------+  
mysql> use gg  
Database changed  
mysql> create table t1(id int);  
  
  
来到数据库gg下面  
**cp -a t1.frm ../test/t1.frm**  
  
mysql> select \* from t1;  
ERROR 1146 (42S02): Table 'test.t1' doesn't exist  
mysql> **alter table t1  discard tablespace;**  
Query OK, 0 rows affected, 2 warnings (0.01 sec)  
  
来到数据库gg下面

**cp -a t1.ibd ../test/t1.ibd**

mysql> use test  
mysql> select \* from t1;  
ERROR 1814 (HY000): Tablespace has been discarded for table 't1'  
mysql> **alter table t1  import tablespace; **  
Query OK, 0 rows affected, 1 warning (0.04 sec)  
mysql> select \* from t1;  
Empty set (0.00 sec)