MySQL Temporary Table

Mysql has a particular type of table called the temporary table, which is not permanently created as the rest of the table, which happens in MySQL.

This one stores a temporary result set which can be used several times again in a single session.

Syntax

For Creation:

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

For Dropping:

DROP TEMPORARY TABLE table_name;

A temporary table comes in very handy when we need to create a complex table or an expensive query table with various joins.

Enough talking. Let us create and drop some temporary tables with real-time examples.

MySQL Create Temporary Table

CREATE TEMPORARY TABLE temp_actor
SELECT * FROM actor
LIMIT 0;

This creates the table column structure temporary table based on the actor table.

Insert into Temporary Table MySQL

INSERT INTO temp_actor(first_name,last_name)
SELECT first_name, last_name
FROM actor;

This inserts the table constraints into the temp_actor table.

Drop Temporary Table MySQL 

drop temporary table temp_actor;

This deletes the temporary table which is created.

Conclusion

So this is how we can use the temporary table in MySQL effectively.