Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use temporary tables in MySQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

It's easy to create temporary tables, adding the TEMPORARY keyword to normal CREATE TABLE statements:

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 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 called tmp_table, it will be necessary to mask (hide) the non-temporary table tmp_table.

If you declare that the temporary table is a HEAP table, MySQL also allows you to specify that it is 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 queries against it faster than temporary tables on disk. However, the HEAP table is somewhat 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, the temporary table may not be at all fast.

1. After the temporary table is disconnected from mysql, the system automatically deletes the data in the temporary table, but this is limited to tables created with the following statement:

Define fields:

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

2) Import 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 is 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, we can see that the data of the temporary table will be emptied, and if you disconnect, it will be emptied automatically, but it is impossible for your program to connect to the database every time sql is issued (if so, there will be problems you are worried about, if not, there will be no problem), because only disconnecting the database will empty the data. If you issue sql multiple times in a database connection, the system will not automatically empty the temporary table data.

The above is the editor for you to share how to use temporary tables in MySQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.