固定分片hash

• schema.xml配置分片表，数据节点，数据库实例

• rule.xml配置分片规则

• ./mycat restart重启服务

• mycat-definitive-guide10.5.2 固定分片 hash 算法
  

重点

• 本条规则是取id的二进制低10位，即id的二进制&1111111111，会产生1024个数值（[0-1023]）

• 此算法的优点在于如果按照 10 进制取模运算，在连续插入 1-10 时候 1-10 会被分到 1-10 个分片，增 大了插入的事务控制难度，而此算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度。

• 2,1和256,512分成3个分片，分别为256+256+512=1024

schema.xml

<schema name="parking" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
    <table name="t_partition_by_long" dataNode="dn1,dn2,dn3" rule="PartitionByLongDemo" />
</schema>

<dataNode name="dn1" dataHost="aliyun" database="parking_db" />
<dataNode name="dn2" dataHost="aliyun" database="parking_db2" />
<dataNode name="dn3" dataHost="aliyun" database="parking_db3" />

<dataHost name="aliyun" maxCon="20" minCon="3" balance="0"
 writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="jdbc:mysql://120.26.86.107:3306" user="root"
 password="xxx">
        </writeHost> 
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

rule.xml

<tableRule name="PartitionByLongDemo">
     <rule> 
         <columns>refer_id</columns>
         <algorithm>PartitionByLongDemoAlgorithm</algorithm>
    </rule>
</tableRule>
<!-- 2,1和256,512分成3个分片，分别为256+256+512=1024 -->
<function name="PartitionByLongDemoAlgorithm" class="io.mycat.route.function.PartitionByLong">
     <property name="partitionCount">2,1</property>
     <property name="partitionLength">256,512</property>
</function>

sql

CREATE TABLE `t_partition_by_long` (
  `id` int NOT NULL AUTO_INCREMENT,
  `refer_id` int DEFAULT NULL COMMENT '引用ID',
  `refer_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '引用名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

# dn1
insert into t_partition_by_long(id, refer_id, refer_name) values (1, 0, "256-1-nacos1");
insert into t_partition_by_long(id, refer_id, refer_name) values (2, 255, "256-1-nacos2");
insert into t_partition_by_long(id, refer_id, refer_name) values (3, 1024, "256-1-nacos3");
insert into t_partition_by_long(id, refer_id, refer_name) values (4, 1279, "256-1-nacos4");

# dn2
insert into t_partition_by_long(id, refer_id, refer_name) values (1, 256, "256-2-nacos1");
insert into t_partition_by_long(id, refer_id, refer_name) values (2, 511, "256-2-nacos2");
insert into t_partition_by_long(id, refer_id, refer_name) values (3, 1280, "256-2-nacos3");
insert into t_partition_by_long(id, refer_id, refer_name) values (4, 1535, "256-2-nacos4");

# dn3
insert into t_partition_by_long(id, refer_id, refer_name) values (1, 512, "512-3-nacos1");
insert into t_partition_by_long(id, refer_id, refer_name) values (2, 1023, "512-3-nacos2");
insert into t_partition_by_long(id, refer_id, refer_name) values (3, 1536, "512-3-nacos3");
insert into t_partition_by_long(id, refer_id, refer_name) values (4, 2047, "512-3-nacos4");

bug

java.lang.StringIndexOutOfBoundsException: String index out of range: -1

原因: navicat客户端连接mycat执行sql语句中不能有注释
方案：去除注释