Home / Database Structure / Tables / Question No: 205

Each storage engine in MySQL implements tables with a particular set of characteristics. One characteristic held in common by all storage engines is that by default they create tables that exist until they are removed with DROP TABLE. This behavior may be changed by using CREATE TEMPORARY TABLE rather than CREATE TABLE.

How to create a temporary table, take an example:

CREATE TEMPORARY TABLE tbl_name
(
    id         INT NOT NULL,
    first_name CHAR(30) NOT NULL,
    last_name  CHAR(30) NOT NULL,
    dob        DATE NOT NULL
);

A TEMPORARY table differs from a non- TEMPORARY table in the following ways:

• It's visible only to the client that created it and may be used only by that client. This means that different clients can create TEMPORARY tables that have the same name and no conflict occurs.
• A TEMPORARY table exists only for the duration of the connection in which it was created. The server drops a TEMPORARY table automatically when the client connection ends if the client has not already dropped it. This is convenient because you need not remember to remove the table yourself.
• A TEMPORARY table may have the same name as a non-TEMPORARY table. The non- TEMPORARY table becomes hidden to the client that created the TEMPORARY table as long as the TEMPORARY table exists.
• A TEMPORARY table can be renamed only with ALTER TABLE. You cannot use RENAME TABLE.

A table created with TEMPORARY is not the same thing as a MEMORY table. A MEMORY table is temporary in the sense that its contents are lost if you restart the server, but the table definition continues to exist in its database. A TEMPORARY table exists only while the client that created it remains connected, and then disappears completely. Given that a server restart necessarily involves termination of all client connections, it also results in removal of all TEMPORARY tables. Another difference is that a MEMORY table is available to any client that has permission to access it, not just to the client that created it.