mycat分库规则sharding-by-month

sharding-by-month按照月份分库

1.rule.xml

<tableRule name="sharding-by-month">
        <rule>
                <columns>createtime</columns>
                <algorithm>partbymonth</algorithm>
        </rule>
</tableRule>
<function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd HH:mm:ss</property>
        <property name="sBeginDate">2015-01-01 00:00:00</property>
</function>

dateFormat为日期格式,sBeginDate为开始日期

2.schema.xml
点击(此处)折叠或打开

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="">

        <schema name="logdb" checkSQLschema="false" sqlMaxLimit="100">
          <table name="tb_log_t" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="mod-long" />
          <table name="tb_user_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-intfile-provcode" />
          <table name="tb_user_detail_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="auto-sharding-long-userid" />
          <table name="tb_user_murmur_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-murmur" />
          <table name="tb_user_murmur_string_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-murmur-userid" />
          <table name="tb_user_crc32slot_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="crc32slot" />
          <table name="tb_user_partbymonth_t" dataNode="dn$1-13" rule="sharding-by-month" />
        </schema>
        <schema name="coss03" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnoss03"/>
        <dataNode name="dn1" dataHost="localhost1" database="log01" />
        <dataNode name="dn2" dataHost="localhost1" database="log02" />
        <dataNode name="dn3" dataHost="localhost1" database="log03" />
        <dataNode name="dn4" dataHost="localhost1" database="log04" />
        <dataNode name="dn5" dataHost="localhost1" database="log05" />
        <dataNode name="dn6" dataHost="localhost1" database="log06" />
        <dataNode name="dn7" dataHost="localhost1" database="log01" />
        <dataNode name="dn8" dataHost="localhost1" database="log02" />
        <dataNode name="dn9" dataHost="localhost1" database="log03" />
        <dataNode name="dn10" dataHost="localhost1" database="log04" />
        <dataNode name="dn11" dataHost="localhost1" database="log05" />
        <dataNode name="dn12" dataHost="localhost1" database="log06" />
        <dataNode name="dn13" dataHost="localhost1" database="log01" />
        <dataNode name="dnoss03" dataHost="localhost1" database="oss03" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.56.141:3306" user="root"
                                   password="mysql">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.56.142:3306" user="root" password="mysql" />
                </writeHost>
        </dataHost>
</mycat:schema>

name="tb_user_partbymonth_t" 这里定义了"dn$1-13" 13个datanode,每个datanode写入不同的实际数据库(log01至log06),13个datanode,只能写入13个月份,月份从2015-01至2016-01,感觉这种分库方法不是很好，新增月份分库的时候还需要修改schema.xml文件，然后重启动mycat.

3.创建表
CREATE TABLE `tb_user_partbymonth_t` (
  `id` varchar(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

写入数据(注意这里不能使用now函数)
insert into tb_user_partbymonth_t(id,name,createtime) values('a0001','name1','2015-01-01 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0002','name1','2015-02-02 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0003','name1','2015-03-01 00:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0004','name1','2015-04-01 00:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0005','name1','2015-05-01 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0006','name1','2015-06-02 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0007','name1','2015-07-01 00:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0008','name1','2015-08-01 00:00:00');

insert into tb_user_partbymonth_t(id,name,createtime) values('a0009','name1','2015-09-01 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0010','name1','2015-10-02 10:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0011','name1','2015-11-01 00:00:00');
insert into tb_user_partbymonth_t(id,name,createtime) values('a0012','name1','2015-12-01 00:00:00');

insert into tb_user_partbymonth_t(id,name,createtime) values('a0013','name1','2016-01-01 00:00:00');

-- The End --