merge表

?

    注意：

1  每個(gè)子表的結(jié)構(gòu)必須一致，主表和子表的結(jié)構(gòu)需要一致，



2  每個(gè)子表的索引在merge表中都會(huì)存在，所以在merge表中不能根據(jù)該索引進(jìn)行唯一性檢索。



3  子表需要是MyISAM引擎

4  AUTO_INCREMENT 不會(huì)按照你所期望的方式工作。



建表語(yǔ)句



create table tablename(正常的字段)engine=merge insert_method=last



insert_method：



有兩個(gè)值如下：

LAST  如果你執(zhí)行insert 指令來(lái)操作merge表時(shí)，插入操作會(huì)把數(shù)據(jù)添加到最后一個(gè)子表中。



FIRST  同理，執(zhí)行插入數(shù)據(jù)時(shí)會(huì)把數(shù)據(jù)添加到第一個(gè)子表中。



例子：



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('張三',0);



insert into user2 (name,sex) values('lisi',1);



mysql> select * from user1;

+----+------+------+

| id | name | sex  |

+----+------+------+

|  1 | 張三 |    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 | 張三 |    0 |

|  1 | lisi |    1 |

+----+------+------+



mysql> insert into alluser(name,sex) values('嘿嘿',0);



mysql> select * from user1;

+----+------+------+

| id | name | sex  |

+----+------+------+

|  1 | 張三 |    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)

//他把這條數(shù)據(jù)存入了user2表里是因?yàn)槲覀兊膇nsert_method的參數(shù)填寫的是last



mysql> update alluser set sex=replace(sex,0,1) where id=2;

+----+------+------+

| id | name | sex  |

+----+------+------+

|  1 | 張三 |    0 |

|  1 | lisi |    1 |

|  2 | 嘿嘿 |    1 |

+----+------+------+


  

?

?

mysql-merge合并表