Mysql-merge table _ MySQL

Mysql-merge table bitsCN.com

Mysql-merge table

[SQL]

Note:

1. the structure of each sub-table must be consistent. 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. the sub-table must be the MyISAM engine.

4 AUTO_INCREMENT 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 user2 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 | + ---- + ------ +

BitsCN.com