MySQL temporary tables

[Frank_Rizzo]

I understand temporary tables are dropped automatically. But are the tables which are created unique? i.e. should I create a different name for the table or does MySQL sort this out?

CREATE TEMPORARY table TEMP1 TYPE=HEAP select * from CUSTOMERS
.
.
PHP database report stuff here....
.
.
DROP TEMPORARY table TEMP1

This creates a temporary table called TEMP1 and is populated from CUSTOMERS.

1. What happens if 2 users run the code at near the same time? Will the TEMP1 table be corrupted?

2. What happens if 1 user fires up 2 browser windows and runs the script at near the same time?

3. Is it really necessary to DROP the table at the end of the code. MySQL says that temporary tables are automatically dropped.

I could create a random name rather than TEMP1 that should sort it?

Note that the TYPE=HEAP doesn't really matter here. The same problem should exist if HEAP or not: does creating a temporary table create TEMP1 in seperate user space?

[crypto]

Frank,

This is what I got from MySql docs:

"A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.)"

So this means you can safely use any name for your temporary table.