MySQL Tables – Handling Two Tables with the Same Name

MySQLtable

I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that:

MySQL allows to create a temporary table with the same name as a permanent table. So the temporary table got dropped and not the permanent table. I got really confused with which table I am working.

MySQL version: 5.1.36-enterprise-gpl-pro-log

This is what I'd tested:

mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

 mysql> create temporary table test(id int);
 Query OK, 0 rows affected (0.00 sec)



mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL


mysql> drop table test;
 Query OK, 0 rows affected (0.00 sec)

 mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

Is this a bug or is there an alternate way to overcome this?

Best Answer

MySQL allows you to create a temp table with a existing name because they don't have the same "scope". A temporary table is visible in the session only, and it is dropped at session ending. If you have the same name, MySQL "hide" the original table until you drop your temp table.

You can refer to the Temporary Tables section in the MySQL documentation

Max.

Related Question