1、实验环境win及其mysql版本:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25 |
+-----------+
1 row in set (0.03 sec)
2、创建两张子表并建立主键:
mysql> create table t1 (a int not null primary key) engine=myisam;
Query OK, 0 rows affected (0.13 sec)
mysql> create table t2 (a int not null primary key) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
3、插入数据:
mysql> insert into t1(a) values(1),(2);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2(a) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
4、创建合并表:
mysql> create table mrg(a int not null primary key)
-> engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.08 sec)
注意:insert_method=last ---------------last将insert送入合并表中的最后一张表中
---------------first将insert送入合并表中的第一张表中
仅此两个选项
merge数据引擎:
5、合并表中插入数据:
mysql> select * from mgr;
ERROR 1146 (42S02): Table 'test.mgr' doesn't exist
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
+---+
4 rows in set (0.00 sec)
mysql> insert into mrg(a) values (3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
|
3 |
+---+
5 rows in set (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
|
3 |
+---+
3 rows in set (0.00 sec)
可以看出结果是正确的;
6、子表中插入数据:
mysql> insert into t2(a) values (4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
+---+
6 rows in set (0.00 sec)
mysql> insert into t1(a) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
7 rows in set (0.00 sec)
可以看出合并表中同样拥有子表中的数据;
7、删除合并表:
mysql> select * from mrg;
ERROR 1146 (42S02): Table 'test.mrg' doesn't exist
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
+---+
3 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)
mysql> insert into t1(a) values (6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(a) values (6);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
| 6 |
+---+
4 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+---+
5 rows in set (0.00 sec)
可以看出合并表没了,子表可以查看;
8、删除子表:
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mrg;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
or of non-MyISAM type or doesn't exist
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+---+
5 rows in set (0.00 sec)
mysql> insert into mrg(a) values (7);
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
or of non-MyISAM type or doesn't exist
可以看出删除了子表后合并表不能查询和写入。
转载于:https://blog.51cto.com/shouhouzhe/1376886