mysql myisam 分表_基于MRG_MyISAM引擎的Mysql分表

正常情况下的分表，都是直接创建多个相同结构的表，比如table_1、table_2...最近碰到一个特殊需求，需要创建一个主表，所有分表的数据增删改查，全部自动实时更新到主表，这个时候可以使用MRG_MyISAM引擎了。

首先创建主表`mygame_message_log`。然后创建分表(必须使用MyISAM引擎)：

create table `mygame_message_log_1` LIKE `mygame_message_log`;

create table `mygame_message_log_2` LIKE `mygame_message_log`;

create table `mygame_message_log_3` LIKE `mygame_message_log`;

create table `mygame_message_log_4` LIKE `mygame_message_log`;

create table `mygame_message_log_5` LIKE `mygame_message_log`;

create table `mygame_message_log_6` LIKE `mygame_message_log`;

create table `mygame_message_log_7` LIKE `mygame_message_log`;

create table `mygame_message_log_8` LIKE `mygame_message_log`;

create table `mygame_message_log_9` LIKE `mygame_message_log`;

create table `mygame_message_log_10` LIKE `mygame_message_log`;

create table `mygame_message_log_11` LIKE `mygame_message_log`;

create table `mygame_message_log_12` LIKE `mygame_message_log`;

create table `mygame_message_log_13` LIKE `mygame_message_log`;

create table `mygame_message_log_14` LIKE `mygame_message_log`;

create table `mygame_message_log_15` LIKE `mygame_message_log`;

create table `mygame_message_log_16` LIKE `mygame_message_log`;

create table `mygame_message_log_17` LIKE `mygame_message_log`;

create table `mygame_message_log_18` LIKE `mygame_message_log`;

create table `mygame_message_log_19` LIKE `mygame_message_log`;

create table `mygame_message_log_20` LIKE `mygame_message_log`;

然后将主表关联到分表：

ALTER TABLE mygame_message_log

ENGINE = MERGE UNION=

(mygame_message_log_1,

mygame_message_log_2,

mygame_message_log_3,

mygame_message_log_4,

mygame_message_log_5,

mygame_message_log_6,

mygame_message_log_7,

mygame_message_log_8,

mygame_message_log_9,

mygame_message_log_10,

mygame_message_log_11,

mygame_message_log_12,

mygame_message_log_13,

mygame_message_log_14,

mygame_message_log_15,

mygame_message_log_16,

mygame_message_log_17,

mygame_message_log_18,

mygame_message_log_19,

mygame_message_log_20)

INSERT_METHOD=no AUTO_INCREMENT=1;

需要注意的是，表的主键不能设为自增，所有表的结构全部要一致。

同时这种方法会有一个缺陷，就是一旦要修改表结构，就会很麻烦。千万不能直接修改！

最好开始就将表设计考虑周到一点，如果实在要修改，可按如下步骤：

1、删除主表；

2、针对所有分表修改相应的字段；

3、重新创建一个主表：

create table `mygame_message_log` LIKE `mygame_message_log_1`;

4、给主表做关联：

ALTER TABLE mygame_message_log

ENGINE = MERGE UNION=

(mygame_message_log_1,

mygame_message_log_2,

mygame_message_log_3,

mygame_message_log_4,

mygame_message_log_5,

mygame_message_log_6,

mygame_message_log_7,

mygame_message_log_8,

mygame_message_log_9,

mygame_message_log_10,

mygame_message_log_11,

mygame_message_log_12,

mygame_message_log_13,

mygame_message_log_14,

mygame_message_log_15,

mygame_message_log_16,

mygame_message_log_17,

mygame_message_log_18,

mygame_message_log_19,

mygame_message_log_20)

INSERT_METHOD=no AUTO_INCREMENT=1;

5、成功，原来主表的数据都恢复了