mysql 42s01_mysql ERROR 1050 (42S01): Table already exists

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

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)