MySQL Temp table creation

Unlike SQL SERVER creating temporary tables, you cannot write directly to the CREATE TABLE #Test_Table

Instead, you need to add the temporary (temporary< temporary >) keyword between Create and table

CREATE Temporary TABLE test_table (

NAME VARCHAR (Ten) is not NULL,

Age 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 test_table

It is best not to show duplicates in existing data tables when creating temporary tables

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

CREATE Temporary TABLE test_table (

NAME VARCHAR (Ten) is not NULL,

Age INTEGER Not NULL

) TYPE = HEAP

Because the heap table is stored in memory, the query may run faster than the temporary table on the disk.

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.

Go

MySQL Temp table creation