MySQL – Temporary Table

It is possible to store temporary data in MySQL by creating a special table called a Temporary Table. This table can be reused several times within a single session. If we use an older version of MySQL, this table is not available. It is available in MySQL from version 3.23 and above. Only the current session has access to this table. Whenever the current session is closed or the user terminates the connection, MySQL automatically deletes this table. The DROP TABLE command can also be used to remove this table explicitly when the user will not be using it.

When the PHP script is used to run the code, this table is automatically removed once it has completed its execution. If the user is connected to the server using the MySQL client, then this table will remain until the user closes the MySQL client program, terminates the connection, or manually removes the table from the server.

With temporary tables, we can perform complex tasks quickly, such as querying data that requires a single SELECT statement with JOIN clauses. In this case, the user may use this table to store the output and then run another query to process it.

In MySQL, temporary tables have a number of features, which are outlined below:

• The CREATE TEMPORARY TABLE statement is used by MySQL to create temporary tables.
• It is only possible to use this statement if the MySQL server has the privilege of CREATE TEMPORARY TABLES.
• A temporary table with the same name can be visible and accessible to the client who created it, which means that two clients can use the temporary table without conflicting with each other. This is due to the fact that this table can only be accessed by the client who created it. Therefore, a user cannot create two temporary tables with the same name at the same time.
• In MySQL, a temporary table is automatically dropped when the user closes the session or terminates the connection manually.
• The user can create a temporary table with the same name as a normal table in a database. As an example, if a user creates a temporary table with the name student, the existing student table will not be accessible. Therefore, the user will now refer to the temporary student table when performing any query against the student table. After removing a temporary table, the permanent student table becomes accessible once again.

Syntax

mysql> CREATE TEMPORARY TABLE table_name (  
   column_1, column_2, ..., table_constraints  
);  

Example

CREATE TEMPORARY TABLE emp_details( emp_name VARCHAR(40) NOT NULL,
total_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
total_roles INT UNSIGNED NOT NULL DEFAULT 0);

The next step is to insert values into the temporary table:

INSERT INTO emp_details(emp_name, total_salary, total_roles) VALUES
('Joseph', 150000.75, 2), ('Peter', 180000.75, 2);

To obtain the results, run the following query:

SELECT * from emp_details;