The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.
What are Temporary Tables?
Temporary tables were added in the MySQL Version 3.23. If you use an older version of MySQL than 3.23, you cannot use the temporary tables, but you can use Heap Tables.
As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
Example
The following program is an example showing you the usage of the temporary table. The same code can be used in PHP scripts using the mysqli_query() function.
mysql> CREATE TEMPORARY TABLE `order_details_status` (
-> `id` INT(11) NOT NULL,
-> `status_name` VARCHAR(50) NOT NULL,
-> PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (1, 'Allocated');
Query OK, 1 row affected (0.04 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (2, 'Invoiced');
Query OK, 1 row affected (0.03 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (3, 'Shipped');
Query OK, 1 row affected (0.04 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (4, 'On Order');
Query OK, 1 row affected (0.07 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (5, 'No Stock');
Query OK, 1 row affected (0.04 sec)
mysql> select * from order_details_status;
+----+-------------+
| id | status_name |
+----+-------------+
| 1 | Allocated |
| 2 | Invoiced |
| 3 | Shipped |
| 4 | On Order |
| 5 | No Stock |
+----+-------------+
5 rows in set (0.00 sec))When you issue a SHOW TABLES command, then your temporary table would not be listed out in the list. Now, if you will log out of the MySQL session and then you will issue a SELECT command, then you will find no data available in the database. Even your temporary table will not exist.
Dropping Temporary Tables
By default, all the temporary tables are deleted by MySQL when your database connection gets terminated. Still if you want to delete them in between, then you do so by issuing the DROP TABLE command.
The following program is an example on dropping a temporary table −
mysql> CREATE TEMPORARY TABLE `order_details_status` (
-> `id` INT(11) NOT NULL,
-> `status_name` VARCHAR(50) NOT NULL,
-> PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (1, 'Allocated');
Query OK, 1 row affected (0.04 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (2, 'Invoiced');
Query OK, 1 row affected (0.03 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (3, 'Shipped');
Query OK, 1 row affected (0.04 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (4, 'On Order');
Query OK, 1 row affected (0.07 sec)
> INSERT INTO `order_details_status` (`id`, `status_name`) VALUES (5, 'No Stock');
Query OK, 1 row affected (0.04 sec)
mysql> select * from order_details_status;
+----+-------------+
| id | status_name |
+----+-------------+
| 1 | Allocated |
| 2 | Invoiced |
| 3 | Shipped |
| 4 | On Order |
| 5 | No Stock |
+----+-------------+
5 rows in set (0.00 sec))
mysql> DROP TABLE order_details_status;
mysql> SELECT * FROM order_details_status;
ERROR 1146 (42S02): Table 'mydb.order_details_status' doesn't exist