Provides you with an in-depth understanding of Mysql temporary tables.

Source: Internet
Author: User

The Mysql temporary table is used to identify a few required records at a specific time. The following describes how to use the Mysql temporary table in detail. If you are interested in the Mysql temporary table, take a look.

When you work on a very large table, you may occasionally need to run many queries to obtain a small subset of a large amount of data, instead of running these queries on the entire table, instead, it allows MySQL to find a few required records each time, and selecting a record to a Mysql temporary table may be faster, 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:

The Code is as follows:

 
 
  1. CREATE TEMPORARY TABLE tmp_table (  
  2. name VARCHAR(10) NOT NULL,  
  3. value INTEGER NOT NULL  
  4. )  

The Mysql temporary table 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 shield the temporary table from hiding it.) 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:

The Code is as follows:

 
 
  1. CREATE TEMPORARY TABLE tmp_table (  
  2. name VARCHAR(10) NOT NULL,  
  3. value INTEGER NOT NULL  
  4. ) 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.

MySQL Show statement usage

How to clear a table in Mysql

Seven Mysql table types

MySQL connection query Overview

Optimization instance for MySQL random Query

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.