Home>

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 entire table,Instead, let mysql find out the few records needed each time,It may be faster to select records into a temporary table,Then run queries on these tables.

Creating temporary tables 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 during your connection to mysql.When you disconnectMySQL will automatically delete the table and free up the space used.Of course you can delete tables 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,It will be necessary for temporary tables to 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 the heap table is stored in memory,The query you run on it may be faster than a temporary table on disk.However, the heap table is slightly different from the general table,And has its own limitations.See the 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,Temporary tables can be a little unpleasant.

After the temporary table is disconnected from mysql, the system will automatically delete the data in the temporary table.But this is limited to tables created with:

Define the fields:

create temporary table tmp_table (
 name varchar (10) not null, value integer not null
)

Import query results directly into a temporary table

create temporary table tmp_table select * from table_name

In addition, mysql also allows you to create temporary tables directly in memory,Because it's all fast in memory,The syntax is as follows:

create temporary table tmp_table (
 name varchar (10) not null, value integer not null
) type=heap

From the above analysis, we can see that the data of the temporary table will be cleared.When you disconnect, it will be automatically emptied,But it is impossible for your program to connect to the database every time SQL is issued (if this is the case,Then the problem you worry about will arise,If not, there is no problem), because the data will only be emptied if the database connection is disconnected,The system will not automatically clear the temporary table data if you issue multiple sqls in a database connection.

Only with the current connection, The temporary table is visible.When the connection is closed, The temporary table is automatically cancelled.This means that two different joins can use the same temporary table name,At the same time two temporary tables will not conflict with each other,Nor does it conflict with the original non-temporary table of the same name.(The original table is hidden,Until the temporary table is cancelled.) Must have create temporary tables permission to create temporary tables.You can specify the creation of a memory temporary table by specifying engine | type=memory ;.

If the table already exists,Then use the keyword if not exists to prevent errors.Note that the structure of the original table is the same as the structure of the table represented in the create table statement.This is not verified.Note:If you use if not exists in a create table ... select statement, regardless of whether the table already exists,All records selected by the select section are inserted.

The drop temporary table statement only cancels the temporary table;the statement does not terminate transactions in progress.With connection pooling,To prevent performance bottlenecks caused by multiple create and drop temporary tables,You can use create if not exists + truncate table to improve performance.

Temporary tables support primary key and index designation.Queries on joined non-temporary tables can use the specified primary key or index to improve performance.

create procedure sp_test_tt (in i_chars varchar (50), out o_counts bigint)
begin
     create temporary table if not exists tmptable-
     (
      objchk varchar (255) primary key,      modelname varchar (50),      operator varchar (500),      pmodelname varchar (50)
     );
     truncate table tmptable;-Empty the temporary table before use.
     insert into tmptable values ​​(i_chars, i_chars, i_chars, i_chars);
     insert into tmptable values ​​(i_chars, i_chars, i_chars, i_chars);-statement 1
     select * from tmptable;-statement 2
     select count (*) into o_counts from tmptable;-statement 3
end;

The above code statement 1 returns all data in the temporary table,Statement 2 writes the total number of records to the output parameter. The truncate statement is placed after create, not at the end of the entire stored procedure.The reason is that the subsequent statement 1 inserts the same value,Temporary table pk check will produce an error,The stored procedure eventually ends abnormally.Comprehensive exception handling,It can be modified as follows,To clear the temporary table after each stored procedure call.

Let's look at another example:

create procedure sp_test_tt (in i_chars varchar (50), out o_counts bigint)
begin
     create temporary table if not exists tmptable
     (
      objchk varchar (255) primary key,      modelname varchar (50),      operator varchar (500),      pmodelname varchar (50)
     ) engine=memory;
     begin
          declare exit handler for sqlwarning, not found, sqlexception set o_counts=-1;
          insert into tmptable values ​​(i_chars, i_chars, i_chars, i_chars);
          select * from tmptable;-statement 1
          select count (*) into o_counts from tmptable;
     end;
     truncate table tmptable;-statement 2
end;

Although the above code statement 2 finally truncate table cleared all temporary table data,However, the data result set of the select in the previous statement 1 will not be cleared.Verified by java program.

The temporary table can solve the problem of two-dimensional array output.However, high-volume data insertion can only be done by programs using loops.Input array in some special cases,For example, the input of the id of a good set of data to be deleted can only be done using a loop.Temporary tables are also not suitable when a three-dimensional array is required.

  • Previous Installation and configuration tutorial of MySQL performance monitoring software Nagios
  • Next Tutorial on using MySQL to back up MySQL data