表对应的分片规则

查看rule.xml查看对应的关系

<tableRule name="sharding-by-intfile">

<rule>

<columns>sharding_id</columns>根据该字段分片

<algorithm>hash-int</algorithm>分片的方法

</rule>

</tableRule>

查看rule.xml对应的方法

<function name="hash-int"

class="io.mycat.route.function.PartitionByFileMap">

<property name="mapFile">partition-hash-int.txt</property>---对应的文件

<property name="defaultNode">1</property>

</function>

查看文件

[root@localhost conf]# more partition-hash-int.txt

10000=0####sharding_id为10000发到1节点

10010=1 ####sharding_id为10010发到2节点

DEFAULT_NODE=1 ###其它插到2节点

实验
mysql> create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employee(id,name,sharding_id) values(2,'leader us',10000);
Query OK, 1 row affected (0.01 sec)


mysql> insert into employee(id,name,sharding_id) values(4,'leader us',10000);
Query OK, 1 row affected (0.00 sec)


mysql> insert into employee(id,name,sharding_id) values(3,'leader us',100003);-----其它插到2节点



mysql> insert into employee(id,name,sharding_id) values(4,'leader us',10010);
Query OK, 1 row affected (0.01 sec)


mysql> insert into employee(id,name,sharding_id) values(5,'leader us',10010);
Query OK, 1 row affected (0.03 sec)

分片表如下：


<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

查看rule.xml对应的关系


<tableRule name="auto-sharding-long">

<rule>

<columns>id</columns>

<algorithm>rang-long</algorithm>

</rule>

</tableRule>

对应的方法


<function name="rang-long"

class="io.mycat.route.function.AutoPartitionByLong">

<property name="mapFile">autopartition-long.txt</property>

</function>

对应的文件：


# range start-end ,data node index

# K=1000,M=10000.

0-500M=0#####范围0-500M插到第一个节点

500M-1000M=1 #####范围500m-1000M插到第2个节点

1000M-1500M=2 。。。类推

<table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />


fun:




<function name="mod-long" class="io.mycat.route.function.PartitionByMod">

<!-- how many data nodes -->

<property name="count">3</property>

</function>

<tableRule name="mod-long">

<rule>

<columns>id</columns>

<algorithm>mod-long</algorithm>

</rule>

</tableRule>

tab:

<table name="month_tab" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="sharding-by-month" />

rule:

<tableRule name="sharding-by-month">

<rule>

<columns>create_time</columns>

<algorithm>partbymonth</algorithm>

</rule>

</tableRule>

<function name="partbymonth"

class="io.mycat.route.function.PartitionByMonth">

<property name="dateFormat">yyyy-MM-dd</property>

<property name="sBeginDate">2015-01-01</property>##开始时间

</function>

测试：
mysql> insert into month_tab(id,name,sharding_id,create_time) values (1,'1',1,'2015-01-01');
Query OK, 1 row affected (0.43 sec)

mysql> insert into month_tab(id,name,sharding_id,create_time) values (2,'2',2,'2015-02-02');
Query OK, 1 row affected (0.01 sec)

mysql> insert into month_tab(id,name,sharding_id,create_time) values (3,'3',3,'2015-03-03');
Query OK, 1 row affected (0.49 sec)

mysql> insert into month_tab(id,name,sharding_id,create_time) values (4,'4',4,'2015-04-04');###按月分片，只有三个节点，只能插到1,2,3月份的，4月份就开始报错了
ERROR 1064 (HY000): Can't find a valid data node for specified node index :MONTH_TAB -> CREATE_TIME -> 2015-04-04 -> Index : 3

tab：

<table name="day_tab" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-day"/>

rule：


<tableRule name="sharding-by-day">

<rule>

<columns>create_time</columns>

<algorithm>partbyday</algorithm>

</rule>

</tableRule>

<function name="partbyday"

class="io.mycat.route.function.PartitionByDate">

<property name="dateFormat">yyyy-MM-dd</property>

<property name="sBeginDate">2015-01-01</property>###起始日期

<property name="sPartionDay">3</property>###多少天后开始分片

</function>

测试：

插了前9天，分到三个分片

mysql> select * from day_tab;
+----+------+-------------+---------------------+
| id | name | sharding_id | create_time |
+----+------+-------------+---------------------+
| 7 | 1|1 | 2015-01-08 00:00:00 |
| 8 | 1|1 | 2015-01-09 00:00:00 |
| 13 | 1|1 | 2015-01-07 00:00:00 |
| 7 | 1|1 | 2015-01-01 00:00:00 |
| 8 | 1|1 | 2015-01-02 00:00:00 |
| 9 | 1|1 | 2015-01-03 00:00:00 |
| 10 | 1|1 | 2015-01-04 00:00:00 |
| 11 | 1|1 | 2015-01-05 00:00:00 |
| 12 | 1|1 | 2015-01-06 00:00:00 |
+----+------+-------------+---------------------+
9 rows in set (0.01 sec)


mysql> insert into day_tab(id,name,sharding_id,create_time) values (17,'1',1,'2015-01-10'),(18,'1',1,'2015-01-11');###插第10天的，开始报错
ERROR 1064 (HY000): Index: 3, Size: 3