领导：数据太多了，你按照接入方给我把xx表给我分表下！ 以前的代码不准改哈
今天领导让我做数据分表。。。数据的确有点多哈（多吗？8百万条？不多吧，不过以后接入方多了，估计会很多吧）
然后随便看了下 sharding、mycat..
emmmm 我是按照具体字段去分割，不需要太复杂，如果表不存在 报错就行，没有太多复杂的东西，所以。。。
自己写一个吧
领导需求列出来了
- 你得能按照某个字段分表
- 你不能改代码
不能改代码？还得分表？！！！
开始分析框架：用的Mybatis-plus
哦~ 可以搞！！！
用到一个玩意：DynamicTableNameInnerInterceptor
不知道有人会不会用到这个，如果用到的 可以评论 ，有时间我回回复，所以。。。 我就贴一下主要代码
至于哪个地方用这个 Interceptor emmmm 评论区问我下
@Slf4j
@Component("customerDynamicTableNameInnerInterceptor")
public class CustomerDynamicTableNameInnerInterceptor extends DynamicTableNameInnerInterceptor {
private final Map<String, TableNameHandler> tableNameHandlerMap = new ConcurrentHashMap<>();
private final Map<String, String> tableNameMap = new ConcurrentHashMap<String, String>() {
{
put("xxxx", "xxxx");
put("xxxxxxx", "xxxxxx");
put("xxxxxxxx", "xxxx");
put("xxxxxxxxxx", "xxxxx");
}
};
private String customerChangeTable(String sql, SqlCommandType type, List<ParameterMapping> parameterMappings, Object parameterObject) {
TableNameParser parser = new TableNameParser(sql);
List<TableNameParser.SqlToken> names = new ArrayList<>();
parser.accept(names::add);
StringBuilder builder = new StringBuilder();
int last = 0;
for (TableNameParser.SqlToken name : names) {
int start = name.getStart();
if (start != last) {
builder.append(sql, last, start);
String value = name.getValue();
TableNameHandler handler = tableNameHandlerMap.get(value);
if (handler != null) {
builder.append(handler.dynamicTableName(sql, value));
} else {
// 此处判断是否 需要修改 table名称
if (tableNameMap.containsKey(value)) {
builder.append(tableNameHandler(sql, value, type, parameterMappings, parameterObject));
} else {
builder.append(value);
}
}
}
last = name.getEnd();
}
if (last != sql.length()) {
builder.append(sql.substring(last));
}
return builder.toString();
}
private final ObjectFactory objectFactory = new DefaultObjectFactory();
private final ObjectWrapperFactory objectWrapperFactory = new DefaultObjectWrapperFactory();
private final ReflectorFactory reflectorFactory = new DefaultReflectorFactory();
private String tableNameHandler(String sql, String tableName, SqlCommandType type, List<ParameterMapping> parameterMappings, Object parameterObject) {
String tableFiled = tableNameMap.get(tableName);
int index = sql.indexOf(tableFiled);
if (index == -1) {
return tableName;
}
if (type == SqlCommandType.SELECT || type == SqlCommandType.UPDATE || type == SqlCommandType.DELETE) {
index = sql.indexOf(tableFiled + " = ?");
if (index == -1) return tableName;
// 1. 获取当前 这个index 是多少个问号呀
int position = getPlaceholderPosition(sql, index + ((tableFiled + " = ?").length()) - 1, "?");
return getTableName(tableName, position, parameterMappings, parameterObject);
} else if (type == SqlCommandType.INSERT) {
// 查询出当前 对应的 index 是哪一个
int position = getInsertPosition(getParts(sql), tableFiled);
return getTableName(tableName, position, parameterMappings, parameterObject);
}
return tableName;
}
private String getTableName(String tableName, int position, List<ParameterMapping> parameterMappings, Object parameterObject) {
if (position <= 0) {
return tableName;
}
ParameterMapping parameterMapping = parameterMappings.get(position - 1);
String property = parameterMapping.getProperty();
// 从 parameterObject中获取
MetaObject metaObject = MetaObject.forObject(parameterObject, objectFactory, objectWrapperFactory, reflectorFactory);
Object value = metaObject.getValue(property);
return tableName + "_" + value;
}
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
mpBs.sql(this.customerChangeTable(mpBs.sql(), SqlCommandType.SELECT, mpBs.parameterMappings(), mpBs.parameterObject()));
}
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(this.customerChangeTable(mpBs.sql(), sct, mpBs.parameterMappings(), mpBs.parameterObject()));
}
}
/**
* 找到SQL语句中所有?的下标
*
* @param sql SQL语句
* @return 包含所有?下标的列表
*/
public static List<Integer> findPlaceholderIndexes(String sql, String placeholder) {
List<Integer> indexes = new ArrayList<>();
int index = 0;
while ((index = sql.indexOf(placeholder, index)) != -1) {
indexes.add(index);
index++; // 移动到下一个字符，避免重复计算
}
return indexes;
}
/**
* 判断给定的下标是第几个?占位符
*
* @param sql SQL语句
* @param givenIndex 给定的下标
* @param placeholder 占位符
* @return 给定下标是第几个?占位符，如果不存在则返回-1
*/
public static int getPlaceholderPosition(String sql, int givenIndex, String placeholder) {
List<Integer> indexes = findPlaceholderIndexes(sql, placeholder);
for (int i = 0; i < indexes.size(); i++) {
if (indexes.get(i) == givenIndex) {
return i + 1; // 返回的是第几个占位符，所以加1
}
}
return -1; // 如果给定的下标不在列表中，返回-1
}
/**
* 找到SQL语句中所有?的下标
*
* @param sql SQL语句
* @return 包含所有?下标的列表
*/
public static List<Integer> findPlaceholderIndexes(String sql) {
List<Integer> indexes = new ArrayList<>();
int index = 0;
while ((index = sql.indexOf('?', index)) != -1) {
indexes.add(index);
index++; // 移动到下一个字符，避免重复计算
}
return indexes;
}
/**
* 根据列名找到对应的?占位符的下标
*
* @param sql SQL语句
* @param columnName 列名
* @return ?占位符的下标，如果未找到则返回-1
*/
public static int findPlaceholderIndexByColumn(String sql, String columnName) {
String[] parts = sql.split("\\s*,\\s*");
for (int i = 0; i < parts.length; i++) {
if (parts[i].trim().contains(columnName)) {
List<Integer> indexes = findPlaceholderIndexes(sql);
if (i < indexes.size()) {
return indexes.get(i);
}
break;
}
}
return -1;
}
public static List<String> getParts(String sql) {
int i = sql.toLowerCase().indexOf("value");
String s1 = sql.substring(0, i);
String[] split = s1.split("\\s*,\\s*");
List<String> returnList = new ArrayList<>(Arrays.asList(split));
String s2 = sql.substring(i);
boolean flag = false;
int startIndex = 0;
for (int index = 0; index < s2.toCharArray().length; index++) {
char c = s2.charAt(index);
if (c == '\'') {
flag = !flag;
}
if (!flag) {
if (c == ',' || c == ')') {
returnList.add(s2.substring(startIndex, index));
startIndex = index + 1;
}
}
}
return returnList;
}
public static int getInsertPosition(List<String> parts, String tableFiled) {
String placeholder = "?";
int positionIndex = -1;
int startIndex = 0;
int placeholderIndex = 0;
int returnIndex = -1;
boolean startFlag = false;
for (int i = 0; i < parts.size(); i++) {
String part = parts.get(i);
if (part.contains(tableFiled)) {
// index
positionIndex = i + 1;
}
if (part.toLowerCase().contains("value")) {
startFlag = true;
}
if (startFlag) {
startIndex++;
}
if (part.contains(placeholder)) {
placeholderIndex++;
}
if (startIndex == positionIndex) {
returnIndex = placeholderIndex;
}
}
return returnIndex;
}
}
最后 祝大家在这个新年的钟声里，愿你的生活中充满欢笑，事业上收获满满，爱情里甜蜜温馨。新年快乐！
