How to use MySQL temp table

When working on a very large table, you might occasionally need to run a lot of queries to get a small subset of large amounts of data, instead of running these queries against the entire table, instead of having MySQL find the few records needed each time, selecting the records to a temporary table might be faster and then running the query on those tables.

It is easy to create a temporary table and add the temporary keyword to the normal CREATE TABLE statement:

CREATE Temporary TABLE tmp_table (

Name VARCHAR (Ten) is not NULL,

Value INTEGER not NULL

)

Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL automatically deletes the table and frees up the space used. Of course you can delete 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 need to mask (hide) the non-temporal table tmp_table.

If you declare that the temp table is a heap, MySQL also allows you to specify that it is created in memory:

CREATE Temporary TABLE tmp_table (

Name VARCHAR (Ten) is not NULL,

Value INTEGER not NULL

) TYPE = HEAP

Because the heap table is stored in memory, the query you run against it may be faster than the temporary table on the disk. However, the heap table is somewhat different from the normal table and has its own limitations. See MySQL reference manual for details.

As suggested earlier, you should test the 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 temp table may be a little unpleasant.

1. After the temporary table is disconnected from the MySQL connection, the system automatically deletes the data from the staging table, but this is limited to the tables established with the following statement:

Define fields:

CREATE Temporary TABLE tmp_table (

Name VARCHAR (Ten) is not NULL,

Value INTEGER not NULL

)

2) Import the query results directly into the staging table

CREATE temporary TABLE tmp_table SELECT * FROM table_name

2. mysql also allows you to create temporary tables in memory directly, because it is in memory all the speed will be fast, the syntax is as follows:

CREATE Temporary TABLE tmp_table (

Name VARCHAR (Ten) is not NULL,

Value INTEGER not NULL

) TYPE = HEAP

3. From the above analysis can be seen that the temporary table data will be emptied, you disconnect the connection will be automatically emptied, but you do not have a program in the SQL to connect the database once every time (if so, then there will be a problem you worry, if not there is no problem), Because only the disconnected database connection will be emptied data, in a database connection issued multiple SQL, the system will not automatically empty the temporary table data.

How to use MySQL temp table