Mysql的存储引擎之:MERGE存储引擎

Merge是基于MyISAM存储引擎的表,所以Merge存储引擎也叫做MGR_MyISAM存储引擎,作用是将一组MyISAM存储引擎的表聚合成一张表。

 

Merge中要聚合的MyISAM表的列和索引必须有相同的定义及顺序。

 

建立MERGE存储引擎的表

建立两个基于MyISAM存储引擎的表table_myisam1、tabl_myisam2:

 

mysql> create table table_myisam1(id intprimary key, name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> create table table_myisam2(id intprimary key, name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table_myisam1 values(1,'myisam1');
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> insert into table_myisam2 values(1,' myisam2');
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0  Warnings: 0

 

 

 

建立基于MERGE存储引擎的表table_merge,聚合上面的table_myisam1、tabl_myisam2两张表:

 

mysql> create table table_merge(id intprimary key, name varchar(20)) engine=merge union(table_myisam1,table_myisam2);
Query OK, 0 rows affected (0.05 sec)

 

 

 

查询下table_merge表(就是聚合table_myisam1、tabl_myisam2表的数据):

 

mysql> select * from table_merge;
+----+---------+
| id | name    |
+----+---------+
|  1| myisam1 |
|  1| myisam2 |
+----+---------+
2 rows in set (0.00 sec)

 

其实这里有个奇怪现象,就是Merge表中的ID字段是主键,但现在重复了竟然没事。。。

感兴趣同学可以自己做个试验研究一下

 

查看下table_merge表的数据文件:

注:. MRG不是存储数据,而是指向数据来源地的文件; .frm是表结构定义文件

 

[mysql@localhost test]$ ll table_merge.*
-rw-rw---- 1 mysql mysql 8586 Sep  2 16:02 table_merge.frm
-rw-rw---- 1 mysql mysql   28 Sep 2 16:02 table_merge.MRG

 

 

 

查看下 .MRG文件内容(指向了聚合的两张表):

 

[mysql@localhost test]$ more table_merge.MRG
table_myisam1
table_myisam2

 

注:使用MERGE存储引擎的表不会存储数据,只是汇总了MyISAM表的数据而已

 

 

MERGE表的插入操作

向Merge表插入数据有两种操作方式:

1:向MyISAM子表插入数据

2:向Merge表插入数据

 

向MyISAM子表插入数据,然后在Merge表中体现

向table_myisam1表新增一条数据:

 

mysql> insert into table_myisam1 values(2,'new_myisam1');
Query OK, 1 row affected (0.00 sec)

 

 

 

查看table_merge表数据:

 

mysql> select * from table_merge;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam1 |
|  1| myisam1     |
|  1| myisam2     |
+----+-------------+
3 rows in set (0.00 sec)

 

直接向MERGE表插入数据

 

直接向MERGE表插入数据的时候,需要加上插入的参数:insert_method

insert_method参数的可选项:

1:first 向第一个表插入数据

2:last 向最后一个表插入数据

3:no 不插入数据(默认值)

 

向MERGE表中聚合的最后一个表插入数据

 

mysql> alter table table_merge insert_method=last;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0  Warnings: 0

mysql> insert into table_merge values(2,'new_myisam2');
Query OK, 1 row affected (0.00 sec)

 

 

 

查看下MERGE表table_merge:

 

mysql> select * from table_merge;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam1 |
|  1| myisam1     |
|  2| new_myisam2 |
|  1| myisam2     |
+----+-------------+
4 rows in set (0.00 sec)

 

 

 

查看下MERGE表中聚合的最后一个MyISAM表table_myisam2:

 

mysql> select * from table_myisam2;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam2 |
|  1| myisam2     |
+----+-------------+
2 rows in set (0.00 sec)

 

 

MERGE表的DROP操作

 

对Merge表的Drop操作

Merge表是聚合了MyISAM表的操作,当drop一个Merge表时,只会drop掉Merge表本身,不会对聚合的MyISAM子表产生影响,同时各个MyISAM子表也不再有联系。

 

再建立一个用于测试drop的Merge表table_merge_drop:

 

mysql> create table table_merge_drop(id int primary key, name varchar(20)) engine=merge union(table_myisam1,table_myisam2);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from table_merge_drop;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam1 |
|  1| myisam1     |
|  2| new_myisam2 |
|  1| myisam2     |
+----+-------------+
4 rows in set (0.00 sec)

 

 

 

Drop这个Merge表table_merge_drop:

 

mysql> drop table table_merge_drop;
Query OK, 0 rows affected (0.00 sec)

 

 

 

再次查看MyISAM子表的数据,依然存在:

 

mysql> select * from table_myisam1;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam1 |
|  1| myisam1     |
+----+-------------+
2 rows in set (0.00 sec)

mysql> select * from table_myisam2;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam2 |
|  1| myisam2     |
+----+-------------+
2 rows in set (0.00 sec)

 

对Merge表中聚合的MyISAM子表的Drop操作

 

如果删除了Merge表中某个MyISAM子表, Merge表会报错,需要重新更改union聚合的表即可

 

删除Merge表中MyISAM子表table_myisam2:

 

mysql> drop table table_myisam2;
Query OK, 0 rows affected (0.00 sec)

 

查询Merge表会报错:

 

mysql> select * from table_merge;
ERROR 1168 (HY000): Unable to openunderlying table which is differently defined or of non-MyISAM type or doesn'texist

 

 

 

更改Merger表的union表聚合,只保留table_myisam1这一个MyISAM子表

 

mysql> alter table table_merge engine=merge union(table_myisam1) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0  Warnings: 0

 

 

 

再次查询Merge表,正常:

 

mysql> select * from table_merge;
+----+-------------+
| id | name        |
+----+-------------+
|  2| new_myisam1 |
|  1| myisam1     |
+----+-------------+
2 rows in set (0.00 sec)

 

 

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读