星空网 > 软件开发 > 数据库

基于MRG_MyISAM引擎的Mysql分表

  正常情况下的分表,都是直接创建多个相同结构的表,比如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、成功,原来主表的数据都恢复了




原标题:基于MRG_MyISAM引擎的Mysql分表

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

南岔物流:https://www.goluckyvip.com/tag/98615.html
桦南物流:https://www.goluckyvip.com/tag/98616.html
辉南物流:https://www.goluckyvip.com/tag/98617.html
定南物流:https://www.goluckyvip.com/tag/98618.html
南乐物流:https://www.goluckyvip.com/tag/98619.html
Konga上市:https://www.goluckyvip.com/tag/9862.html
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流