SQL分片工具类

SQL分片工具类(SqlShardingUtil)提供数据库查询的智能分片功能，支持数字和字符串两种字段类型的分片策略。对于数字字段，可以指定分片数量均匀划分数值区间；对于字符串字段，则按照ASCII字符范围自动划分。工具类确保分片后的SQL语句完整覆盖数据范围且不重复，同时支持附加WHERE条件。实现原理是通过计算分片边界值，构建区间查询条件，特别处理字符串类型的开放式结尾区间，保证数据完整性。该工具适用于大数据量查询的性能优化场景。

import java.util.ArrayList;
import java.util.List;/*** SQL分片工具类* 支持 minVal 和 maxVal 为空的处理* 确保分片数据不重复且完整覆盖*/
public class SqlShardingUtil {public enum FieldType {NUMBER, STRING}/*** 生成分片SQL列表** @param baseSql    基础SQL（不含WHERE）* @param where      额外过滤条件，如"status = 1"，可传空或null* @param splitField 分片字段名* @param fieldType  分片字段类型* @param minVal     分片起始值，数字传Long，字符串传单字符字符串* @param maxVal     分片结束值，数字传Long，字符串传单字符字符串* @param shardCount 分片数量（数字分片生效，字符串分片忽略）* @return 分片SQL语句列表*/public static List<String> splitSQL(String baseSql, String where, String splitField, FieldType fieldType,Object minVal, Object maxVal, int shardCount) {List<String> result = new ArrayList<>();String baseWhere = (where == null || where.trim().isEmpty()) ? "WHERE 1=1" : (where.trim().toUpperCase().startsWith("WHERE") ? where.trim() : "WHERE " + where.trim());if (fieldType == FieldType.NUMBER) {if (!(minVal instanceof Number) || !(maxVal instanceof Number)) {throw new IllegalArgumentException("NUMBER类型分片，minVal和maxVal必须是数字");}long min = ((Number) minVal).longValue();long max = ((Number) maxVal).longValue();if (max < min) {throw new IllegalArgumentException("maxVal不能小于minVal");}if (shardCount <= 0) {shardCount = 1; // 至少一个分片}long total = max - min + 1;long step = (long) Math.ceil((double) total / shardCount);for (long start = min; start <= max; start += step) {long end = Math.min(start + step, max + 1);String condition = String.format("%s >= %d AND %s < %d", splitField, start, splitField, end);result.add(baseSql + " " + baseWhere + " AND " + condition);}} else if (fieldType == FieldType.STRING) {if (!(minVal instanceof String) || !(maxVal instanceof String)) {throw new IllegalArgumentException("STRING类型分片，minVal和maxVal必须是字符串");}if (((String) minVal).length() == 0 || ((String) maxVal).length() == 0) {throw new IllegalArgumentException("minVal和maxVal字符串不能为空");}char minChar = ((String) minVal).charAt(0);char maxChar = ((String) maxVal).charAt(0);if (maxChar < minChar) {throw new IllegalArgumentException("maxVal字符不能小于minVal字符");}// 忽略shardCount，按字符区间分片List<Character> asciiChars = getCharRange(minChar, maxChar);for (int i = 0; i < asciiChars.size() - 1; i++) {char start = asciiChars.get(i);char end = asciiChars.get(i + 1);String condition = String.format("%s >= '%c' AND %s < '%c'", splitField, start, splitField, end);result.add(baseSql + " " + baseWhere + " AND " + condition);}// 最后一段闭区间，确保不漏数据char last = asciiChars.get(asciiChars.size() - 1);String lastCondition = String.format("%s >= '%c'", splitField, last);result.add(baseSql + " " + baseWhere + " AND " + lastCondition);} else {throw new UnsupportedOperationException("不支持的FieldType：" + fieldType);}return result;}/*** 获取指定区间内的有效字符列表（包含数字、大小写字母及常用符号）** @param start 起始字符（包含）* @param end   结束字符（包含）* @return 字符列表*/private static List<Character> getCharRange(char start, char end) {List<Character> list = new ArrayList<>();for (char c = start; c <= end; c++) {if (Character.isDigit(c) || Character.isLetter(c) || isSymbol(c)) {list.add(c);}}return list;}/*** 判断是否为常用符号*/private static boolean isSymbol(char c) {return "!@#$%^&*()_+-=[]{}|;:',.<>?/`~\"\\ ".indexOf(c) >= 0;}// 示例 main 方法public static void main(String[] args) {String baseSql = "SELECT * FROM user_table";String where = "status = 1";String splitField = "user_id";// 数字分片示例List<String> numberShards = splitSQL(baseSql, where, splitField, FieldType.NUMBER, 1, 100, 5);System.out.println("数字分片：");numberShards.forEach(System.out::println);// 字符分片示例，覆盖0~Z（数字和大写字母）List<String> stringShards = splitSQL(baseSql, where, "username", FieldType.STRING, "0", "Z", 0);System.out.println("\n字符分片：");stringShards.forEach(System.out::println);}