Mysql temporary table and memory table creation, query, deletion, and precautions

Create, query, delete, and note: the temporary table is different from the ENGINE of the MEMORY table. The default value of the temporary table is MyISAM, and the MEMORY table is MEMORY. The temporary table is only visible to the current session, the connection is automatically deleted when it is disconnected!

Mysql tutorial temporary table and memory table creation, query, deletion, and precautions
The engine of the temporary table and memory table is different. The default value of the temporary table is myisam, while the memory table is memory. The temporary table is only visible to the current session. When the connection is disconnected, it is automatically deleted!
You don't have to worry that the name of the created temporary table will conflict with the temporary table or non-temporary table created in other sessions! NOTE: If your temporary table has the same name as the normal table, the normal table will be hidden, just like global variables and local variables.
Creating a temporary table does not cause a normal commit transaction commit.

Temporary table

Create temporary table tmp_table

Create a temporary table

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

Import data to a temporary table

Create temporary table tmp_table select * from table_name

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 while the table is still connected.

You can also use statements to delete

Drop table tmp_table

The engine type can only be memory (heap), myisam, merge, and innodb.
Mysql cluster is not supported.
The same query statement can only be referenced once! For example, select * from tp_table, tp_table as alias_name; is incorrect.

The same user storage function can only be referenced once!
Show tables does not display temporary tables
Temporary tables cannot be renamed using rename. Only alter table old_tp_table_name rename new_tp_table_name can be used;
Impact on the use of replication

Instance

Memory table

Mysql> create table tmp2 (id int not null) type = heap;

Instance

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

So what about speed (the difference between myisam and memory )?
Lab started:
Implementation Method: Perform olap segmentation on two tens of millions of tables. Two different methods are used to create intermediate tables. Finally, the data in the intermediate table is taken out as required and inserted into the result table.
Objective: To test the speed of a temporary memory table and a temporary table
1. Use create temporary table type = heap to create an intermediate table as a temporary memory table.
2. Use create temporary table to create an intermediate table.

Experiment results:
Temporary memory table: 1 hour
1 11:03:48
1 12:03:39
Temporary table: 1 hour and 17 minutes
2 12:25:28
2 13:42:37

It is found that memory is about 20% faster than myisam.

The mysql server will automatically create an internal temporary table: This temporary table can be a temporary memory table that only exists in the memory, or a temporary myisam table that is stored in the hard disk; in addition, the initial memory temporary table may be changed to a myisam temporary table because of the increase of the table. The conversion critical point is determined by the small values of the max_heap_table_size and tmp_table_size system variables.