Mysql-merge table

Mysql-merge table [SQL] Note: 1 the structure of each sub-table must be consistent, and the structure of the master table and sub-table must be consistent, 2. The index of each sub-table exists in the merge table. Therefore, you cannot perform a unique search based on the index in the merge table. 3 sub-tables must be MyISAM engine 4 AUTO_INCREMENT and will not work as expected. Table creation statement

Create table tablename (normal field) engine = merge insert_method = last

 

Insert_method: There are two values: LAST. If you execute the insert command to operate the merge table, the insert operation adds the data to the LAST sub-table. Similarly, when inserting data, the data is added to the FIRST sub-table. Example:

Create table user1 (id int (10) not null auto_increment, name varchar (50), sex int (1), primary key (id) engine = myisam charset = utf8; create table user2 (id int (10) not null auto_increment, name varchar (50), sex int (10), primary key (id) engine = myisam charset = utf8; insert into user1 (name, sex) values ('zhang san', 0); insert into user2 (name, sex) values ('lisi', 1 ); mysql> select * from user1; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | + ---- + ------ + mysql> select * from user2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | lisi | 1 | + ---- + ------ + create table alluser (id int (10) not null auto_increment, name varchar (50), sex int (10), index (id) type = merge union = (user1, user2) insert_method = last; mysql> select * from alluser; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | 1 | lisi | 1 | + ---- + ------ + ------ + mysql> insert into alluser (name, sex) values (' ', 0); mysql> select * from user1; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | + ---- + ------ + 1 row in set (0.00 sec) mysql> select * from user2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | lisi | 1 | 2 | | 0 | + ---- + ------ + ------ +

 

2 rows in set (0.00 sec) // This data is stored in the user2 table because our insert_method parameter is set to last

Mysql> update alluser set sex = replace (sex, 0, 1) where id = 2; + ---- + ------ + | id | name | sex | + ---- + ------ + | 1 | Zhang San | 0 | 1 | lisi | 1 | 2 | | 1 | + ---- + ------ +