sharding-jdbc行分片策略默认不支持按分片键的范围查询_inline strategy cannot support this type sharding:

sharding-jdbc行分片策略默认不支持按分片键的范围查询

在开发时，对主键id做了 范围查询。结果遇到如下报错：

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Inline strategy cannot support this type sharding:RangeRouteValue(columnName=id, tableName=table, valueRange=(1‥2))
### The error may exist in file [E:\work\Project\target\classes\mapper\XXXMapper.xml] 
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id     FROM table     where id > 1 and id < 2  
### Cause: java.lang.IllegalStateException: Inline strategy cannot support this type sharding:RangeRouteValue(columnName=id, tableName=table, valueRange=(1‥2))
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
	... 74 more
Caused by: java.lang.IllegalStateException: Inline strategy cannot support this type sharding:RangeRouteValue(columnName=id, tableName=table, valueRange=(1‥2))
	at com.google.common.base.Preconditions.checkState(Preconditions.java:173)
	at org.apache.shardingsphere.core.strategy.route.inline.InlineShardingStrategy.doSharding(InlineShardingStrategy.java:62)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeTables(ShardingStandardRoutingEngine.java:214)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:195)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:121)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:115)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:96)
	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:74)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:69)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
	at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy292.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy291.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 80 more
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

原因：使用行分片策略

原先的sharding-jdbc的分片策略配置是：

sharding:
      binding-tables: tableName
      tables:
        tableName:
          actual-data-nodes: ds0.tableName_$->{0..1}
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: tableName_$->{id % 2}
1
2
3
4
5
6
7
8
9

查阅官方资料、github发现：

上面的配置，使用了主键id作为单分片键，行表达式的分片策略。该分片策略只支持 = 和 in 操作符，并不支持范围查询。如果你想要使用范围查询，你需要配置 开启标准策略。

解决方案：使用标准分片策略

对应配置：

sharding:
  binding-tables: tableName
  tables:
    tableName:
      table-strategy:
        standard:#用于单分片键的标准分片场景
          sharding-column: id  #分片列名称
          precise-algorithm-class-name: com.project.com.PreciseModuloAlgorithm
          #精确分片算法类名称，用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
          range-algorithm-class-name: com.project.com.component.RangeModuloAlgorithm
          #范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
1
2
3
4
5
6
7
8
9
10
11

问题具体可参考，官方github上的issues提问：https://github.com/apache/shardingsphere/issues/4180
其他配置可参考：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/
对于具体的分片算法类，可参考官方github上的example：https://github.com/apache/shardingsphere-example
分片算法类 需要自己根据实际场景进行开发，这里贴出官方example里的实现类：

public final class PreciseModuloAlgorithm implements PreciseShardingAlgorithm<Integer> {

    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % 10 + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

public final class RangeModuloAlgorithm implements RangeShardingAlgorithm<Integer> {
    
    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        int minValue = shardingValue.getValueRange().hasLowerBound() ? shardingValue.getValueRange().lowerEndpoint() : Integer.MIN_VALUE;
        int maxValue = shardingValue.getValueRange().hasUpperBound() ? shardingValue.getValueRange().upperEndpoint() : Integer.MAX_VALUE;
        // 最大值减最小值，得到差
        long range = BigInteger.valueOf(maxValue).subtract(BigInteger.valueOf(minValue)).longValue();
        // 最小值得绝对值除10的余数
        int begin = Math.abs(minValue) % 10;
        // 超过9直接返回可用的表名，这里的9是，自己的分片策略值
        // 假设我的分片策略是：对id除以10，取余数
        if (range > 9) {
            return availableTargetNames;
        }
        // 如果差在分片策略内的，就直接取余数，得到对应的表名
        for (int i = begin; i <= range; i += 1) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

sharding-jdbc 分片策略

分片策略
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。

• 标准分片策略
  对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。

• 复合分片策略
  对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

• 行表达式分片策略
  对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。

• Hint分片策略
  对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

• 不分片策略
  对应NoneShardingStrategy。不分片的策略。