1.背景描述: 用分布式数据库中间件ShardingSphere对数据表User按照身份证号certNo(假定全为数字)最后一位进行分表
2.问题: 无法对数据表进行修改,
3.解答: ShardingSphere禁止对分表字段certNo进行修改,所以我设置了certNo为不可修改字段,导致ShardingSphere没有路由成功;shardingConditions获取不到值certNo会对库中所有表(User0, User1, User2 … )路由,则会根据主键id查出多条记录,无法定位某条记录进行修改
4.解决方法: 将certNo设置为主键或者,不让id自增将certNo的值赋给id并设id为分表字段
public class User{
private Integer id;
private String certNo;
//其他字段
}
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user${0..9}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=cert_no
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user${Long.parseLong(cert_no) % 10}
package org.apache.shardingsphere.core.route.router.sharding;
/**
* Route SQL.
*
* @param logicSQL logic SQL
* @param parameters SQL parameters
* @param sqlStatement SQL statement
* @return parse result
*/
@SuppressWarnings("unchecked")
public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
Optional<ShardingStatementValidator> shardingStatementValidator = ShardingStatementValidatorFactory.newInstance(sqlStatement);
if (shardingStatementValidator.isPresent()) {
shardingStatementValidator.get().validate(shardingRule, sqlStatement, parameters);
}
SQLStatementContext sqlStatementContext = SQLStatementContextFactory.newInstance(metaData.getRelationMetas(), logicSQL, parameters, sqlStatement);
Optional<GeneratedKey> generatedKey = sqlStatement instanceof InsertStatement
? GeneratedKey.getGenerateKey(shardingRule, metaData.getTables(), parameters, (InsertStatement) sqlStatement) : Optional.<GeneratedKey>absent();
//这里的shardingConditions获取不到值certNo
ShardingConditions shardingConditions = getShardingConditions(parameters, sqlStatementContext, generatedKey.orNull(), metaData.getRelationMetas());
boolean needMergeShardingValues = isNeedMergeShardingValues(sqlStatementContext);
if (sqlStatementContext.getSqlStatement() instanceof DMLStatement && needMergeShardingValues) {
checkSubqueryShardingValues(sqlStatementContext, shardingConditions);
mergeShardingConditions(shardingConditions);
}
RoutingEngine routingEngine = RoutingEngineFactory.newInstance(shardingRule, metaData, sqlStatementContext, shardingConditions);
RoutingResult routingResult = routingEngine.route();
if (needMergeShardingValues) {
Preconditions.checkState(1 == routingResult.getRoutingUnits().size(), "Must have one sharding with subquery.");
}
SQLRouteResult result = new SQLRouteResult(sqlStatementContext, shardingConditions, generatedKey.orNull());
result.setRoutingResult(routingResult);
if (sqlStatementContext instanceof InsertSQLStatementContext) {
setGeneratedValues(result);
}
return result;
}