基於MRG_MyISAM引擎的Mysql分表，mrg_myisammysql

基於MRG_MyISAM引擎的Mysql分表，mrg_myisammysql

　　正常情況下的分表，都是直接創建多個相同結構的表，比如table_1、table_2...最近碰到一個特殊需求，需要創建一個主表，所有分表的數據增刪改查，全部自動實時更新到主表，這個時候可以使用MRG_MyISAM引擎了。

　　首先創建主表`mygame_message_log`。然後創建分表：

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、成功，原來主表的數據都恢復了