1 取模
2 枚举
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile"> </table> <tableRule name="sharding_by_intfile"> <rule> <columns>areacode</columns> <algorithm>hash-int</algorithm> </rule> </tableRule> <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap"> <!--其中分片函数配置中,mapFile标识配置文件名称,type默认值为0,0表示Integer,非零表示String--> <property name="mapFile">partition-hash-int.txt</property> <!-- 所有的节点配置都是从0开始,及0代表节点1 defaultNode 默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点,结点为指定的值 默认节点的作用:枚举分片时,如果碰到不识别的枚举值,就让它路由到默认节点 如果不配置默认节点(defaultNode值小于0表示不配置默认节点),碰到 不识别的枚举值就会报错, like this:can't find datanode for sharding column:column_name val:ffffffff --> <property name="type">1</property> <property name="defaultNode">1</property> </function> 文件里这么写 110=0 0 就对应dn1 120=1 1 就对应dn2
注意,文件partition-hash-int.txt 和 schema.xml放在相同的路径下,也就是mycat的conf下
3 范围约定
<table name="orders_ware_info" dataNode="dn1,dn2" rule="auto_sharding_long"> </table> <tableRule name="auto_sharding_long"> <rule> <columns>order_id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <!--其中分片函数配置中,mapFile标识配置文件名称,type默认值为0,0表示Integer,非零表示String--> <property name="mapFile">autopartition-long.txt</property> <property name="defaultNode">1</property> </function> autopartition-long.txt文件里这么写 0-102=0 103-200=1
4 按照日期分片
按照月份
<tableRule name="sharding-by-date">
<rule>
<columns>date_str</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<!-- 分片规则 partbymonth 的配置 从 2015 -01 -01 开始分片 -->
<function name="partbymonth" class="org.opencloudb.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
<property name="sEndDate">2015-12-31</property>
</function>
由于只建立了8个分片,超出部分就直接抛数组越界异常了。 mysql> insert into t_range_date (id,date_str,context) values(1,'2015-01-01','month-1-str'); insert into t_range_date (id,date_str,context) values(2,'2015-02-01','month-2-str'); insert into t_range_date (id,date_str,context) values(3,'2015-03-01','month-3-str'); insert into t_range_date (id,date_str,context) values(4,'2015-04-01','month-4-str'); insert into t_range_date (id,date_str,context) values(5,'2015-05-01','month-5-str'); insert into t_range_date (id,date_str,context) values(6,'2015-06-01','month-6-str'); insert into t_range_date (id,date_str,context) values(7,'2015-07-01','month-7-str'); insert into t_range_date (id,date_str,context) values(8,'2015-08-01','month-8-str'); insert into t_range_date (id,date_str,context) values(9,'2015-09-01','month-9-str'); insert into t_range_date (id,date_str,context) values(10,'2015-10-01','month-10-str'); insert into t_range_date (id,date_str,context) values(11,'2015-11-01','month-11-str'); Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ERROR 1064 (HY000): Index: 8, Size: 8 ERROR 1064 (HY000): Index: 9, Size: 8 ERROR 1064 (HY000): Index: 10, Size: 8
按照 day分表
<tableRule name="sharding-by-date"> <rule> <columns>create_time</columns> <algorithm>sharding-by-date</algorithm> </rule> </tableRule> <function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2018-01-31</property> <property name="sEndDate">2018-12-31</property> <property name="sPartionDay">2</property> </function> 相关属性: dateFormat:日期格式 sBeginDate:开始日期 sEndDate:结束日期,如果配置了结束日期,则代码数据到达了这个日期的分片后,会重复从开始分片插入 sPartionDay:分区天数,默认值 10 ,从开始日期算起,每个10天一个分区
配置了sEndDate后如果超过了日期,数据都会落到第一个分片数据库里