当前位置:Gxlcms > 数据库问题 > 使用Merge存储引擎实现MySQL分表

使用Merge存储引擎实现MySQL分表

时间:2021-07-01 10:21:17 帮助过:27人阅读

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 ;

  2.用户2表

  1.   <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> user2 (
  2.    id </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000"> AUTO_INCREMENT,
  3.    name </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">50</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  4.     sex </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">0</span><span style="color: #000000">,
  5.   </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> (`id`)
  6.   ) ENGINE</span><span style="color: #808080">=</span>MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span>utf8 AUTO_INCREMENT<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> ;

  3.主表

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">NOT</span> <span style="color: #808080">EXISTS</span><span style="color: #000000"> alluser (
  2.    id </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000"> AUTO_INCREMENT,
  3.    name </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">50</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  4.    sex </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">0</span><span style="color: #000000">,
  5. </span><span style="color: #0000ff">INDEX</span>(id)) ENGINE <span style="color: #808080">= </span>MERGE <span style="color: #0000ff">UNION</span><span style="color: #808080">=</span>(user1,user2) INSERT_METHOD<span style="color: #808080">=</span>LAST AUTO_INCREMENT<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> ;

  1) ENGINE = MERGE 和 ENGINE = MRG_MyISAM是一样的意思,都是代表使用的存储引擎是 Merge。

  2) INSERT_METHOD,表示插入方式,取值可以是:0 和 1,0代表不允许插入,1代表可以插入;

  3) FIRST插入到UNION中的第一个表,LAST插入到UNION中的最后一个表。

三、操作

  1. 先在user1表中增加一条数据,然后再在user2表中增加一条数据,查看 alluser中的数据。当然这种操作是不正确的,因为我们只在总表上操作(增删改查),但是当增加的时候 我们同样可以在alluser中增加一条id相同的记录。

   insert into user1(name,sex) values (‘张三‘,1);

   insert into user2(name,sex) values (‘李四‘,2);

    select * from alluser;  发现是刚刚插入的数据如下:

   技术分享

     这就出现了一个id重复,这就造成了当删除和修改的时候异常,解决办法是给 alluser的id赋唯一值。

     我们解决方法是,重新建立一张表tb_ids(id int),然后在alluser表中建立一个触发器,触发器的功能是 当在alluser表中增加一条记录时,取出tb_ids中的id值,然后将tb_ids的id值加1,

   触发器内容如下:   

  1. <span style="color: #000000"> DELIMITER $$
  2. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TRIGGER</span><span style="color: #000000"> tr_seq
  3. BEFORE </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">on</span><span style="color: #000000"> alluser
  4. </span><span style="color: #0000ff">FOR</span> EACH ROW <span style="color: #0000ff">BEGIN</span>
  5. <span style="color: #0000ff">select</span> id <span style="color: #0000ff">into</span> <span style="color: #008000">@testid</span> <span style="color: #0000ff">from</span> tb_ids limit <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
  6. </span><span style="color: #0000ff">update</span> tb_ids <span style="color: #0000ff">set</span> id <span style="color: #808080">=</span> <span style="color: #008000">@testid</span> <span style="color: #808080">+</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
  7. </span><span style="color: #0000ff">set</span> new.id <span style="color: #808080">=</span> <span style="color: #008000">@testid</span><span style="color: #000000">;
  8. </span><span style="color: #0000ff">END</span><span style="color: #000000">$$
  9. DELIMITER;</span>

 

  2.在总表中增加一条数据,

    insert into alluser(name,sex) values(‘王五‘,1);

    insert into alluser(name,sex) values(‘赵六‘,2);

    

  

 

使用Merge存储引擎实现MySQL分表

标签:ast   连接池   操作   ges   意思   arc   image   技术   rom   

人气教程排行