Creating Temporary Tables
Problem
You need a table only for a short time, then you want it to disappear automatically.
Solution
Create a TEMPORARY table and let MySQL take care
of clobbering 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, 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 ...