Write an Article?
Ask Question

MySQL Temporary Tables: Create, Insert and Drop

Previous Page
Next Page

Temporary tables are tables stored for a session. Once the session is dropped i.e. connection is broken, the temporary table is also dropped.

  • Temporary Tables are available only to the user creating the table. Hence multiple users can create a temporary table with the same name.
  • We can have a temporary table with the same name as a normal/base table. In such a case, the temporary table takes precedence, and the effect is as if there is no base table.

This is not recommended as this may cause potential data loss.

Create a temporary table in MySQL

We can create a temporary table using the CREATE TEMPORARY TABLE. The Basic Syntax is as follows:

CREATE TEMPORARY TABLE <table-name>(
<column-name> <datatype> <constraints>
…..
);

Example:

CREATE TEMPORARY TABLE RATING_DESC(
RATING_ID INT,
DESCRIPTION VARCHAR(50)
);

'Insert into' temporary table in MySQL

Insertion in a Temporary table is exactly like a normal table

Example:

insert into rating_desc
values(0,'Poor'),
(1,'Needs Improvement');

Create a temporary table using a Query in MySQL

We can also create a temporary table using a Query just like a normal table. To do so the Syntaxi s,

CREATE TEMPORARY TABLE <table-name>
<Query>

Example:

Create TEMPORARY TABLE MAX_SALARY
SELECT E.FIRST_NAME,E.LAST_NAME,TEMP.SALARY,ED.DEPT_ID,D.DEPT_NAME FROM
      (SELECT MAX(SALARY) AS 'SALARY', DEPT_ID FROM EMPLOYEES E, EMPLOYEE_DEPT ED WHERE E.EMP_ID = ED.EMP_ID GROUP BY ED.DEPT_ID) AS TEMP
      INNER JOIN EMPLOYEE_DEPT ED ON TEMP.DEPT_ID = ED.DEPT_ID
      INNER JOIN EMPLOYEES E ON E.EMP_ID = ED.EMP_ID AND TEMP.SALARY = E.SALARY
      INNER JOIN DEPARTMENTS D ON D.DEPT_ID = ED.DEPT_ID;

Show temporary table in MySQL

Temporary Tables are hidden in Show Table, Show Full Tables, and even in the INFORMATION_SCHEMA.TABLES table. This is not an easy way of checking the Temporary table. However, we can get the metadata of the tables by using the following Query

SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;

The number of columns (N_COLS) is 3 more than the number of columns in the temporary table because InnoDB always creates three hidden table columns (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR) for each table.

Drop a temporary table in MySQL

To drop a table we use the drop Temporary table syntax,

DROP TEMPORARY TABLE <table-name> ;

Example:

DROP TEMPORARY TABLE MAX_SALARY;

Previous Page
Next Page