Parse Mysql temporary tables and their features

  • 2020-05-24 06:22:15
  • OfStack

The temporary table exists when the connection is not broken, and the data and structure of the temporary table are in memory. You can take a test. You can create a temporary table, but you will not find the.frm file in the data directory of the response
mysql > CREATE TEMPORARY TABLE tmp_table (
- >
- > name VARCHAR(10) NOT NULL,
- > value INTEGER NOT NULL
- >
- > ) ;
Query OK, 0 rows affected (0.38 sec)

View table status
mysql > show CREATE TABLE tmp_table \G
*************************** 1. row ***************************
Table: tmp_table
Create Table: CREATE TEMPORARY TABLE `tmp_table` (
`name` varchar(10) NOT NULL,
`value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

It's no different from a normal table, but the data is in memory and you can't find the data file in the responding data directory.
Once you are disconnected, the table will disappear.

Something similar to a temporary table is an in-memory table, sometimes called a heap table.
mysql > CREATE TABLE mem_table (
- >
- > name VARCHAR(10) NOT NULL,
- > value INTEGER NOT NULL
- >
- > ) TYPE = HEAP;
Query OK, 0 rows affected, 1 warning (0.01 sec)

View table status
mysql > show CREATE TABLE mem_table \G
*************************** 1. row ***************************
Table: mem_table
Create Table: CREATE TEMPORARY TABLE `mem_table` (
`name` varchar(10) NOT NULL,
`value` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

You will find 1 mem_table.frm file under the data directory, so the data of the memory table is put on the disk, but after inserting the data, there is no data file on the disk, so the data is in memory, because the memory engine is used. 1 denier down machine data will not exist. The table is fast because the data is in memory. The downside is security.


Related articles: