数据库分表遇到的问题

数据库分表的自定方法

分表的表名是表名_年份

处理数据分表精度问题

    @Override
    protected String getShardingTableNameSuffix(Date shardingColumnValue) {
        String yyyyMM = DateUtils.date2String(shardingColumnValue, DateFormatType.SIMPLE_DATE_FORMAT_YEAR_MON);
        String yyyy = yyyyMM.substring(0,4);
        return "_" + yyyy;
    }

处理数据库分表范围问题

    @Override
    protected List<String> getTableNamesLess(Date upper, Collection<String> shardingTableNameList) {
        List<String> result = new ArrayList<>();
        String upperSuffix = DateUtils.date2String(upper, DateFormatType.SIMPLE_DATE_FORMAT_YEAR_MON);
        upperSuffix = upperSuffix.substring(0,4);
        String finalUpperSuffix = upperSuffix;
        shardingTableNameList.forEach(tableName -> {
            String tableSuffix = tableName.substring(tableName.lastIndexOf("_") + 1);
            if (Integer.parseInt(tableSuffix) <= (Integer.parseInt(finalUpperSuffix))) {
                result.add(tableName);
            }
        });
        return result;
    }

    @Override
    protected List<String> getTableNamesGreater(Date lower, Collection<String> shardingTableNameList) {
        List<String> result = new ArrayList<>();
        String lowerSuffix = DateUtils.date2String(lower, DateFormatType.SIMPLE_DATE_FORMAT_YEAR_MON);
        lowerSuffix= lowerSuffix.substring(0,4);
        String finalLowerSuffix = lowerSuffix;
        shardingTableNameList.forEach(tableName -> {
            String tableSuffix = tableName.substring(tableName.lastIndexOf("_") + 1);
            if (Integer.parseInt(tableSuffix) >= (Integer.parseInt(finalLowerSuffix))) {
                result.add(tableName);
            }
        });
        return result;
    }

    @Override
    protected List<String> getTableNamesGreaterAndLess(Date lower, Date upper, Collection<String> shardingTableNameList) {
        List<String> result = new ArrayList<>();
        String lowerSuffix = DateUtils.date2String(lower, DateFormatType.SIMPLE_DATE_FORMAT_YEAR_MON);
        String upperSuffix = DateUtils.date2String(upper, DateFormatType.SIMPLE_DATE_FORMAT_YEAR_MON);
        lowerSuffix= lowerSuffix.substring(0,4);
        upperSuffix = upperSuffix.substring(0,4);
        String finalLowerSuffix = lowerSuffix;
        String finalUpperSuffix = upperSuffix;
        shardingTableNameList.forEach(tableName -> {
            String tableSuffix = tableName.substring(tableName.lastIndexOf("_") + 1);
            if (Integer.valueOf(tableSuffix) >= (Integer.parseInt(finalLowerSuffix)) && Integer.valueOf(tableSuffix) <= (Integer.parseInt(finalUpperSuffix))) {
                result.add(tableName);
            }
        });
        return result;
    }

配置分表策略

    <sharding:standard-strategy id="相关ShardingStrategy" sharding-column="分表字段" precise-algorithm-ref="XXXXStatsPreciseShardingAlgorithm" range-algorithm-ref="XXXXXStatsRangeShardingAlgorithm"/>


    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="数据库名" default-data-source-name="数据库名">
            <sharding:table-rules>
                <sharding:table-rule logic-table="表名" actual-data-nodes="节点" table-strategy-ref="相关ShardingStrategy" />
            </sharding:table-rules>
        </sharding:sharding-rule>
        <sharding:props>
            <prop key="sql.show">true</prop>
        </sharding:props>

分表遇到的坑

通过id查找，更新，删除就会报错（不鞥你确定去哪张表中去查找数据）
接下来我介绍一下mybatis的方式处理的问题

@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
    @Signature(type = StatementHandler.class, method = "parameterize", args = {Statement.class})
})
public class CustomInterceptor implements Interceptor {


    private static final String STATEMENT_HANDLER_METHOD = "prepare";

    private static final String PARAMETERIZE_HANDLER_METHOD = "parameterize";


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String method = invocation.getMethod().getName();
        switch (method) {
            case STATEMENT_HANDLER_METHOD:
                return prepare(invocation);
            case PARAMETERIZE_HANDLER_METHOD:
                return parameterize(invocation);
        }
        return invocation.proceed();
    }

    @SneakyThrows
    private Object prepare(Invocation invocation) {
        RoutingStatementHandler routingStatementHandler = (RoutingStatementHandler) PluginUtils
            .realTarget(invocation.getTarget());
        // 通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
        // 优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，
        // 同时它支持对JavaBean、Collection、Map三种类型对象的操作。
        MetaObject metaObject = MetaObject.forObject(routingStatementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
            SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        // 获取mapper的Statement对象,它描述的是mapper对象的配置
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

        if (!checkMethodAddSharedingField(mappedStatement)) {
          return   invocation.proceed();
        }

        // 在sql尾部拼接分表所属属性
        BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
        String sqlStr = boundSql.getSql()+ " and " + "date_time" + " = ?";

        ReflectionUtils.setProperty(boundSql,"sql",sqlStr);

        metaObject.setValue("delegate.boundSql", boundSql);
        return invocation.proceed();
    }
   }
    ```