公司有個記錄表，每天有幾百萬的數據，所以我決定按月把他分下表。

用spring整合的。

首先，sharding-sphere不支持自動創建表，所以我提前創建了兩年的表，命名規則 logicTableName + _2019_06

以下是官方文檔上面的分片算法介紹：

?由于這個記錄表在業務層面，只有insert 和 query ，我就用create_datetime作為分表位，查詢是有時間范圍的，所以我選擇復合分片算法。

以下是代碼。

package com.beisheng.sharding;import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Collection; import java.util.Date; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set;import org.apache.commons.lang3.StringUtils; import org.springframework.stereotype.Service;import com.google.common.collect.Lists; import com.google.common.collect.Range; import com.xhlc.pay.util.DatetimeUtil;import io.shardingsphere.api.algorithm.sharding.ListShardingValue; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.RangeShardingValue; import io.shardingsphere.api.algorithm.sharding.ShardingValue; import io.shardingsphere.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm; import lombok.extern.slf4j.Slf4j; /*** * @author OliverAAAAA* @since 2019-6-10 12:00:00**/ @Service("tShopUploadAppInfoRecordTableShardingAlgorithm") @Slf4j public class TShopUploadAppInfoRecordTableShardingAlgorithm implements ComplexKeysShardingAlgorithm {@SuppressWarnings("unchecked")@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> shardingValues) {Collection<String> routTables = new HashSet<String>();if (shardingValues != null) {for (ShardingValue shardingValue : shardingValues) {// eq 條件if (shardingValue instanceof PreciseShardingValue) {PreciseShardingValue<Date> preciseShardingValue = (PreciseShardingValue<Date>) shardingValue;Date value = preciseShardingValue.getValue();String routTable = getRoutTable(preciseShardingValue.getLogicTableName(), value);if (StringUtils.isNotBlank(routTable)) {routTables.add(routTable);}// between 條件} else if (shardingValue instanceof RangeShardingValue) {RangeShardingValue<Date> rangeShardingValue = (RangeShardingValue<Date>) shardingValue;Range<Date> valueRange = rangeShardingValue.getValueRange();Date lowerEnd = (Date) valueRange.lowerEndpoint();Date upperEnd = (Date) valueRange.upperEndpoint();Collection<String> tables = getRoutTable(shardingValue.getLogicTableName(), lowerEnd, upperEnd);if (tables != null && tables.size() > 0) {routTables.addAll(tables);}//多個參數} else if (shardingValue instanceof ListShardingValue) {ListShardingValue<Date> rangeShardingValue = (ListShardingValue<Date>) shardingValue;Collection<Date> values = rangeShardingValue.getValues();for (Date date : values) {String routTable = getRoutTable(shardingValue.getLogicTableName(), date);if (StringUtils.isNotBlank(routTable)) {routTables.add(routTable);}}}if (routTables != null && routTables.size() > 0) {return routTables;}}}throw new UnsupportedOperationException();}private String getRoutTable(String logicTable, Date keyValue) {if (keyValue != null) {String formatDate = DatetimeUtil.formatDate(keyValue, "_YYYY_MM");return logicTable + formatDate;}return null;}public static void main(String[] args) {Calendar cal = Calendar.getInstance();Date start = cal.getTime();String formatDate = DatetimeUtil.formatDate(start, "_YYYY_MM");cal.set(Calendar.MONTH, 10);Date end = cal.getTime();List<String> list = getRangeNameList(start, end);System.out.println(list);System.out.println(formatDate);}private static List<String> getRangeNameList(Date start, Date end) {List<String> result = Lists.newArrayList();Calendar dd = Calendar.getInstance();// 定義日期實例dd.setTime(start);// 設置日期起始時間while (dd.getTime().before(end)) {// 判斷是否到結束日期SimpleDateFormat sdf = new SimpleDateFormat("_YYYY_MM");String str = sdf.format(dd.getTime());result.add(str);dd.add(Calendar.MONTH, 1);// 進行當前日期月份加1}return result;}private Collection<String> getRoutTable(String logicTable, Date lowerEnd, Date upperEnd) {Set<String> routTables = new HashSet<String>();if (lowerEnd != null && upperEnd != null) {List<String> rangeNameList = getRangeNameList(lowerEnd, upperEnd);for (String string : rangeNameList) {routTables.add(logicTable + string);}}return routTables;}}

下面是配置：

<bean id="tShopUploadAppInfoRecordTableShardingAlgorithm" class="com.beisheng.sharding.TShopUploadAppInfoRecordTableShardingAlgorithm" /><sharding:complex-strategy id="strategyTableTShopUploadAppInfoRecord" sharding-columns="create_datetime" algorithm-ref="tShopUploadAppInfoRecordTableShardingAlgorithm" /><sharding:data-source id="dynamicDataSource"><sharding:sharding-rule data-source-names="ds_cdf_master_0,ds_cdf_slave_0"default-data-source-name="ds_cdf_master_0"><sharding:master-slave-rules><sharding:master-slave-rule id="ds_ms0"master-data-source-name="ds_cdf_master_0" slave-data-source-names="ds_cdf_slave_0"strategy-type="RANDOM" /></sharding:master-slave-rules><sharding:table-rules><sharding:table-rule logic-table="t_shop_upload_app_info_record"table-strategy-ref="strategyTableTShopUploadAppInfoRecord" /></sharding:table-rules><sharding:binding-table-rules><sharding:binding-table-rulelogic-tables="t_shop_upload_app_info_record" /></sharding:binding-table-rules></sharding:sharding-rule><sharding:props><prop key="sql.show">${shard.sql.show}</prop></sharding:props></sharding:data-source>

純原創，轉載請注明出處~

?

本人最近開了一個公眾號，會講一些常用的技術，以及面試題，歡迎關注

掃碼關注，每天獲取最前沿的互聯網知識~

總結

以上是生活随笔為你收集整理的sharding-sphere按月动态分表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。