Two tables with same name in MySQL

Question

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?

score 6 · Accepted Answer · answered Jul 05 '14 at 08:01

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.

score 1 · Answer 2 · edited Jun 02 '19 at 09:46

From the manual (CREATE TABLE):

A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

That means after you create a temporary table with the same name as some existing table, you can not access the normal table by that name, but only the temporary one (during that session). If you think that opens space for an error, use a different name for the temporary table (e.g. use prefix temp_) - that's simple and does not leave space for confusion.

score 0 · Answer 3 · answered Jul 05 '14 at 09:02

You've answered this question in your first one. You don't "need to insert records in a permanent table...same name.... temporary table... same session" because you have control over the table names!

You simply ensure that the temporary table is given a prefix/suffix tmp_/_tmp or similar as this avoids any confusion (as referred to by @SpeedyGonsales).

If you're making this kind of mistake now, just think how easy it will be to make for somebody new to the system in a year's time! Your organisation (even if that's only you!) should have a naming convention, otherwise you get this sort of SNAFU.

Two tables with same name in MySQL

3 Answers3