Simple usage of MySQL temporary tables

MySQL temporary tables are very useful when we need to save some temporary data. The temporary table is only visible in the current connection. When the connection is closed, MySQL will automatically delete the table and release all space.

Temporary tables were added in MySQL version 3.23. If your MySQL version is lower than 3.23, you cannot use MySQL temporary tables. However, nowadays, few people use such a low version of MySQL database service.

MySQL temporary tables are only visible to the current connection. If you use a PHP script to create a MySQL temporary table, the temporary table will be automatically destroyed every time the PHP script is executed.

If you use other MySQL client programs to connect to the MySQL database server to create a temporary table, the temporary table will be destroyed only when you close the client program. Of course, you can also destroy it manually.

Examples

The following shows a simple example of using MySQL temporary tables. The following SQL code can be applied to the mysql_query() function of a PHP script.

mysql> CREATE TEMPORARY TABLE SalesSummary (
  -> product_name VARCHAR(50) NOT NULL
  -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
  -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
  -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
  -> (product_name, total_sales, avg_unit_price, total_units_sold)
  -> VALUES
  -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

When you use the SHOW TABLES command to display a list of tables, you will not be able to see the SalesSummary table.

If you exit the current MySQL session and then use the SELECT command to read the temporary table data that was originally created, you will find that the table does not exist in the database because the temporary table has been destroyed when you exit.

Deleting MySQL temporary tables

By default, temporary tables are automatically destroyed when you disconnect from the database. Of course, you can also use the DROP TABLE command in the current MySQL session to manually delete the temporary table.

The following is an example of manually deleting a temporary table:

mysql> CREATE TEMPORARY TABLE SalesSummary (
  -> product_name VARCHAR(50) NOT NULL
  -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
  -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
  -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
  -> (product_name, total_sales, avg_unit_price, total_units_sold)
  -> VALUES
  -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist

How to create a temporary table directly using query:

CREATE TEMPORARY TABLE temporary table name AS
(
  SELECT * FROM old table name LIMIT 0,10000
);

Here are some additional

When working with very large tables, you may occasionally need to run many queries to get a small subset of a large amount of data. Rather than running these queries against the entire table, it may be faster to let MySQL find the few records you need at a time, select the records into a temporary table, and then run the queries against those tables.

Creating a temporary table is easy. Add the TEMPORARY keyword to the normal CREATE TABLE statement:

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
 )

The temporary table will exist for the duration of your connection to MySQL. When you disconnect, MySQL will automatically drop the table and free up the space used. Of course you can drop the table and free up space while still connected.

DROP TABLE tmp_table

If a table named tmp_table already exists in the database when you create a temporary table named tmp_table, the temporary table will necessarily mask (hide) the non-temporary table tmp_table.

If you declare the temporary table to be a HEAP table, MySQL also allows you to specify that it be created in memory:

CREATE TEMPORARY TABLE tmp_table ( 
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
 ) TYPE = HEAP

Because HEAP tables are stored in memory, queries you run against them may be faster than against temporary tables on disk. However, HEAP tables are somewhat different from normal tables and have their own limitations. See the MySQL Reference Manual for details.

As suggested earlier, you should test temporary tables to see if they are actually faster than running queries against a large database. If the data is well indexed, the temporary table may not be faster at all.

1. After the temporary table is disconnected from MySQL, the system will automatically delete the data in the temporary table, but this is limited to the table created by the following statement:
Define the fields:

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
 )

2) Import the query results directly into a temporary table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. In addition, MySQL also allows you to create temporary tables directly in memory. Because it is in memory, the speed will be very fast. The syntax is as follows:

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
 ) TYPE = HEAP

3. From the above analysis, it can be seen that the data in the temporary table will be cleared. It will be automatically cleared when you disconnect. However, it is impossible for your program to connect to the database every time you issue a SQL statement (if this is the case, then there will be the problem you are worried about. If not, there will be no problem). Because the data will be cleared only when the database connection is disconnected. If you issue multiple SQL statements in a database connection, the system will not automatically clear the temporary table data.