How to create and drop Temporary table in MySQL - MySQL Developer Tutorial

How to create and drop temporary table in MySQL


Temporary table are special type of tables which are present only for the session in which they are created. Once the session is closed , the temporary tables dropped automatically.

As developers we often create the temporary tables to store the results so we can use in different parts of programs. Sometime you create them so you can perform different calculations and finally save the results in some permanent table.

Below are few of the things you need to remember working with Temporary tables in MySQL.


  1. To create Temporary table, you will say "create Temporary table TableName". 
  2. To drop the temporary table, you can use drop temporary table TableName. If you don't drop the temporary table but end the session, the table is dropped anyways.
  3. Different users or in different sessions you can have the temporary tables with same name.
  4. The temporary table can be created with the same name the permanent table exists. In case you have created the temporary table with the same permanent table name, anything you will do, you will be using the temporary table.
  5. I will not suggest to use the same name like permanent table, that can cause confusion.
  6. If you will drop the table , in case temporary and permanent table have the same name, it will drop the temporary table.

Let's say if we would like to create temporary table with name mytable in MySQL. you can use below syntax.


create temporary table mytable(id int, name varchar(100));


                                                 
To drop temporary table, you can use "drop temporary table TableName".





2 comments: