Temporary tables in Mysql databases

Temp tables can be very useful for holding temporary data. Temporary tables option is available in MySQL versions 3.23 and above.

The temporary table is automatically dropped when the session ends or the connection is closed. The user can also manually drop temporary tables.

The same temp table name can be used in many connections simultaneously, because the temp table is available and accessible only to the client that created it.

Creating a temp table:

In standard way:

CREATE TEMPORARY TABLE tempTable1( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL, PRIMARY KEY ( id ) );

With data in a table with Select:

CREATE TEMPORARY TABLE tempTable1 SELECT ColumnName1,ColumnName2,... FROM table1;

You can add indexes when creating the temp table:

CREATE TEMPORARY TABLE tempTable1
( PRIMARY KEY(ColumnName2) )
SELECT ColumnName1,ColumnName2,... FROM table1;

The IF NOT EXISTS statement can be used with the create table command to prevent the ‘table already exists’ error. However, if another table with the same name exists, the table will not be created in this session.

CREATE TEMPORARY TABLE IF NOT EXISTS tempTable1
SELECT ColumnName1,ColumnName2,... FROM table1;

Deleting temp tables:

The “DROP TEMPORARY TABLE” command is used to delete the temp table you created in your current session.

DROP TEMPORARY TABLE tempTable1;

DROP TEMPORARY TABLE IF EXISTS tempTable1;