MySQL Cookbook, 2nd Edition

Creating Temporary Tables

Problem

You need a table only for a short time, and then you want it to disappear automatically.

Solution

Create a TEMPORARY table, and let MySQL take care of removing it.

Discussion

Some operations require a table that exists only temporarily and that should disappear when it’s no longer needed. You can of course issue a DROP TABLE statement explicitly to remove a table when you’re done with it. Another option is to use CREATE TEMPORARY TABLE. This statement is just like CREATE TABLE except that it creates a transient table that disappears when your connection to the server closes, if you haven’t already removed it yourself. This is extremely useful behavior because you need not remember to remove the table. MySQL drops it for you automatically. TEMPORARY can be used with the usual table-creation methods:

• Create the table from explicit column definitions:

  CREATE TEMPORARY TABLEtbl_name (...column definitions...);

• Create the table from an existing table:

  CREATE TEMPORARY TABLEnew_table LIKE original_table;

• Create the table on the fly from a result set:

  CREATE TEMPORARY TABLEtbl_name SELECT ... ;

Temporary tables are connection-specific, so several clients each can create a temporary table having the same name without interfering with each other. This makes it easier to write applications that use transient tables, because you need not ensure that the tables have unique names for each client. (See Generating Unique Table Names for further discussion of table-naming ...