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 IF  not EXISTSUser1 (idint( One) not NULLauto_increment, namevarchar( -)DEFAULT NULL, Sexint(1) not NULL DEFAULT 0,PRIMARY KEY(ID)) ENGINE=MyISAMDEFAULTCHARSET=UTF8 auto_increment=1;

2. User 2 Table

 　  CREATE TABLE IF  not EXISTSUser2 (IDint( One) not NULLauto_increment, namevarchar( -)DEFAULT NULL, Sexint(1) not NULL DEFAULT 0,PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8 auto_increment=1;

3. Main Table

   CREATE TABLE IF  not EXISTSAllUser (IDint( One) not NULLauto_increment, namevarchar( -)DEFAULT NULL, Sexint(1) not NULL DEFAULT 0,     INDEX(ID)) ENGINE= MERGEUNION=(User1,user2) Insert_method=Last Auto_increment=1;

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. Of course this kind of operation is not correct, because we only operate on the general table (add and remove check), but when the increase we can also add a record of the same ID in 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.

Our workaround is to re-establish a table tb_ids (id int), and then create a trigger in the AllUser table, where the function of the trigger is to remove the ID value from the tb_ids when a record is added to the AllUser table, and then add the Tb_ids ID value to 1.

The trigger contents are as follows:

DELIMITER $$CREATE TRIGGERTr_seq beforeINSERT  onAllUser forEach ROWBEGIN       SelectId into @testid  fromTb_ids limit1; UpdateTb_idsSetId= @testid + 1; SetNew.id=  @testid; END$$ DELIMITER;

2. Add a piece of data to the summary table,

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

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

　　

Using the merge storage engine to implement MySQL sub-table