mycat one群:106088787。
说明:根据字段按照月分片。
注释:字段为时间类型。
一、conf/schema.xml文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="sharding_by_month" primaryKey="id" dataNode="dn$0-2"
rule="sharding-by-month" />
</schema>
<dataNode name="dn0" dataHost="dh-1" database="db0"/>
<dataNode name="dn1" dataHost="dh-1" database="db1"/>
<dataNode name="dn2" dataHost="dh-1" database="db2"/>
<dataHost name="dh-1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
二、conf/rule.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>part-by-month</algorithm>
</rule>
</tableRule>
<function name="part-by-month"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2017-10-01</property>
<property name="sEndDate">2017-12-30</property>
</function>
</mycat:rule>
三、规则文件信息
无
四、测试用到的sql
=============按照create_time字段 按 月分================
CREATE TABLE sharding_by_month (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL);
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-10-01', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-10-30', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-11-11', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-11-21', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-12-01', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-12-31', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2018-01-01', database());
INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2018-01-31', database());
select * from sharding_by_month;
五、注意事项
1、schema里的table的dataNode节点个数必须:大于rule的开始时间按照分片数计算到现在的个数
2、按照自然月计算(无论是28、30、31天都是一个月的)
3、分片节点个数可以后增加,但是必须符合1的说明。
六、配置说明:
columns: 分片字段,字符串类型
dateFormat : 日期字符串格式
sBeginDate : 开始日期
sEndDate:结束日期