Using the merge storage engine to implement MySQL sub-table

First, the use of the scene

The merge table is somewhat similar to a view. Using the merge storage engine to implement MySQL sub-table, this method is more suitable for those who did not consider the table in advance, with the increase in data, there has been a slow data query.

This time if you want to separate the existing big data scale pain, the most painful thing is to change the code. Therefore, using the merge storage engine to implement MySQL sub-table can avoid code changes.

Each table under the merge engine has only one MRG file. The MRG stores the relationship of the tables and the way in which the data is inserted. It's like a shell, or a connection pool, where the data is stored in a sub-table.

For additions and deletions, direct operation of the general table can be.

Ii. Establishment of the table

1. User 1 table

CREATE TABLE ' user1 ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar (#) DEFAULT NULL,  ' sex ' int (1) not ' NULL DEFAULT ' 0 ',  

2. User 2 Table

Create table user2 like User1;

3. Main Table

CREATE TABLE ' AllUser ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar () DEFAULT NULL,  ' sex ' int (1) N OT NULL Default ' 0 ',  KEY ' id ' (' id ')) engine=mrg_myisam default Charset=utf8 insert_method=last union= (' User1 ', ' User2 ');

1) engine = Merge and engine = Mrg_myisam is the same meaning, all are represented using the storage engine is the MERGE.

2) Insert_method, indicating the insertion method, the value can be: 0 and 1, 0 is not allowed to insert, 1 means can be inserted;

3) First insert into union, last inserted into the final table in union.

Third, the operation

1. First add a piece of data to the User1 table and then add a piece of data to the User2 table to see the data in the AllUser.

Insert into User1 (name,sex) VALUES (' Zhang San ', 1);

Insert into User2 (name,sex) VALUES (' John Doe ', 2);

SELECT * from AllUser; Found the data that was just inserted as follows:

　　　

This presents an ID repetition, which causes the exception when deleted and modified, and the workaround is to assign a unique value to the ID of the AllUser.

The workaround is to re-establish a table tb_ids (id int), which is used to save an ID, insert an initial data, and delete the data from User1 and User2.

CREATE TABLE tb_ids (id int);　　INSERT into tb_ids values (1);　　Delete from User1; Delete from User2;

A trigger is then created in the User1 and User2 tables, where the function of the trigger is to add a record in the User1 or User2 table, remove the ID value from the Tb_ids, assign the User1 and User2 ID, and then add the Tb_ids ID value to 1.

The trigger content is as follows (change User1 to User2):

   DELIMITER $$   CREATE TRIGGER tr_seq   before INSERT on user1   for each ROW BEGIN       select ID  into @testid from Tb_ids limit 1;      Update tb_ids Set id = @testid + 1;   Set new.id =  @testid;   end$$   DELIMITER;

2. Add a separate piece of data to the User1 and User2 tables,

Insert into User1 (name,sex) VALUES (' Harry ', 1);

Insert into User2 (name,sex) VALUES (' Zhao Liu ', 2);

3. Query the data in User1 and User2:

4. Query the data in the summary table AllUser and find that the ID is not duplicated:

Get.

Using the merge storage engine to implement MySQL sub-table