MySQL Cookbook

3.25 Creating Temporary Tables

3.25.1 Problem

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

3.25.2 Solution

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

3.25.3 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, available in MySQL 3.23.2 and up, 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 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 Recipe 3.27 for further discussion of this issue.)

Another property of temporary tables is that they can be created with the same name as a permanent table. In this case, the temporary table "hides" the permanent table for the duration of its existence, which can be useful for making a copy of a table that you can modify without affecting the original by mistake. The DELETE statement in the following set of queries removes records from a temporary mail table, leaving the original permanent one unaffected:

mysql> CREATE TEMPORARY TABLE mail SELECT * FROM mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
mysql> DELETE FROM mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
mysql> DROP TABLE mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+

Although temporary tables created with CREATE TEMPORARY TABLE have the preceding benefits, keep the following caveats in mind:

• If you want to reuse the temporary table within a given session, you'll still need to drop it explicitly before recreating it. It's only the last use within a session that you need no explicit DROP TABLE for. (If you've already created a temporary table with a given name, attempting to create a second one with that name results in an error.)

• Some APIs support persistent connections in a web environment. Use of these prevents temporary tables from being dropped as you expect when your script ends, because the web server keeps the connection open for reuse by other scripts. (The server may close the connection eventually, but you have no control over when that happens.) This means it can be prudent to issue the following statement prior to creating a temporary table, just in case it's still hanging around from the previous execution of the script:

  DROP TABLE IF EXISTS tbl_name

• If you modify a temporary table that "hides" a permanent table with the same name, be sure to test for errors resulting from dropped connections. If a client program automatically reconnects after a dropped connection, you'll be modifying the original table after the reconnect.