SQL code example in MySQL temporary table

The following article describes how to use a temporary MySQL table. We all know that when running a large table, in practice, you may need to run a lot of related queries to obtain a small subset of a large amount of data, rather than running these queries on the entire table. Instead, let MySQL find the required few records each time and select the record

The following article describes how to use a temporary MySQL table. We all know that when running a large table, in practice, you may need to run a lot of related queries to obtain a small subset of a large amount of data, rather than running these queries on the entire table. Instead, let MySQL find the required few records each time and select the record

The following article describes how to use a temporary MySQL table. We all know that when running a large table, in practice, you may need to run a lot of related queries to obtain a small subset of a large amount of data, rather than running these queries on the entire table.

Instead, MySQL can find a few required records each time, and it may be faster to select a temporary table, and then run the query on these tables.

It is easy to CREATE a TEMPORARY MySQL TABLE, and the TEMPORARY keyword is added to the normal create table statement:

SQL code

 
 

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

Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL automatically deletes the table and releases the space used. Of course, you can delete the table and release the space when it is still connected.

 
 

  
  
DROP TABLE tmp_table 
 
 

If the table named tmp_table already exists in the Database when you create a temporary table named tmp_table, it is necessary to block (hide) the temporary table tmp_table.

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

SQL code

 
 

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

Because the HEAP table is stored in the memory, you may query it faster than the temporary table on the disk. However, HEAP tables are somewhat different from general tables and have their own restrictions. For more information, see the MySQL reference manual.

As previously suggested, you should test temporary tables to see if they are faster than running queries on a large number of databases. If the data is well indexed, the temporary table may be a little unpleasant.

1. After a temporary table is disconnected from MySQL, the system will automatically delete the data in the temporary MySQL table. However, this is only the table created using the following statement:

1) define Fields

 
 

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

2) directly import the query results to the temporary table

Create temporary table tmp_table SELECT * FROM table_name

2. In addition, MySQL allows you to directly create temporary tables in the memory, because all the speed in the memory will be fast, and 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, we can see that the data in the temporary MySQL table will be cleared, and the data will be automatically cleared when you disconnect the connection, however, it is impossible for your program to connect to the database every time an SQL statement is released. (if this is the case, there will be issues you are worried about. If not, there will be no problems ), because data is cleared only when the database connection is disconnected, the system will not automatically clear the temporary table data if multiple SQL statements are released in a database connection.

[Edit recommendations]