Mysql memory table and temporary table

The memory table is a table in the memory. The size of the memory used can be calculated by using My. specify max_heap_table_size in cnf, for example, max_heap_table_size1024M. The memory table is different from the temporary table. The temporary table is also stored in memory. The maximum memory required for the temporary table must be set through tmp_table_size128M. When the data exceeds the maximum value of the temporary table

The memory table is a table in the memory. The size of the memory used can be calculated by using My. specify max_heap_table_size in cnf. For example, max_heap_table_size = 1024 M. The memory table is different from the temporary table. The temporary table is also stored in the memory. The maximum memory required for the temporary table must be set by tmp_table_size = M. When the data exceeds the maximum value of the temporary table

The memory table is a table in the memory. The size of the memory used can be calculated by using My. specify max_heap_table_size in cnf. For example, max_heap_table_size = 1024 M. The memory table is different from the temporary table. The temporary table is also stored in the memory. The maximum memory required for the temporary table must be set by tmp_table_size = M. When the data exceeds the maximum value of the temporary table, it is automatically converted to a disk table. When I/O operations are required, the performance will be greatly reduced, but the memory table will not. When the memory table is full, A message indicating full data error is displayed.

1. Memory table definition:

create table test(id int unsigned not null auto_increment primary key,state char(10),type char(20),date char(30))ENGINE=MEMORY DEFAULT CHARSET=utf8;

2. Memory table features:

1. The memory table is defined as stored on the disk. The extension is. frm, so the restart will not be lost.
2. The data in the memory table is stored in the memory, so data will be lost after restart.
3. The memory table uses a fixed record length format.
4. Memory tables do not support BLOB or TEXT columns, such as varchar and text fields.
5. The memory table supports the AUTO_INCREMENT column and the index of the column that can contain NULL values (this is incorrect because it is not supported on the Internet ). Memory tables support operations greater than (>) and less than (<), which are not supported on the Internet.
6. After mysql is restarted, the primary key, auto-increment, and index still exist, but data is lost. This is also used to correct some incorrect texts on the Internet.
7. The memory table is shared among all clients (just like any other non-TEMPORARY table ).
8. The MEMORY storage engine performs HASH and BTREE indexes. You can specify one or another for a given index by adding a USING clause as follows:
   

   CREATE TABLE lookup(id INT, INDEX USING HASH (id))ENGINE = MEMORY;CREATE TABLE lookup(id INT, INDEX USING BTREE (id))ENGINE = MEMORY;

   
   
9. You can use -- init-file to initialize the memory table to avoid data clearing after mysql is restarted. For example, -- init-file = "/data/mysql/init. SQL", the init. SQL format is:
   

   use db_test;select *** into m_table;

   
   
10. During database replication, if the host crashes, the delete from [memory table] will be automatically added to the binLog to delete the slave data to ensure data consistency between the two sides.
11. The memory table does not support transactions.
12. The memory table is a table lock. When the modification is frequent, the performance may decrease.

3. Summary:

1. for multiple sessions, the name of the table to be created cannot be the same
2. After a session is created, it is visible to other sessions.
3. The data directory contains only tmp_memory.frm, the table structure is placed on the disk, and the data is stored in the memory.
4. After mysql is restarted or disabled, data in the memory table will be lost, but the table structure still exists.
5. You can create and delete indexes and support unique indexes.
6. It does not affect the master and slave databases. The data inserted in the master database can also be found in the slave database.
7. show tables

Temporary table:

1. Create:

CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL)  

2. features:

1. The name of the created table can be the same.
2. The table structure and data are stored in the memory.
3. The session disappears. Both the table structure and data are lost.
4. You can create and delete indexes.
5. The table created in the master database cannot be found in the slave database,
6. show tables cannot see the table