MySQL-MERGE存储引擎的使用测试

MERGE 存储引擎，主要用于合并多张表结构相同的 MyISAM 表数据（类似union all，不是union，因为数据不会去重），merge 表实际不存储数据。实践记录如下：

1、创建三张相同表结构的  MyISAM 表：payment_2006、payment_2007、payment_2008：

create table payment_2006
(
  country_id smallint,
  payment_date datetime,
  amount decimal(15,2),
  key idx_fk_country_id(country_id)
) engine=myisam;


create table payment_2007
(
  country_id smallint,
  payment_date datetime,
  amount decimal(15,2),
  key idx_fk_country_id(country_id)
) engine=myisam;


create table payment_2008
(
  country_id smallint,
  payment_date datetime,
  amount decimal(15,2),
  key idx_fk_country_id(country_id)
) engine=myisam;

2、分别向 payment_2006 、 payment_2007 、payment_2008表中插入测试数据： 

insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000); 

insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000); 

insert into payment_2008 values(1,'2008-08-08',18000),(2,'2008-03-15',380000); 

3、分别查看这三表张的记录：

mysql> select * from payment_2006;

+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.01 sec)


mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)


mysql> select * from payment_2008;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2008-08-08 00:00:00 |  18000.00 |
|          2 | 2008-03-15 00:00:00 | 380000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

4、创建 merge 引擎类型的 payment_all 表：

CREATE TABLE payment_all(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
INDEX(country_id)
)engine=merge union=(payment_2008,payment_2006,payment_2007) INSERT_METHOD=FIRST;

说明： union指定要合并的 MyISAM表；INSERT_METHOD表示当向这个merge引擎类型的payment_all 表插入数据时，数据是插入到union参数指定的哪张表（FIRST：表示插入到第一张表，LAST：表示插入到最后一张表）。

5、查看merge引擎类型 payment_all 虚拟合并表的结果：

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2008-08-08 00:00:00 |  18000.00 |        
|          2 | 2008-03-15 00:00:00 | 380000.00 |
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+

说明：因为在创建 payment_all时，union参数指定的第一张表是 payment_2008，所以先显示payment_2008表的数据。 

6、向merge引擎类型 payment_all 虚拟合并表插入数据：

insert into payment_all values(3,'2006-01-01',9999);

7、检查插入的数据：

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2008-08-08 00:00:00 |  18000.00 |
|          2 | 2008-03-15 00:00:00 | 380000.00 |
|          3 | 2006-01-01 00:00:00 |   9999.00 |
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
7 rows in set (0.00 sec)


mysql> select * from payment_2008;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2008-08-08 00:00:00 |  18000.00 |
|          2 | 2008-03-15 00:00:00 | 380000.00 |
|          3 | 2006-01-01 00:00:00 |   9999.00 |
+------------+---------------------+-----------+

看到，数据已经插入到了 创建 payment_all 表时union参数指定的第一张表 payment_2008中，因为创建 payment_all 表时，INSERT_METHOD=FIRST。

（完）