MySQL memory table

As the name implies, "memory tables" are created in the memory. Is that true? In fact, the MySQL memory table and table structure are created on the disk and the data is stored in the memory. Therefore, when MySQL is started, the table exists and the data also exists, if you have the permission to view this table, you can access the data in this memory table in all sessions. when MySQL is restarted, the data disappears and the structure still exists.

Create a memory table:

CREATE TABLE test(
  id int(10),
  num int(10)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

Check whether the creation is successful:

show tables;

After using the memory table, if you want to release the occupied memory, you can use either of the following methods:

-- Delete data
Delete from test;

Or

-- Clear the table
Truncate table test;

Or

-- Delete a table
Drop table test;

Memory has the following features:

1. for variable-length types such as varchar, memory tables are stored with a fixed length;

2. Memory tables can have non-unique keys;

3. The memory table cannot contain blob or text columns;

4. The memory table supports auto_increment columns;

5. The memory table supports insertion delay to give priority to reading;

6. Non-Temporary memory tables are directly shared with all clients like other non-memory tables;

When using a memory table, pay attention to the following aspects:

1. The server memory is large enough;

2. The memory table we created is different from the MySQL internal temporary table:

The data in the memory table is stored in the memory, while the internal temporary table (generated by our query statement) is stored in the memory when appropriate. When the internal temporary table becomes very large, mySQL will automatically convert it to a table stored on the disk, but the memory table we created will not be automatically converted.

3. When we separately Delete from a memory table, the memory will not be recycled; the memory will be recycled only when the entire table is deleted;

4. On the MySQL Master/Slave server, the memory table can be copied.

References:

Http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html