听新闻说M哈弗G_MyISAM引擎的Mysql分表

  平常意况下的分表,都以直接开立四个一律布局的表,譬如table_1、table_2…以来碰着叁个异样要求,要求创设三个主表,全部分表的多少增加和删除改查,全体自行实时更新到主表,那时候能够利用M奥迪Q5G_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、成功,原本主表的多少都苏醒了

发表评论

电子邮件地址不会被公开。 必填项已用*标注