MYSQL利用merge存储引擎来实现分表

 

 

创建user1和user2两个分表

建表语句如下：只是表名不一样，其他字段信息及主键一致。

CREATE TABLE IF NOT EXISTS user1(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS user2(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

加入测试数据：

INSERT INTO user1(NAME,sex) VALUES('huangbaokang',0)
INSERT INTO user2(NAME,sex) VALUES('zhanglulu',0)

创建总表：

CREATE TABLE IF NOT EXISTS t_user (  
        id INT(11) NOT NULL AUTO_INCREMENT,  
        name VARCHAR(50) DEFAULT NULL,  
       sex INT(1) NOT NULL DEFAULT '0',  
       INDEX(id)  
      )  ENGINE = MRG_MYISAM  UNION =(user1,user2) INSERT_METHOD LAST CHARSET UTF8;

采用merge类型，insert_method为last 
查询结果如下：

往t_user表中插入一条数据：

INSERT INTO t_user(NAME,sex) VALUES('猪八戒',1);

• 1

 
插入到了user2表中，因为INSERT_METHOD为last，最后插入的是user2表。

业务分表实现

当一个项目数据库表设计的时候没有考虑到分表时，时间久而久之，表的数据量会非常巨大，如某某平台注册信息表。

假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据，

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000

INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000

这样我就成功的将一张user表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个t_user表，只把这个t_user表的表名改成user就行了。但是，不是所有的mysql操作都能用的。

如： 
如果你使用 alter table 来把 merge 表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层 myisam 表的行被复制到已更换的表中，该表随后被指定新类型。

更新t_user表，看会不会影响其他表数据。执行如下：

UPDATE t_user SET sex=1

再次查询，发现是可以修改存储在其他表的数据。