创建user_1、user_2表,创建合并表user_merge,注意:几个表的结构要完全一样,merge表必须使用merge引擎。
CREATE TABLE `user_1` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `user_2` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
create table `user_merge`(
id int(11) not null,
name varchar(50) not null,
primary key(`id`)
) engine=merge union=(user_1,user_2) insert_method=first;
merge表的insert_method有两个值,first和last,表示向merge表插入数据的时候是插入到第一个表还是最后一个表。
向merge表插入一条数据:
mysql> insert into user_merge(id,name) values(1,'fx1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_merge;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
+----+------+
1 row in set (0.00 sec)
查询一下user_id,看数据是不是插入到了user_1:
mysql> select * from user_1;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
+----+------+
1 row in set (0.00 sec)
数据时插入到user_1了。
向user_2插入一条数据,然后查询merge表:
mysql> insert into user_2(id,name) values(2,'fx2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_2;
+----+------+
| id | name |
+----+------+
| 2 | fx2 |
+----+------+
1 row in set (0.02 sec)
mysql> select * from user_1;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from user_merge;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
| 2 | fx2 |
+----+------+
2 rows in set (0.00 sec)
查询merge表发现能够合并user_1和user_2的数据。
更新user_2表的数据,然后查询看看:
mysql> update user_merge set name='fx22' where id=2;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user_2;
+----+------+
| id | name |
+----+------+
| 2 | fx22 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from user_merge;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
| 2 | fx22 |
+----+------+
2 rows in set (0.01 sec)
user_2的数据已经被修改了,查询merge表也正常。
删除数据,然后查询看看:
mysql> delete from user_merge where id=2;
Query OK, 1 row affected (0.02 sec)
mysql> select * from user_2;
Empty set (0.01 sec)
mysql> select * from user_merge;
+----+------+
| id | name |
+----+------+
| 1 | fx1 |
+----+------+
1 row in set (0.00 sec)
发现user_2的数据已经被删除,查询merge表时也删除了。