MySQL Merge Engine Implementation sub-table

The merge engine is a set of MyISAM tables, the composition of the table structure must be identical, the merge table itself has no data, the operation of the merge table is actually the operation of the sub-table, but is transparent to the app, when inserted by default is inserted on the last table, You can also specify to insert into the first table, the merger table is actually just one shell of multiple sub-tables, deleting it without affecting the actual stored data.

Create a child table User1

CREATE TABLE ' user1 ' (' id ' int (one) not null auto_increment, ' name ' varchar () CHARACTER SET latin1 DEFAULT NULL, PRI MARY KEY (' id ')) engine=myisam auto_increment=2 DEFAULT Charset=utf8;

Create a child table User2

CREATE TABLE ' user2 ' (' id ' int (one) not null auto_increment, ' name ' varchar () CHARACTER SET latin1 DEFAULT NULL, PRI MARY KEY (' id ')) engine=myisam auto_increment=2 DEFAULT Charset=utf8;

Create a merge table AllUser

CREATE TABLE ' AllUser ' (' id ' int (one) not null, ' name ' varchar () DEFAULT NULL, PRIMARY KEY (' id ')) engine=mrg_myisam DEFAULT charset=latin1 insert_method=last union= (' user1 ', ' user2 ');

Insert data for AllUser now

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/9C/3C/wKioL1ltxI2BDSe_AAAo8oVwel0381.jpg-wh_500x0-wm_ 3-wmp_4-s_267681147.jpg "title=" 1.jpg "alt=" wkiol1ltxi2bdse_aaao8ovwel0381.jpg-wh_50 "/>650) this.width=650;" Src= "Https://s1.51cto.com/wyfs02/M00/9C/3B/wKioL1ltxG6gpCvDAAAo8oVwel0713.jpg-wh_500x0-wm_3-wmp_4-s_ 2392584309.jpg "title=" 1.jpg "alt=" Wkiol1ltxg6gpcvdaaao8ovwel0713.jpg-wh_50 "/>

Then look at the data found in the User2 table and insert it.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/9C/3C/wKiom1ltxL3wjeqCAAAnas7bGzk683.jpg-wh_500x0-wm_ 3-wmp_4-s_3995468817.jpg "title=" 2.jpg "alt=" Wkiom1ltxl3wjeqcaaanas7bgzk683.jpg-wh_50 "/>

Next, insert the Sub table tables and view the AllUser table

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/9C/3C/wKiom1ltxOvw6cVNAAAm-8NsFSk725.jpg-wh_500x0-wm_ 3-wmp_4-s_3950426516.jpg "style=" Float:none; "title=" 3.jpg "alt=" Wkiom1ltxovw6cvnaaam-8nsfsk725.jpg-wh_50 "/>

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/9C/3C/wKioL1ltxOyDVwqAAAApa1C6Bqc649.jpg-wh_500x0-wm_ 3-wmp_4-s_1620798429.jpg "style=" Float:none; "title=" 4.jpg "alt=" Wkiol1ltxoydvwqaaaapa1c6bqc649.jpg-wh_50 "/>

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/9C/3C/wKioL1ltxP6ifVGIAAAyKb1XDmg147.jpg-wh_500x0-wm_ 3-wmp_4-s_3629557246.jpg "title=" 5.jpg "alt=" Wkiol1ltxp6ifvgiaaaykb1xdmg147.jpg-wh_50 "/>

found that the insert operation of the child table will eventually be mapped to the AllUser table, a certain sense of the implementation of the table, but there are limitations, that is, the data distribution is not uniform, because the insertion can not be custom rules, can only insert the child table specified at the time of creation, and there is no restriction on the check, For example, in three tables, we set the ID as the primary key, we insert an existing data in the User2 in the User1, but the AllUser table is not error-prone and inserted successfully

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/9C/3C/wKioL1ltxmjRkKTdAAA1PM6kZDc831.jpg-wh_500x0-wm_ 3-wmp_4-s_1360109523.jpg "style=" Float:none; "title=" 1.jpg "alt=" Wkiol1ltxmjrkktdaaa1pm6kzdc831.jpg-wh_50 "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/9C/3C/wKiom1ltxmihAGnYAAA0QRULkuU941.jpg-wh_500x0-wm_ 3-wmp_4-s_3499055595.jpg "style=" Float:none; "title=" 2.jpg "alt=" Wkiom1ltxmihagnyaaa0qrulkuu941.jpg-wh_50 "/>

For these reasons, the merge engine implementation of the sub-table in practical applications is not much

MySQL Merge Engine Implementation sub-table