mycat1.6.5分片(按小时)

来源：互联网发布：林振强知乎编辑：程序博客网时间：2024/06/05 10:47

mycat one群：106088787。

说明：根据字段按照一天内的小时分片。

注释：字段为字符串类型，yyyymmddHH 10位。

一、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_hour" primaryKey="id" dataNode="dn$0-2"   rule="sharding-by-hour" />   </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-hour"><rule><columns>sharding_col</columns><algorithm>sharding-by-hour</algorithm></rule></tableRule><function name="sharding-by-hour"class="io.mycat.route.function.LatestMonthPartion"><property name="splitOneDay">3</property></function></mycat:rule>

三、规则文件信息

无

四、测试用到的sql

=============按照create_time字段 按 小时分================CREATE TABLE  sharding_by_hour  (create_time   timestamp NULL ON UPDATE CURRENT_TIMESTAMP  ,`db_nm`  varchar(20) NULL,sharding_col varchar(10) null);INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100101','2017-10-01', database());INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100108','2017-10-01', database());INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100109','2017-10-01', database());INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100116','2017-10-01', database());INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100117','2017-10-01', database());INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100123','2017-10-01', database());select * from sharding_by_hour;

五、注意事项

1、分片数必须跟dataNode节点数一致

2、最大分24个分片

3、分片字段的值必须是字符串，如：2017102112.(即为2017年10月21日12点)

六、参数说明

columns：拆分字段，字符串类型（yyyymmddHH）

splitOneDay ：一天切分的分片数

阅读全文

0 0