How to create and use temporary tables in MySQL _ MySQL

This article mainly introduces how to create and use temporary tables in MySQL. pay attention to the data clearing problem in temporary tables. if you need to clear temporary tables, refer to when you are working on a very large table, you may occasionally need to run a lot of queries to obtain a small subset of a large amount of data. instead of running these queries on the entire table, you can ask MySQL to find a few records each time, selecting a record to a temporary table may be faster and then running the query on these 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(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 non-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:

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.

After the temporary table is disconnected from mysql, the system will automatically delete the data in the temporary table, but this is only the table created using the following statement:

Define fields:

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

Directly import query results to temporary tables

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

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

From the analysis above, we can see that the data in the temporary 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.

The TEMPORARY table is visible only during the current connection. When the connection is closed, the TEMPORARY table is automatically canceled. This means that two different connections can use the same temporary table name. at the same time, the two temporary tables do not conflict with each other and do not conflict with the original non-temporary table with the same name. (The original table is hidden until the temporary table is canceled .) You must have the create temporary tables permission to CREATE a TEMPORARY table. You can specify ENGINE | TYPE = MEMORY; to create a temporary MEMORY table.

IF the table already EXISTS, use the keyword if not exists to prevent errors. Note: it is not verified whether the structure of the original TABLE is the same as that of the TABLE in the create table statement. Note: IF you use if not exists in the create table... SELECT statement, the selected records are inserted no matter whether the table exists or NOT.

The drop temporary table statement only cancels the temporary table and does not terminate ongoing transactions. When the connection pool is used, you can use the create if not exists + truncate table method to improve performance to prevent performance bottlenecks caused by multiple CREATE and drop temporary tables.

Temporary tables support specifying primary keys and indexes. You can use the specified primary key or index to improve the performance of non-temporary table queries.

Create procedure sp_test_tt (IN I _chars VARCHAR (50), OUT o_counts BIGINT) BEGIN create temporary table if not exists tmpTable-CREATE temporary table (objChk varchar (255) primary key, modelName varchar (50), Operator varchar (500), PModelName varchar (50); truncate TABLE tmpTable; -- clear the temporary TABLE before use. Insert into tmpTable values (I _chars, I _chars); insert into tmpTable values (I _chars, I _chars); -- statement 1 select * from tmpTable; -- statement 2 select count (*) into o_counts from tmpTable; -- statement 3END;

The preceding code statement 1 returns all data in the temporary table, and statement 2 writes the total number of records to the output parameter. The truncate statement is placed after create, instead of the end of the entire stored procedure. The reason is that statement 1 inserts the same value, and the primary key verification of the two temporary tables produces an error, the stored procedure ends abnormally. For comprehensive exception handling, you can modify the following to clear the temporary table after each stored procedure call.
Let's look at an 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 2END;

Although the preceding code statement 2 finally truncate table clears all temporary table data, the data result set of the select statement 1 is not cleared. It has passed java program verification.

Temporary tables can solve the problem of two-dimensional array output. However, large volumes of data insertion can only be done in a loop by the program. In some special cases, the input array, for example, the input of a set of IDs of the data to be deleted, can only be done in a loop. Temporary tables are not applicable when a three-dimensional array is required.

The above is the basic creation and use tutorial _ MySQL for temporary tables in MySQL. For more information, see PHP Chinese network (www.php1.cn )!