mycat分表扩展之按日期(天)分片
1.修改schema.xml配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="orders1" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" autoIncrement="true" fetchStoreNodeByJdbc="true">
<!--orders_detail与orders表根据order_id关联-->
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<!--定义全局表-->
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
<!--分片枚举-->
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
<!--范围约定-->
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>
<!--按日期（天）分片-->
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="orders1" />
<dataNode name="dn2" dataHost="host2" database="orders1" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.2.130:3306" user="root"
password="kht123">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="jdbc:mysql://192.168.2.131:3306" user="root"
password="kht123">
</writeHost>
</dataHost>
</mycat:schema>
2.修改rule.xml配置文件
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
<!--
# columns：分片字段，algorithm：分片函数
#dateFormat ：日期格式
#sBeginDate ：开始日期
#sEndDate：结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
#sPartionDay ：分区天数，即默认从开始日期算起，分隔 2 天一个分区
-->
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-01-01</property>
<property name="sEndDate">2019-01-04</property>
<property name="sPartionDay">2</property>
</function>
#用户登录信息表
CREATE TABLE login_info
(
`id` INT AUTO_INCREMENT comment '编号',
`user_id` INT comment '用户编号',
`login_date` date comment '登录日期',
PRIMARY KEY(id)
);
#插入数据
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');
INSERT INTO login_info(id,user_id,login_date) VALUES (7,101,'2019-01-07');
INSERT INTO login_info(id,user_id,login_date) VALUES (8,102,'2019-01-08');
4.查询Mycat、dn1、dn2可以看到数据分片效果
