前言

最近在工作中使用ShardingSphere实现mysql数据分表，在此记录一下遇到的一些坑。

ShardingSphere简介

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，提供标准化的数据水平扩展、分布式事务和分布式治理等功能。

更新分片字段问题

本次使用Sharding-JDBC，并只分表，不分库（避开了跨库导致的分布式事务问题，当然ShardingSphere也支持XA、Seata等分布式事务）。

当执行更新分片字段的update语句时，报错如下：

org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [order], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@2b877a54].

	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:59)
	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:42)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.lambda$decorate$0(ShardingRouteDecorator.java:61)
	at java.util.Optional.ifPresent(Optional.java:159)
        ...

根据调用堆栈找到相应源码

public final class ShardingUpdateStatementValidator implements ShardingStatementValidator<UpdateStatement> {
    
    @Override
    public void validate(final ShardingRule shardingRule, final UpdateStatement sqlStatement, final List<Object> parameters) {
        String tableName = sqlStatement.getTables().iterator().next().getTableName().getIdentifier().getValue();
        //遍历所有的set声明
        for (AssignmentSegment each : sqlStatement.getSetAssignment().getAssignments()) {
            String shardingColumn = each.getColumn().getIdentifier().getValue();
            if (shardingRule.isShardingColumn(shardingColumn, tableName)) {
                //当set语句段为分片键时进入
                Optional<Object> shardingColumnSetAssignmentValue = getShardingColumnSetAssignmentValue(each, parameters);
                Optional<Object> shardingValue = Optional.empty();
                Optional<WhereSegment> whereSegmentOptional = sqlStatement.getWhere();
                if (whereSegmentOptional.isPresent()) {
                    shardingValue = getShardingValue(whereSegmentOptional.get(), parameters, shardingColumn);
                }
                if (shardingColumnSetAssignmentValue.isPresent() && shardingValue.isPresent() && shardingColumnSetAssignmentValue.get().equals(shardingValue.get())) {
                //当set子句中设置的的新值和where子句中的旧值相同可跳出
                    continue;
                }
                //抛出异常
                throw new ShardingSphereException("Can not update sharding key, logic table: [%s], column: [%s].", tableName, each);
            }
        }
    }

分析源码发现，当更新分片字段时，只有当set子句中设置的新值等于where子句中给定的旧值时，才不会抛出异常，其他更新分片字段的情况都是不允许的!

between and 解析问题

由于当时是给一个旧项目做改造，做分表这块改造的时候，原始的业务代码，sql语句等等是没有动的，直接将原来的DataSource替换为新配置的ShardingDataSource.

但是在测试阶段，发现了一个奇怪的报错：

ava.lang.ClassCastException: org.apache.shardingsphere.sql.parser.sql.segment.dml.item.ExpressionProjectionSegment cannot be cast to org.apache.shardingsphere.sql.parser.sql.segment.dml.column.ColumnSegment

	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.createBetweenSegment(MySQLVisitor.java:351)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitPredicate(MySQLVisitor.java:313)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitPredicate(MySQLVisitor.java:121)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$PredicateContext.accept(MySQLStatementParser.java:11690)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitBooleanPrimary(MySQLVisitor.java:273)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitBooleanPrimary(MySQLVisitor.java:121)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$BooleanPrimaryContext.accept(MySQLStatementParser.java:11463)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:258)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:121)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ExprContext.accept(MySQLStatementParser.java:11241)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:261)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:121)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ExprContext.accept(MySQLStatementParser.java:11241)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)

业务sql类似于这样：

select * from order where compId = 2 and DATE_FORMAT(updateTime,'%Y-%m-%d') between DATE_FORMAT('20210616','%Y-%m-%d') and DATE_FORMAT('20210617','%Y-%m-%d')

留意后面的between and子句，使用了函数

直接源码定位一波（当前版本：4.1.1）：

private PredicateSegment createBetweenSegment(final PredicateContext ctx) {
        
        ColumnSegment column = (ColumnSegment) visit(ctx.bitExpr(0));//这一行代码类型转换异常
        ExpressionSegment between = (ExpressionSegment) visit(ctx.bitExpr(1));
        ExpressionSegment and = (ExpressionSegment) visit(ctx.predicate());
        return new PredicateSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), column, new PredicateBetweenRightValue(between, and));
    }

打断点调试：

微信图片_20210627212923.png

直接调用visit(ctx.bitExpr(0))发现返回的类型是ExpressionProjectionSegment,强制类型转换为ColumnSegment导致报错。

结合官方文档和代码上下文可知，这部分代码是sql解析引擎将sql语句解析为抽象语法树的过程。但是在createBetweenSegment方法中，默认认为 between and语句中between关键字之前的语句段为列段（ColumnSegment)，当在between之前使用表达式时，获取到的语法树节点为表达式段(ExpressionProjectionSegment)。

当时的解决方案为改写sql不使用表达式来规避这个问题，后续发现在最新版本(5.0.0-alpha)中已经修复了这个问题。

private BetweenExpression createBetweenSegment(final PredicateContext ctx) {
        ExpressionSegment left = (ExpressionSegment) visit(ctx.bitExpr(0));
        ExpressionSegment between = (ExpressionSegment) visit(ctx.bitExpr(1));
        ExpressionSegment and = (ExpressionSegment) visit(ctx.predicate());
        boolean not = null != ctx.NOT();
        return new BetweenExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), left, between, and, not);
    }

解析between段时，默认认为between之前为表达式段。

总结

1.使用update语句更新分片字段时，只允许将分片字段set为和旧值（旧值需要在where子句中）相同

2. 4.1.1版本between 关键字之前不支持表达式，在5.0.0-alpha提供支持

ShardingSphere踩坑合集（一）

前言

ShardingSphere简介

更新分片字段问题

between and 解析问题

总结