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:
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 issues.)
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
statements removes rows 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:
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.)
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 you’re using a programming interface that has reconnect capability enabled. If a client program automatically reconnects after it detects a dropped connection, you’ll be modifying the permanent table after the reconnect, not the temporary table.
Some APIs support persistent connections or connection pools. Use of these prevents temporary tables from being dropped as you expect when your script ends because the connection remains open for reuse by other scripts. Your script has no control over when the connection closes. 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 TEMPORARY TABLE IF EXISTStbl_name
The TEMPORARY
keyword is
useful here if the temporary table has already been dropped. It
prevents the statement from dropping any permanent table that
happens to have the same name.
3.15.186.79