Brief introduction to the usage of MySQL temporary table

MySQL temporary tables are very useful when we need to save some temporary data. Temporary tables are visible only on the current connection, and when the connection is closed, Mysql automatically deletes the table and frees up all space.

Temporary tables are added in MySQL 3.23. If your MySQL version is lower than 3.23, you cannot use MySQL's temporary tables. However, nowadays, it is rare to use such a low version of MySQL database service.

The MySQL temporary table is only visible in the current connection. If you use the PHP script to create an MySQL temporary table, the temporary table will be automatically destroyed every time the PHP script completes execution.

If you use another MySQL client program to connect to the MySQL database server to create a temporary table, the temporary table will only be destroyed when the client program is closed, or you can destroy it manually.

Instances

The following shows a simple example of using the MySQL temporary table, and the following SQL code can be applied to the mysql_query () function of the 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 data tables, you will not be able to see the SalesSummary table.

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

Delete MySQL temporary table

By default, when you disconnect from the database, the temporary table will be automatically destroyed. You can also use the DROP TABLE command to manually delete temporary tables during the current MySQL session.

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

The way to create temporary tables directly with queries:

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

Here are a few additions

When working on very large tables, you may occasionally need to run many queries to get a small subset of a large amount of data. Instead of running these queries on the whole table, let MySQL find a few required records at a time. It may be faster to select records to a temporary table and then run queries on these tables.

It's easy to create a temporary table by adding the TEMPORARY keyword to a normal CREATE TABLE statement:

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

The temporary table will exist during your connection to MySQL. When you disconnect, MySQL will automatically delete the table and free up the used space. Of course, you can delete the table and free up space while still connecting.

DROP TABLE tmp_table

If a table named tmp_table already exists in the database when you create a temporary table named tmp_table, it will be necessary for the temporary table to mask (hide) the non-temporary table tmp_table.

If you declare the temporary table to be an 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 the HEAP table is stored in memory, you may run a faster query against it than a temporary table on disk. However, the HEAP table is somewhat different from the 1-like table and has its own limitations. See MySQL reference manual for details.

As suggested earlier, you should test temporary tables to see if they are really faster than running queries against a large number of databases. If the data is well indexed, the temporary table may not be fast at 1 o'clock.

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 established with the following statement:
Define fields:

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

2) Importing query results directly into temporary tables

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 will be very fast in memory. 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 of the temporary table will be emptied. If you disconnect, it will be automatically emptied. However, it is impossible for you to connect to the database every time sql is issued in your program (if so, there will be problems you are worried about, if not, there will be no problems), because only by disconnecting the database connection will the data be emptied, and if sql is issued many times in a database connection, the system will not automatically empty the temporary table data.