取模分表,根据时间维度进行分表自定义的Hash
分表实现原理:利用sqlparser解析sql参数,根据参数修改相关的表名为实际表名。
分表后的数据复制,一般采用insert select语句将原有表的数据导入新的分表,或者直接copy原表的数据到分表中。比如根据id取模分四张表,分表后把原有数据复制示例如下。
insert into user1(id,name, extDO, hobbys, votes) select * from user where id mod 4 = 0
insert into user2(id,name, extDO, hobbys, votes) select * from user where id mod 4 = 1
insert into user3(id,name, extDO, hobbys, votes) select * from user where id mod 4 = 2
insert into user4(id,name, extDO, hobbys, votes) select * from user where id mod 4 = 3
插入主表的数据到分表数据中
INSERT INTO tb_member1(tb_member1.id,tb_member1.name,tb_member1.sex)SELECT member.id,member.name,member.sex FROM member where member.id%2=0 ;
INSERT INTO tb_member2(tb_member2.id,tb_member2.name,tb_member2.sex) SELECT member.id,member.name,member.sex FROM member where member.id%2=1 ;
INSERT INTO tb_member3(tb_member3.id,tb_member3.name,tb_member3.sex) SELECT member.id,member.name,member.sex FROM member where member.id%2=2 ;
INSERT INTO tb_member3(tb_member3.id,tb_member3.name,tb_member3.sex) SELECT member.id,member.name,member.sex FROM member where member.id%2=3 ;