Working with Tables

Temporary Tables

You can use CREATE TEMPORARY TABLE to create temporary tables that disappear automatically when your session ends. This is handy because you don't have to bother issuing a DROP TABLE statement explicitly to get rid of the table, and the table doesn't hang around if your session terminates abnormally.

For example, if you have a canned query in a batch file that you run with mysql and decide not to wait for it to finish, you can kill the script in the middle with impunity and the server will remove any temporary tables that the script creates.

A temporary table is visible only to the client that creates the table. The name can be the same as that of an existing permanent table. This is not an error, nor does the existing permanent table get clobbered.

Instead, the permanent table becomes hidden (inaccessible) while the temporary table exists. Suppose you create a temporary table in the sampdb database named member. The original member table becomes hidden, and references to member refer to the temporary table.

If you issue a DROP TABLE member statement, the temporary table is removed and the original member table "reappears." If you simply disconnect from the server without dropping the temporary table, the server automatically drops it for you.

The next time you connect, the original member table is visible again. (The original table also reappears if you rename a temporary table that hides it to have a different name. If the temporary table's new name happens to be that of another permanent table, that table becomes hidden while the temporary table has its name.)

The name-hiding mechanism works only to one level. That is, you cannot create two temporary tables with the same name. A TEMPORARY table can be created with a particular storage format by using a TYPE option. (Prior to MySQL 3.23.54, a MERGE table cannot be TEMPORARY.)

Prior to MySQL 3.23.2, TEMPORARY is unavailable, so there are no true temporary tables except in the sense that you consider them temporary in your own mind. You must remember to drop such a table yourself.

If you forget, the table hangs around until you notice and remove it. Table persistence also occurs if an application creates a table but exits early due to an error before it can drop the table.