• notice
  • Congratulations on the launch of the Sought Tech site

Analyze MySQL temporary table and characteristics

The temporary table exists when the connection is not disconnected.Once disconnected, it will not exist.The data and structure of the temporary table are in memory.You can do a test.You create a temporary table, but go to the data directory of the response.The.frm file will not be found under
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 is no different from the normal table, but the data is stored in the memory, and you cannot find the data file in the corresponding data directory.
Once you disconnect, check the table again and it will disappear.

Somewhat similar to temporary tables are memory tables, and some are also called heap tables.
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 find a mem_table.frm file in the data directory, so the data of the memory table is placed on the disk, but after inserting the data, it is found that there is no data file on the disk, so the data is in the memory, because it is used It is a memory engine.Once the machine is down, the data will not exist.The table because the data is in memory, so the data will be very fast.The disadvantage is safety.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+