MySQL – Temporary Tables in Stored Procedures

MySQL Temporary Tables have the same functionality as standard disk-based tables except they exist in memory. Since memory is not long term storage, they are temporary tables, hence the name.
Temporary Tables, or temp tables for short, allow you to create a short-term storage place within the database for a set of data that you need to use several times in a single series of operations. Temp tables come into play when it isn’t possible to retrieve all the data that you require using one SELECT statement or when you want to work with subsets of the same, larger resultset over several successive operations. Another┬ávery common use of temp tables is to combine data from different sources together to produce a single result set.┬áTemp tables are supported in MySQL 3.23 and later.
To create one, all you need to do is include the TEMPORARY keyword in a table creation statement:

1- Create Temporary Table – Only Definition

2- Create Temporary Table – With Data

There is another way to create a temporary table which includes selecting data from another table and puting it inside the temporary table while creating it:

Once the temporary table is created, you can now use this table in a query like so:

or so:

Temporary tables exist only for the current connection, they are not accessible from other connections. A temporary table is destroyed once the connection using it is terminated.

To add to the lines above, I recommend you check for the existance of a temporary table before creating it, and drop the temporary table explicitly once you are done with it. See below:


Hope this helps.
Stop the hunger,

Leave a Reply

Your email address will not be published. Required fields are marked *