使用Merge存储引擎实现 mysql分表

Merge引擎下每一张表只有一个MRG文件。MRG里面存放着分表的关系，以及插入数据的方式。它就像是一个外壳，或者是连接池，数据存放在分表里面。

merge合并表的要求：

合并的表使用的必须是MyISAM引擎

表的结构必须一致，包括索引、字段类型、引擎和字符集

CREATE TABLE `fenbiao1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_time` datetime DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

复制表

create table fenbiao2 like fenbiao1;
create table fenbiao3 like fenbiao1;
create table fenbiao4 like fenbiao1;
create table fenbiao5 like fenbiao1;
create table fenbiao6 like fenbiao1;
create table fenbiao7 like fenbiao1;
create table fenbiao8 like fenbiao1;
create table fenbiao9 like fenbiao1;
create table fenbiao10 like fenbiao1;
create table fenbiao11 like fenbiao1;
create table fenbiao12 like fenbiao1;

联合表

CREATE TABLE `fenbiao0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_time` datetime DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8  UNION=(
`fenbiao1`,
`fenbiao2`,
`fenbiao3`,
`fenbiao4`,
`fenbiao5`,
`fenbiao6`,
`fenbiao7`,
`fenbiao8`,
`fenbiao9`,
`fenbiao10`,
`fenbiao11`,
`fenbiao12`);

加入触发器

SHOW  TRIGGERS
DROP    TRIGGER   trigger_insert_fenbiao

ps:

触发器：一类特殊的数据库程序，可以监视某种数据的操作(insert/update/delete),并触发相关的操作(insert/update/delete)，保护数据的完整性

语法：

creat trigger 触发器名称
after/before(触发器工作的时机)
update/delete/insert(触发器监听事件)
on 表名(触发器监听的目标表)
for each row(行级监视，mysql固定写法，oracle不同)
begin
  sql语句集........（触发器执行动作，分号结尾）
end;

实例：根据月存到不同的表

create trigger trigger_insert_fenbiao
before insert on fenbiao0
for each row
begin
	DECLARE newMonth VARCHAR(2);
		set newMonth= month(NEW.event_time);
		IF (newMonth = '1')
			THEN INSERT INTO fenbiao1 VALUES (NEW);
		ELSEIF (newMonth = '2')
			THEN INSERT INTO fenbiao2 VALUES (NEW);
		ELSEIF(newMonth = '3')
			THEN INSERT INTO fenbiao3 VALUES (NEW);
		ELSEIF(newMonth = '4')
			THEN INSERT INTO fenbiao4 VALUES (NEW);
		ELSEIF(newMonth = '5')
			THEN INSERT INTO fenbiao5 VALUES (NEW);
		ELSEIF(newMonth = '6')
			THEN INSERT INTO fenbiao6 VALUES (NEW);
		ELSEIF(newMonth = '7')
			THEN INSERT INTO fenbiao7 VALUES (NEW);
		ELSEIF(newMonth = '8')
			THEN INSERT INTO fenbiao8 VALUES (NEW);
		ELSEIF(newMonth = '9')
			THEN INSERT INTO fenbiao9 VALUES (NEW);
		ELSEIF(newMonth = '10')
			THEN INSERT INTO fenbiao10 VALUES (NEW);
		ELSEIF(newMonth = '11')
			THEN INSERT INTO fenbiao11 VALUES (NEW);
		ELSEIF(newMonth = '12')
			THEN INSERT INTO fenbiao12 VALUES (NEW);
		END IF;
end;

即创建表的语句制定引擎即可：engine=myisam

另外，创建分表也可以用like，如：CREATETABLEt2liket1;
对应定期分表的情况下，只要定期相应的增加一个基础表，再修改merge表中的union就行了（ALTERTABLEtbl_nameUNION=(...)）。
如在增加一个表（需和其他基础表一样的结构）：

CREATE TABLE t3(a INT NOT NULL AUTO_INCREMENT PRIMARYKEY,message CHAR(20));
ALTER TABLE total UNION=(t1,t2,t3)

INSERT_METHOD=LAST;表示插入的方法，INSERT_METHOD的值可以是FIRST（插入第一个表），LAST（最后一个表），NO（不能插入）
查询的时候，和平常一样

select  * from total where....
merge表会自动找到相应的基础表进行查询。

 

需要注意的是merge表并不维护“唯一性”检查，唯一性有各基础表完成。所以插入新的记录时候可能和其他基础表的内容重复。所以再插入去需要用代码进行唯一性检查。

 

 

不能直接删除一个分表，这样会破坏merge表。正确的方法是:
alter table t ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;
drop table t1