MySQL Temporary Table

Summary: In this tutorial, we'll discuss about MySQL temporary table and show what you do to the create, use a ND drop temporary tables.

Introduction to MySQL temporary table

In MySQL, a temporary table was a special type of table that allows you to store a temporary result set, which can Reus e several times in a single session. A temporary table was very handy when it was impossible or expensive to query data, requires a single SELECT statement w ITH JOIN clauses. You often use temporary tables on stored procedures to store immediate result sets for the subsequent uses.

MySQL temporary tables has some additional features:

• A temporary table is created by using CREATE TEMPORARY TABLE statement. Notice The TEMPORARY  keyword is added between and CREATE  TABLE  keywords.
• MySQL drops the temporary table automatically when the session ends or connection is terminated. Of course, you can use the DROP TABLE statement to drop a temporary table explicitly if you were no longer use it.
• A temporary table is only available and accessible by the client creates the table.
• Different clients can create a temporary table with the same name without causing errors because only the client who creat Es a temporary table can see it. However, in the same session, the temporary tables cannot has the same name.
• A temporary table can has the same name as an existing table in a database. For example, if you create a temporary table namedemployees In the sample database, the existingemployeesTable becomes inaccessible. Every query you issue against theemployeesTable refers to theemployees Temporary table. When you remove theemployees Temporary table, the PermanentemployeesTable is available and accessible again. Though This is allowed however it isn't recommended to create a temporary table whose name is same as a name of a Permane NT table because it may leads to a confusion. For example, the connection to the MySQL database server are lost and you reconnect to the server automatically, yo U cannot differentiate between the temporary table and the permanent table. In the worst case, issue aDROP TABLEStatement to remove the permanent table instead of the temporary table, which are not expected.

Create MySQL Temporary table

Like the CREATE TABLE statement, MySQL provides many options to CREATE a temporary table. To create a temporary table, you just add the TEMPORARY  keyword to the CREATE TABLE statement.

For example, the following statement creates a top ten customers by revenue temporary table based on the result set of a statement:

CREATE temporary TABLE top10customersselect p.customernumber,       c.customername,       FORMAT (SUM (P.amount), 2) Totalfrom Payments PINNER JOIN customers c on c.customernumber = P.customernumbergroup by P.customernumberorder by Total D Esclimit 10

　　

Now, your can query data from the top10customers temporary table as from a permanent table:

1	SELECT * from top10customers

Drop MySQL Temporary table

You can use the DROP TABLE statement to remove temporary tables However it's good practice to use the DROP TEMPORARY TABLE statement ins Tead. Because DROP TEMPORARY TABLE the removes only temporary tables and not the permanent tables. In addition, the DROP TEMPORARY TABLE statement helps your avoid the mistake of removing a permanent table when you name your temporary t Able the same as the name of the permanent table.

For example, to remove the top10customers  temporary table, you use the following statement:

1	DROP Temporary TABLE top10customers

Notice that if you try to remove a permanent table DROP TEMPORARY TABLE with the statement and you'll get a error message saying that Table trying drop is unknown.

Note If you develop an application that uses a connection pooling or persistent connections, it's not guaranteed that the Temporary tables was removed automatically when your application was terminated. Because The database connection that the application used could be still open and are placed in a connection pool for other C Lients to reuse it. This means your should always remove the temporary tables so you created whenever you are doing with them.

In this tutorial, you had learned about MySQL temporary table and its characteristic. We also gave a example of how to create, use and drop a temporary table.

MySQL Temporary Table