mysql creates memory tables

How do I create a memory table?
Creating a memory table is very easy, just note ENGINE= MEMORY:

CREATE TABLE  `tablename` ( `columnName` varchar(256) NOT NUL) ENGINE=MEMORY DEFAULT CHARSET=latin1 MAX_ROWS=100000000;

Note:

When the data in the memory table is larger than the capacity set by max_heap_table_size, mysql will convert the excess data to disk, so the performance is greatly reduced, so we need to adjust max_heap_table_size according to our actual situation, for example, in the.cnf file [mysqld] below:
max_heap_table_size = 2048M
In addition, the number of records in the table can be controlled by MAX_ROWS.

Memory tables use hash hash indexes to keep data in memory, so they are extremely fast and suitable for caching small and medium-sized databases, but their usage is limited. Here's how bluegrass feels.

1. heap is visible to all users, which makes it ideal for caching.

2. Suitable for use only. heap does not allow the use of xxxTEXT and xxxBLOB data types; Only = and are allowed < = > Operator to search for records (not allowed < , > , < = or > =); auto_increment is not supported; Only non-empty data columns can be indexed (not null).
Note: Operator" < = > This operator and the "=" operator perform the same comparison operation, but the value is 1 instead of NULL when both opcodes are NULL, and 0 instead of NULL when one opcode is NULL.

3. 1 Once the server is restarted, all the heap table data is lost, but the heap table structure still exists, because the heap table structure is stored in the actual database path and will not be deleted automatically. After the restart, heap will be emptied, and the query results for heap will be empty.

4. If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after replication, and primary key and index need to be added again, if necessary.

5. For data loss caused by restart, there are the following solutions:

Before any query, perform a simple query once to determine whether there is data in the heap table, if not, write the data again, or re-copy a table in the DROP table. This requires one more query. However, it can be written as an include file, which can be called whenever you need to use the heap table, which is more convenient.
For pages that need the heap table, the result of the data set is judged on the first and only the first query of the table on the page. If the result is empty, the data needs to be written again. This saves one query.
A better approach would be to automatically write data to heap each time mysql is rebooted. However, the configuration of the server is required and the process is complicated and limited in universality.

6. 1 Some sql statements that are expected to be used

// If the table exists, it is deleted 
DROP TABLE IF EXISTS `abc`;
// Copy the entire table xyz for heap table abc (Contains all data) 
CREATE TABLE `abc` type=heap select * from `xyz`;
// Add a primary key id
ALTER TABLE `abc` ADD PRIMARY KEY (`id`);
// Adding indexes username
ALTER TABLE `abc` ADD INDEX `abc` (`username`);