MySQL之其他存储引擎.docVIP

Merge存储引擎 也叫MRG_MyISAM，为那些完全一致的MyISAM表提供统一的访问接口，使得它们好像是合并了一样，实际上Merge并不存储数据。合并的MyISAM表要求具有完全相同的列和索引信息。mysql CREATE TABLE t1(id INT PRIMARY KEY,name VARCHAR(20)); Query OK, 0 rows affected (0.05 sec) mysql CREATE TABLE t2(id INT PRIMARY KEY,name VARCHAR(20)); Query OK, 0 rows affected (0.01 sec) 各插入2条实验数据 mysql INSERT INTO t1 VALUES(1,a),(2,b); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql INSERT INTO t2 VALUES(3,c),(4,d); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM t1; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql SELECT * FROM t2; +----+------+ | id | name | +----+------+ | 3 | c | | 4 | d | +----+------+ 2 rows in set (0.00 sec) 创建Merge表： union选项指定合并的基表，insert_method选项指明了当向merge表插入数据时，实际插入的表，如不指定，插入时会报错。 mysql CREATE TABLE merge_t1_t2 (id INT PRIMARY KEY, name VARCHAR(20)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST ; Query OK, 0 rows affected (0.01 sec) 查询Merge表结果为t1和t2的并集： mysql SELECT * FROM merge_t1_t2; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ 4 rows in set (0.00 sec) 向Merge表插入数据，由于指定了INSERT_METHOD=LAST，实际插入t2表： mysql insert into merge_t1_t2 values(5,e); Query OK, 1 row affected (0.03 sec) mysql SELECT * FROM merge_t1_t2; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) mysql SELECT * FROM t2; +----+------+ | id | name | +----+------+ | 3 | c | | 4 | d | | 5 | e | +----+------+ 3 rows in set (0.00 sec) 此时，数据目录中也生成了Merge表的文件：merge_t1_t2.frm和merge_t1_t2.MRG 我们来看一下.MGR文件的内容： [root@web01 engine]# cat merge_t1_t2.MRG t1 t2 #INSERT_METHOD=LAST 发现.MGR简单地保存了基表的表名，没有保存实际数据，那么是不是可以通过修改.MGR文件来修改Merge表呢？ 创建表t3，插入2条数据： mysql CREATE TABLE t3(id INT PRIMARY KEY,name VARCHAR(20)); Query OK, 0 rows affected (0.00 sec) mysql I