MySQL – Beginning Temporary Tables in MySQL

MySQL supports Temporary tables to store the resultsets temporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.

Let us create the temporary table named Temp

CREATE TEMPORARY TABLE TEMP
(id INT);


Now you can find out the column names using DESC command

DESC TEMP;

The above returns the following result

This table can be accessed only for the current connection and it can be used like a permanent table and automatically dropped when the connection is closed. However, you can not find temporary tables using INFORMATION_SCHEMA. TABLES system view. It will only list out the permanent tables.

MySQL usually stores the data of temporary tables in memory and processed by Memory Storage engine. But if the data size is too large MySQL automatically converts this to the on – disk table and use MyISAM engine.

You can also create a permanent table with the same name of a temporary table in the same connection. However the structure of permanent table is visible only if the temporary table with the same name is dropped.

Let us create a permanent table with the same name Temp as below

CREATE TABLE TEMP
(id INT,
names VARCHAR(100));

Now running the following command stills gives you the structure of the temporary table temp created earlier.

DESC TEMP;

You can drop the temporary table using DROP TEMPORARY TABLE command;

DROP TEMPORARY TABLE TEMP;

After you executed the temporary table, run the following command

DESC TEMP;

Now you will see the structure of the permanent table named temp

In summary – If there is a Temporary Table in MySQL it gets first priority over the permanent table in the session.

Reference: Pinal Dave (https://blog.sqlauthority.com)