Table Management - MySQL - page 132

• Create the table on the fly from a result set:
CREATE TEMPORARY TABLE tbl_name SELECT ... ;
Temporary tables are session-specific, so multiple clients can each 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. (For further discussion of table-naming issues, see
Recipe 4.4 .)
A temporary table can have the same name as a permanent table. In this case, the tem‐
porary 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 example removes rows from a tem‐
porary mail table, leaving the original permanent table 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 TEMPORARY TABLE mail;
mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
| 16 |
+----------+
Although temporary tables created with CREATE TEMPORARY TABLE have the benefits just
discussed, keep the following caveats in mind:
• To reuse a temporary table within a given session, you must still drop it explicitly
before re-creating it. Attempting to create a second temporary table with the same
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 use a program‐
ming interface that has reconnect capability enabled. If a client program automat‐
ically reconnects after detecting a dropped connection, modifications affect the
permanent table after the reconnect, not the temporary table.