Mycat 源码修改-实现分表规则：按天分表和取摸分表查询
来源：互联网 发布：sql update 条件 编辑：程序博客网 时间：2024/06/06 07:48
Mycat 源码修改-实现分表规则：按天和取摸功能。之前修改过源码，发现其实没什么高深的只需要自己耐心点，多花点时间去调试就可以做到了。通过调试，找到自己想要改的地方，这是关键的：在代码中表现为修改相应的配置规则、相关的路由直达问题。按天分表的在我的前一篇博客（Mycat 开发调试环境配置-开启二次开发之路）中有讲述，只是这里的规则改了下，所以不再主讲按天分表。这里重点讲的是取摸分表查询。
TableConfig构造改进
新增io.mycat.config.model.TableConfig.java类属性
private final String subTables;private final String subTableWay;private final String deliveryField;
io.mycat.config.model.TableConfig.java构造方法
public TableConfig(String name, String primaryKey, boolean autoIncrement,boolean needAddLimit, int tableType,String dataNode,Set<String> dbType, RuleConfig rule, boolean ruleRequired,TableConfig parentTC, boolean isChildTable, String joinKey,String parentKey,String subTables,String subTableWay,String deliveryField) {if (name == null) {throw new IllegalArgumentException("table name is null");} else if (dataNode == null) {throw new IllegalArgumentException("dataNode name is null");}this.subTables=subTables;this.subTableWay=subTableWay;this.deliveryField=deliveryField;this.primaryKey = primaryKey;this.autoIncrement = autoIncrement;this.needAddLimit=needAddLimit;this.tableType = tableType;this.dbTypes=dbType;if (ruleRequired && rule == null) {throw new IllegalArgumentException("ruleRequired but rule is null");}this.name = name.toUpperCase();String theDataNodes[] = SplitUtil.split(dataNode, ',', '$', '-');if (theDataNodes == null || theDataNodes.length <= 0) {throw new IllegalArgumentException("invalid table dataNodes: " + dataNode);}dataNodes = new ArrayList<String>(theDataNodes.length);for (String dn : theDataNodes) {dataNodes.add(dn);}if(subTables!=null && !subTables.equals("")){if(subTableWay==null ||subTableWay.equals("")||subTableWay.equals("DEFAULT")){// 原始按顺序分表方式String sTables[] = SplitUtil.split(subTables, ',', '$', '-');if (sTables == null || sTables.length <= 0) {throw new IllegalArgumentException("invalid table subTables");}this.distTables = new ArrayList<String>(sTables.length);for (String table : sTables) {LOGGER.info("默认分表信息——dataNode："+dataNode+",table:"+table); distTables.add(table);}}else if(subTableWay.equals("BYDELIVERY")){// 取摸分表方式String sTables[] = SplitUtil.split(subTables, ',', '$', '-');if (sTables == null || sTables.length <= 0) {throw new IllegalArgumentException("invalid table subTables");}if(deliveryField==null||deliveryField.equals("")){throw new IllegalArgumentException("invalid table deliveryField");}this.distTables = new ArrayList<String>(sTables.length);for (String table : sTables) {LOGGER.info("取摸分表信息——dataNode："+dataNode+",table:"+table); distTables.add(table);}}else if(subTableWay.equals("BYDATE")){// 按日分表方式String str=subTables.substring(subTables.indexOf("$")+1, subTables.length());String splitChar="-";if(str==null||str.length() <= 0||!str.contains(splitChar)){throw new IllegalArgumentException("invalid table subTables:you should set date like $20170717-20180716 or $20170717-?");}String [] strDates=str.split("-");if(strDates.length!=2) {throw new IllegalArgumentException("invalid table subTables:you should set date like $20170717-20180716 or $20170717-?");}String dateStart=strDates[0];//开始日期String dateEnd=strDates[1];// 结束日期if(dateStart.equals(dateEnd)){throw new IllegalArgumentException("invalid table subTables:you should set date like $20170717-20180716 or $20170717-?");}String dateFormat="yyyyMMdd";Date begin=null;Date end=null;try {begin=DateUtils.parseDate(dateStart, dateFormat);end=DateUtils.parseDate(dateEnd.equals("?")?DateUtils.getStrFormTime(dateFormat, new Date()):dateEnd, dateFormat);} catch (ParseException e) {throw new IllegalArgumentException("invalid table subTables:you should set date like $20170717-20180716 or $20170717-?");}if(begin.getTime()>end.getTime()){throw new IllegalArgumentException("invalid table subTables:start date can't bigger than end date");}// 生成对应的按日期表集合List<Date> dates=DateUtils.getBetweenDates(begin, end);this.distTables = new ArrayList<String>(dates.size());String tablePrex=subTables.substring(0, subTables.indexOf("$"));for (Date date : dates) {String table=tablePrex+DateUtils.getStrFormTime(dateFormat, date);LOGGER.info("按天分表信息——dataNode："+dataNode+",table:"+table); distTables.add(table);}}else{throw new IllegalArgumentException("invalid table subTableWay");}}else{this.distTables = new ArrayList<String>();}this.rule = rule;this.partitionColumn = (rule == null) ? null : rule.getColumn();partionKeyIsPrimaryKey=(partitionColumn==null)?primaryKey==null:partitionColumn.equals(primaryKey);this.ruleRequired = ruleRequired;this.childTable = isChildTable;this.parentTC = parentTC;this.joinKey = joinKey;this.parentKey = parentKey;if (parentTC != null) {locateRTableKeySql = genLocateRootParentSQL();secondLevel = (parentTC.parentTC == null);} else {locateRTableKeySql = null;secondLevel = false;}}注：建议构造重写，构造函数涉及io.mycat.config.loader.xml.XMLSchemaLoader.java类的修改。Schema配置新增Table标签属性
schema.dtd定义table标签新增属性
新增属性字段：subTableWay和deliveryField
<!ELEMENT table (property*,(childTable*))><!ATTLIST table name CDATA #REQUIRED><!ATTLIST table nameSuffix CDATA #IMPLIED><!ATTLIST table dataNode CDATA #REQUIRED><!ATTLIST table rule NMTOKEN #IMPLIED><!ATTLIST table ruleRequired NMTOKEN #IMPLIED><!ATTLIST table primaryKey NMTOKEN #IMPLIED><!ATTLIST table subTables CDATA #IMPLIED><!ATTLIST table subTableWay CDATA #IMPLIED><!ATTLIST table deliveryField CDATA #IMPLIED><!ATTLIST table autoIncrement NMTOKEN #IMPLIED><!ATTLIST table needAddLimit NMTOKEN #IMPLIED><!ATTLIST table type NMTOKEN #IMPLIED>
schema.xml连上MongoDB的配置
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"> <!-- **********默认分表 start --> <!-- subTableWay: DEFAULT(默认) BYDATE（按天） BYDELIVERY(取摸)--> <!-- <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$1-5" dataNode="dn1" /> --> <!-- <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$1-5" subTableWay="DEFAULT" dataNode="dn1" /> --> <!-- **********默认分表 end --> <!-- **********按天分表 start --> <!-- subTableWay: DEFAULT(默认) BYDATE（按天,?:表示今天的日期字符串） BYDELIVERY(取摸)--> <!-- <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$20170701-20170901" subTableWay="BYDATE" dataNode="dn1" /> --> <!-- <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$20170701-?" subTableWay="BYDATE" dataNode="dn1" /> --> <!-- **********按天分表 end --> <!-- **********取摸分表 start --> <!-- subTableWay: DEFAULT(默认) BYDATE（按天） BYDELIVERY(取摸)--> <!-- deliveryField： 标识取摸的字段，如车辆ID --> <!-- <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$1-5" subTableWay="BYDELIVERY" deliveryField="vehicle_id" dataNode="dn1" /> --> <!-- **********取摸分表 end --> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <table name="people" primaryKey="_ID" dataNode="dn1" /> <!-- subTableWay: DEFAULT(默认) BYDATE（按天） BYDELIVERY(取摸)--> <table name="t_subtable" primaryKey="_ID" subTables="t_subtable_$1-5" subTableWay="BYDELIVERY" deliveryField="vehicle_id" dataNode="dn1" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="mongo-test-db" /> <dataHost name="localhost1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"><heartbeat>select user()</heartbeat><writeHost host="hostM" url="mongodb://192.168.1.4/" user="admin" password="123456" ></writeHost> </dataHost></mycat:schema>
路由到分表子表的取摸改进
据下图进行一步步分析 ：
io.mycat.route.impl.DruidMycatRouteStrategy.java类解析路由策略
public RouteResultset routeNormalSqlWithAST(SchemaConfig schema,String stmt, RouteResultset rrs, String charset,LayerCachePool cachePool) throws SQLNonTransientException {/** * 只有mysql时只支持mysql语法 */SQLStatementParser parser = null;if (schema.isNeedSupportMultiDBType()) {parser = new MycatStatementParser(stmt);} else {parser = new MySqlStatementParser(stmt); }MycatSchemaStatVisitor visitor = null;SQLStatement statement;/** * 解析出现问题统一抛SQL语法错误 */try {statement = parser.parseStatement(); visitor = new MycatSchemaStatVisitor();} catch (Exception t) { LOGGER.error("DruidMycatRouteStrategyError", t);throw new SQLSyntaxErrorException(t);}/** * 检验unsupported statement */checkUnSupportedStatement(statement);DruidParser druidParser = DruidParserFactory.create(schema, statement, visitor);druidParser.parser(schema, rrs, statement, stmt,cachePool,visitor);/** * DruidParser 解析过程中已完成了路由的直接返回 */if ( rrs.isFinishedRoute() ) {return rrs;}/** * 没有from的select语句或其他 */ DruidShardingParseInfo ctx= druidParser.getCtx() ; if((ctx.getTables() == null || ctx.getTables().size() == 0)&&(ctx.getTableAliasMap()==null||ctx.getTableAliasMap().isEmpty())) { return RouterUtil.routeToSingleNode(rrs, schema.getRandomDataNode(), druidParser.getCtx().getSql());}if(druidParser.getCtx().getRouteCalculateUnits().size() == 0) {RouteCalculateUnit routeCalculateUnit = new RouteCalculateUnit();druidParser.getCtx().addRouteCalculateUnit(routeCalculateUnit);}SortedSet<RouteResultsetNode> nodeSet = new TreeSet<RouteResultsetNode>();for(RouteCalculateUnit unit: druidParser.getCtx().getRouteCalculateUnits()) {RouteResultset rrsTmp = RouterUtil.tryRouteForTables(schema, druidParser.getCtx(), unit, rrs, isSelect(statement), cachePool);if(rrsTmp != null) {for(RouteResultsetNode node :rrsTmp.getNodes()) {nodeSet.add(node);}}}RouteResultsetNode[] nodes = new RouteResultsetNode[nodeSet.size()];int i = 0;for (RouteResultsetNode aNodeSet : nodeSet) {nodes[i] = aNodeSet; if(statement instanceof MySqlInsertStatement &&ctx.getTables().size()==1&&schema.getTables().containsKey(ctx.getTables().get(0))) { RuleConfig rule = schema.getTables().get(ctx.getTables().get(0)).getRule(); if(rule!=null&& rule.getRuleAlgorithm() instanceof SlotFunction){ aNodeSet.setStatement(ParseUtil.changeInsertAddSlot(aNodeSet.getStatement(),aNodeSet.getSlot())); } }i++;}rrs.setNodes(nodes);//分表/** * subTables="t_order$1-2,t_order3" *目前分表 1.6 开始支持 幵丏 dataNode 在分表条件下只能配置一个，分表条件下不支持join。 */if(rrs.isDistTable()){return this.routeDisTable(statement,rrs);}return rrs;}注：主要关注
for(RouteCalculateUnit unit: druidParser.getCtx().getRouteCalculateUnits()) {
RouteResultset rrsTmp = RouterUtil.tryRouteForTables(schema, druidParser.getCtx(), unit, rrs, isSelect(statement), cachePool);
if(rrsTmp != null) {
for(RouteResultsetNode node :rrsTmp.getNodes()) {
nodeSet.add(node);
}
}
}
io.mycat.route.util.RouterUtil.java多表路由中的单表路由
/** * 多表路由 */public static RouteResultset tryRouteForTables(SchemaConfig schema, DruidShardingParseInfo ctx,RouteCalculateUnit routeUnit, RouteResultset rrs, boolean isSelect, LayerCachePool cachePool)throws SQLNonTransientException {List<String> tables = ctx.getTables();if(schema.isNoSharding()||(tables.size() >= 1&&isNoSharding(schema,tables.get(0)))) {if(Pattern.matches(".*_201\\d{5,5}", tables.get(0))){//支持分表, t_location_20170704这种表，删除结尾的_20170704后再查找路由TableConfig tc=schema.getTables().get( tables.get(0).substring(0, tables.get(0).length()-9));return routeToSingleNode(rrs, tc.getDataNodes().get(0), ctx.getSql());}return routeToSingleNode(rrs, schema.getDataNode(), ctx.getSql());}//只有一个表的if(tables.size() == 1) {return RouterUtil.tryRouteForOneTable(schema, ctx, routeUnit, tables.get(0), rrs, isSelect, cachePool);}Set<String> retNodesSet = new HashSet<String>();//每个表对应的路由映射Map<String,Set<String>> tablesRouteMap = new HashMap<String,Set<String>>();//分库解析信息不为空Map<String, Map<String, Set<ColumnRoutePair>>> tablesAndConditions = routeUnit.getTablesAndConditions();if(tablesAndConditions != null && tablesAndConditions.size() > 0) {//为分库表找路由RouterUtil.findRouteWithcConditionsForTables(schema, rrs, tablesAndConditions, tablesRouteMap, ctx.getSql(), cachePool, isSelect);if(rrs.isFinishedRoute()) {return rrs;}}//为全局表和单库表找路由for(String tableName : tables) {TableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());if(tableConfig==null && Pattern.matches(".*_201\\d{5,5}", tableName)){//支持分表, t_location_20170704这种表，支持结尾的_20170704后再查找路由tableConfig=schema.getTables().get(tableName.substring(0, tableName.length()-9));}if(tableConfig == null) {String msg = "can't find table define in schema "+ tableName + " schema:" + schema.getName();LOGGER.warn(msg);throw new SQLNonTransientException(msg);}if(tableConfig.isGlobalTable()) {//全局表if(tablesRouteMap.get(tableName) == null) {tablesRouteMap.put(tableName, new HashSet<String>());}tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());} else if(tablesRouteMap.get(tableName) == null) { //余下的表都是单库表tablesRouteMap.put(tableName, new HashSet<String>());tablesRouteMap.get(tableName).addAll(tableConfig.getDataNodes());}}boolean isFirstAdd = true;for(Map.Entry<String, Set<String>> entry : tablesRouteMap.entrySet()) {if(entry.getValue() == null || entry.getValue().size() == 0) {throw new SQLNonTransientException("parent key can't find any valid datanode ");} else {if(isFirstAdd) {retNodesSet.addAll(entry.getValue());isFirstAdd = false;} else {retNodesSet.retainAll(entry.getValue());if(retNodesSet.size() == 0) {//两个表的路由无交集String errMsg = "invalid route in sql, multi tables found but datanode has no intersection "+ " sql:" + ctx.getSql();LOGGER.warn(errMsg);throw new SQLNonTransientException(errMsg);}}}}if(retNodesSet != null && retNodesSet.size() > 0) {String tableName = tables.get(0);TableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());if(tableConfig==null && Pattern.matches(".*_201\\d{5,5}", tableName)){//支持分表, t_location_20170704这种表，支持结尾的_20170704后再查找路由tableConfig=schema.getTables().get(tableName.substring(0, tableName.length()-9));}if(tableConfig.isDistTable()){routeToDistTableNode(tableName,schema, rrs, ctx.getSql(), tablesAndConditions, cachePool, isSelect);return rrs;}if(retNodesSet.size() > 1 && isAllGlobalTable(ctx, schema)) {// mulit routes ,not cache route resultif (isSelect) {rrs.setCacheAble(false);routeToSingleNode(rrs, retNodesSet.iterator().next(), ctx.getSql());}else {//delete 删除全局表的记录routeToMultiNode(isSelect, rrs, retNodesSet, ctx.getSql(),true);}} else {routeToMultiNode(isSelect, rrs, retNodesSet, ctx.getSql());}}return rrs;}注：主要关注//只有一个表的 if(tables.size() == 1) { return RouterUtil.tryRouteForOneTable(schema, ctx, routeUnit, tables.get(0), rrs, isSelect, cachePool); }
io.mycat.route.util.RouterUtil.java支持单节点单表路由代码
/** * * 单表路由 */public static RouteResultset tryRouteForOneTable(SchemaConfig schema, DruidShardingParseInfo ctx,RouteCalculateUnit routeUnit, String tableName, RouteResultset rrs, boolean isSelect,LayerCachePool cachePool) throws SQLNonTransientException {if (isNoSharding(schema, tableName)) {return routeToSingleNode(rrs, schema.getDataNode(), ctx.getSql());}TableConfig tc = schema.getTables().get(tableName);if(Pattern.matches(".*_201\\d{5,5}", tableName)){//支持分表, t_location_20170704这种表，删除结尾的_20170704后再查找路由tc=schema.getTables().get(tableName.substring(0, tableName.length()-9));}if(tc == null) {String msg = "can't find table define in schema " + tableName + " schema:" + schema.getName();LOGGER.warn(msg);throw new SQLNonTransientException(msg);}if(tc.isDistTable()){return routeToDistTableNode(tableName,schema,rrs,ctx.getSql(), routeUnit.getTablesAndConditions(), cachePool,isSelect);}if(tc.isGlobalTable()) {//全局表if(isSelect) {// global select ,not cache route resultrrs.setCacheAble(false);return routeToSingleNode(rrs, tc.getRandomDataNode(),ctx.getSql());} else {//insert into 全局表的记录return routeToMultiNode(false, rrs, tc.getDataNodes(), ctx.getSql(),true);}} else {//单表或者分库表if (!checkRuleRequired(schema, ctx, routeUnit, tc)) {throw new IllegalArgumentException("route rule for table "+ tc.getName() + " is required: " + ctx.getSql());}if(tc.getPartitionColumn() == null && !tc.isSecondLevel()) {//单表且不是childTable//return RouterUtil.routeToSingleNode(rrs, tc.getDataNodes().get(0),ctx.getSql());return routeToMultiNode(rrs.isCacheAble(), rrs, tc.getDataNodes(), ctx.getSql());} else {//每个表对应的路由映射Map<String,Set<String>> tablesRouteMap = new HashMap<String,Set<String>>();if(routeUnit.getTablesAndConditions() != null && routeUnit.getTablesAndConditions().size() > 0) {RouterUtil.findRouteWithcConditionsForTables(schema, rrs, routeUnit.getTablesAndConditions(), tablesRouteMap, ctx.getSql(), cachePool, isSelect);if(rrs.isFinishedRoute()) {return rrs;}}if(tablesRouteMap.get(tableName) == null) {return routeToMultiNode(rrs.isCacheAble(), rrs, tc.getDataNodes(), ctx.getSql());} else {return routeToMultiNode(rrs.isCacheAble(), rrs, tablesRouteMap.get(tableName), ctx.getSql());}}}}注：重点是if(tc.isDistTable()){ return routeToDistTableNode(tableName,schema,rrs,ctx.getSql(), routeUnit.getTablesAndConditions(), cachePool,isSelect); }修改 io.mycat.route.util.RouterUtil.java类路由分表节点
/** *分表路由处理 */private static RouteResultset routeToDistTableNode(String tableName, SchemaConfig schema, RouteResultset rrs, String orgSql, Map<String, Map<String, Set<ColumnRoutePair>>> tablesAndConditions, LayerCachePool cachePool, boolean isSelect) throws SQLNonTransientException { TableConfig tableConfig = schema.getTables().get(tableName); if(tableConfig == null) { String msg = "can't find table define in schema " + tableName + " schema:" + schema.getName(); LOGGER.warn(msg); throw new SQLNonTransientException(msg); } if(tableConfig.isGlobalTable()){ String msg = "can't suport district table " + tableName + " schema:" + schema.getName() + " for global table "; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } String partionCol = tableConfig.getPartitionColumn();// String primaryKey = tableConfig.getPrimaryKey(); boolean isLoadData=false; Set<String> tablesRouteSet = new HashSet<String>(); List<String> dataNodes = tableConfig.getDataNodes(); if(dataNodes.size()>1){ String msg = "can't suport district table " + tableName + " schema:" + schema.getName() + " for mutiple dataNode " + dataNodes; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } String dataNode = dataNodes.get(0); //主键查找缓存暂时不实现 if(tablesAndConditions.isEmpty()){ // 取摸查询====开始====== if(tableConfig.getSubTableWay().equals("BYDELIVERY")&&tableConfig.getDeliveryField()!=null&&!tableConfig.getDeliveryField().equals("")){ long vechicleId=-1; String hintStart="/**"; String hintEnd="**/"; if(!orgSql.contains(hintStart)&&!orgSql.contains(hintEnd)){ String msg = "incorrect sql rule by subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"'"; msg+=">>:select SQL would like to end with /**"+tableConfig.getDeliveryField()+"="+108392232+"**/"; LOGGER.warn(msg); throw new SQLNonTransientException(msg); }else{ String valueStr=orgSql.substring(orgSql.indexOf(hintStart)+3, orgSql.length()-3); String [] values=valueStr.split("="); try { vechicleId=Long.parseLong(values[1]); } catch (NumberFormatException e) { String msg = " subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"' value is not a number."; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } if(values.length<2||!tableConfig.getDeliveryField().equalsIgnoreCase(values[0])){ String msg = " subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"' is not the same file end with the SQL"; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } } // 取摸方式处理 List<String> subTables = new ArrayList<String>(); int count=tableConfig.getDistTables().size(); String table=tableConfig.getName(); String ruleTable=tableConfig.getSubTables().substring(0,tableConfig.getSubTables().indexOf("$")); String connectStr=ruleTable.length()==table.length()?"":ruleTable.substring(table.length(), ruleTable.length()); vechicleId=vechicleId%count==0?count:vechicleId%count; String delvieryTable=new String(table+connectStr+vechicleId).toLowerCase(); for (String subTable : tableConfig.getDistTables()) { if(subTable.equals(delvieryTable)){ subTables.add(subTable);// 取摸表 } } tablesRouteSet.addAll(subTables); }else{ List<String> subTables = tableConfig.getDistTables(); tablesRouteSet.addAll(subTables); } // 取摸查询====结束====== }else{ for(Map.Entry<String, Map<String, Set<ColumnRoutePair>>> entry : tablesAndConditions.entrySet()) { boolean isFoundPartitionValue = partionCol != null && entry.getValue().get(partionCol) != null; Map<String, Set<ColumnRoutePair>> columnsMap = entry.getValue(); Set<ColumnRoutePair> partitionValue = columnsMap.get(partionCol); if(partitionValue == null || partitionValue.size() == 0) { // 条件取摸方式=========开始==== //tablesRouteSet.addAll(tableConfig.getDistTables()); if(tableConfig.getSubTableWay().equals("BYDELIVERY")&&tableConfig.getDeliveryField()!=null&&!tableConfig.getDeliveryField().equals("")){ long vechicleId=-1; String hintStart="/**"; String hintEnd="**/"; if(!orgSql.contains(hintStart)&&!orgSql.contains(hintEnd)){ String msg = "incorrect sql rule by subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"'"; msg+=">>:select SQL would like to end with /**"+tableConfig.getDeliveryField()+"="+108392232+"**/"; LOGGER.warn(msg); throw new SQLNonTransientException(msg); }else{ String valueStr=orgSql.substring(orgSql.indexOf(hintStart)+3, orgSql.length()-3); String [] values=valueStr.split("="); try { vechicleId=Long.parseLong(values[1]); } catch (NumberFormatException e) { String msg = " subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"' value is not a number."; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } if(values.length<2||!tableConfig.getDeliveryField().equalsIgnoreCase(values[0])){ String msg = " subTables='BYDELIVERY' property deliveryField='"+tableConfig.getDeliveryField()+"' is not the same file end with the SQL"; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } } // 取摸方式处理 List<String> subTables = new ArrayList<String>(); int count=tableConfig.getDistTables().size(); String table=tableConfig.getName(); String ruleTable=tableConfig.getSubTables().substring(0,tableConfig.getSubTables().indexOf("$")); String connectStr=ruleTable.length()==table.length()?"":ruleTable.substring(table.length(), ruleTable.length()); vechicleId=vechicleId%count==0?count:vechicleId%count; String delvieryTable=new String(table+connectStr+vechicleId).toLowerCase(); for (String subTable : tableConfig.getDistTables()) { if(subTable.equals(delvieryTable)){ subTables.add(subTable);// 取摸表 } } tablesRouteSet.addAll(subTables); orgSql=orgSql.substring(0,orgSql.indexOf(hintStart)); }else{ List<String> subTables = tableConfig.getDistTables(); tablesRouteSet.addAll(subTables); } // 条件取摸方式=========结束==== } else { for(ColumnRoutePair pair : partitionValue) { AbstractPartitionAlgorithm algorithm = tableConfig.getRule().getRuleAlgorithm(); if(pair.colValue != null) { Integer tableIndex = algorithm.calculate(pair.colValue); if(tableIndex == null) { String msg = "can't find any valid datanode :" + tableConfig.getName() + " -> " + tableConfig.getPartitionColumn() + " -> " + pair.colValue; LOGGER.warn(msg); throw new SQLNonTransientException(msg); } String subTable = tableConfig.getDistTables().get(tableIndex); if(subTable != null) { tablesRouteSet.add(subTable); if(algorithm instanceof SlotFunction){ rrs.getDataNodeSlotMap().put(subTable,((SlotFunction) algorithm).slotValue()); } } } if(pair.rangeValue != null) { Integer[] tableIndexs = algorithm .calculateRange(pair.rangeValue.beginValue.toString(), pair.rangeValue.endValue.toString()); for(Integer idx : tableIndexs) { String subTable = tableConfig.getDistTables().get(idx); if(subTable != null) { tablesRouteSet.add(subTable); if(algorithm instanceof SlotFunction){ rrs.getDataNodeSlotMap().put(subTable,((SlotFunction) algorithm).slotValue()); } } } } } } } } Object[] subTables = tablesRouteSet.toArray(); RouteResultsetNode[] nodes = new RouteResultsetNode[subTables.length]; Map<String,Integer> dataNodeSlotMap= rrs.getDataNodeSlotMap(); for(int i=0;i<nodes.length;i++){ String table = String.valueOf(subTables[i]); String changeSql = orgSql; nodes[i] = new RouteResultsetNode(dataNode, rrs.getSqlType(), changeSql);//rrs.getStatement() nodes[i].setSubTableName(table); nodes[i].setSource(rrs); if(rrs.getDataNodeSlotMap().containsKey(dataNode)){ nodes[i].setSlot(rrs.getDataNodeSlotMap().get(dataNode)); } if (rrs.getCanRunInReadDB() != null) { nodes[i].setCanRunInReadDB(rrs.getCanRunInReadDB()); } if(dataNodeSlotMap.containsKey(table)) { nodes[i].setSlot(dataNodeSlotMap.get(table)); } if(rrs.getRunOnSlave() != null){ nodes[0].setRunOnSlave(rrs.getRunOnSlave()); } } rrs.setNodes(nodes); rrs.setSubTables(tablesRouteSet); rrs.setFinishedRoute(true); return rrs;}注：无论有无where查询都是可以的（测试过程中发现错误已修改）。测试取摸分表查询语句
非where条件取摸查询
请对比参考如下两个图：
where条件取摸查询
请对比参考如下两个图：
可见，取摸查询已实现。
插入或取摸插入表数据注意事项
这里需要特别注意mysql数据库关键字问题，下面是数据库关键字测试问题：如 name、table、by等等。
下面两个SQL插入语句在Druid解析是有区别的：
-- 正确解析语句INSERT into t_subtable(_id,title,`name`,`table`,description,likes,`by`) VALUES('59759679dc07a955104a9f1b','mongo','boonya4','t_subtable_4','database','100','boonya') /**vehicle_id=128943434354**/-- 错误解析语句INSERT into t_subtable(_id,title,name,table,description,likes,by) VALUES('59759679dc07a955104a9f1b','mongo','boonya4','t_subtable_4','database','100','boonya') /**vehicle_id=128943434354**/MongoDB API插入数据
Mycat插入数据
Mycat带关键字列插入异常
(io.mycat.route.impl.DruidMycatRouteStrategy:DruidMycatRouteStrategy.java:78) 2017-07-25 12:04:25,114 [WARN ][$_NIOREACTOR-1-RW] ServerConnection [id=1, schema=TESTDB, host=192.168.1.7, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT into t_subtable(_id,title,name,table,description,likes,by) VALUES('59759679dc07a955104a9f1b','mongo','boonya4','t_subtable_4','database','100','boonya') /**vehicle_id=128943434354**/ err:java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: ERROR. token : BY, pos : 64 java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: ERROR. token : BY, pos : 64at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:79)at io.mycat.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:81)at io.mycat.route.RouteService.route(RouteService.java:133)at io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:276)at io.mycat.server.ServerConnection.execute(ServerConnection.java:222)at io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:136)at io.mycat.net.FrontendConnection.query(FrontendConnection.java:317)at io.mycat.net.FrontendConnection.query(FrontendConnection.java:337)at io.mycat.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71)at io.mycat.net.FrontendConnection.rawHandle(FrontendConnection.java:478)at io.mycat.net.FrontendConnection.handle(FrontendConnection.java:460)at io.mycat.net.AbstractConnection.onReadData(AbstractConnection.java:321)at io.mycat.net.NIOSocketWR.asynRead(NIOSocketWR.java:190)at io.mycat.net.AbstractConnection.asynRead(AbstractConnection.java:273)at io.mycat.net.NIOReactor$RW.run(NIOReactor.java:102)at java.lang.Thread.run(Thread.java:745)Caused by: com.alibaba.druid.sql.parser.ParserException: ERROR. token : BY, pos : 64at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:587)at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:167)at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:94)at com.alibaba.druid.sql.parser.SQLExprParser.exprList(SQLExprParser.java:903)at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:2014)at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseInsert(MySqlStatementParser.java:193)at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:199)at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:2065)at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:75)... 15 more (io.mycat.server.ServerConnection:ServerConnection.java:281) 附录阅读
附录是我摘录可能以后用得着的东西，权当备忘。
如果想了解Mycat路由解析可以参考：Mycat的SQL解析和路由
如果想了解Mycat后端通信模块可以参考：Mycat源码篇 : MyCat线程模型分析
如果想了解Mycat事务管理可以参考：Mycat源码篇 : MyCat事务管理机制分析
如果想了解mycat具体的分片取模可以参考：mycat 取模分片，ER分片
如果想了解Mysql全局序列号可以参考：mycat 使用mysql实现全局序列号
阅读全文
0 0
- Mycat 源码修改-实现分表规则：按天分表和取摸分表查询
- Mycat源码修改分表规则的CRUD操作测试与改进
- 按天分表后查询分页（查询表计算方法）
- mycat分表自增 db实现
- postgresql 分表规则实时修改
- Mysql（Laravel）实现按天分组查询数据
- MyCat分表分库规则实现
- mysql 表分区 按天分区
- Mycat（6）：聊天消息表，按月分表 java客户端跨月查询数据
- mycat学习05之水平分表
- 使用mycat分表(一致性hash)
- Mycat 水平分表，垂直分表实践（2）
- mysql 查询语句按天分组统计
- Mysql分表查询的简单实现
- Java Mybatis Plugin插件实现分表路由规则
- Mycat（4）：消息表mysql数据库分表实践
- 数据库中间件MyCAT源码分析：【单库单表】查询
- 哈希256规则分表
- 计算余弦距离
- jquery 动态生成表 绑定click事件，获取同行数据
- Java数据类型和运算符
- HTML Element nodeType 定义
- jdk8，Stream流水线源码解析
- Mycat 源码修改-实现分表规则：按天分表和取摸分表查询
- String常用的方法
- vue.js学习笔记（四）--变化检测问题
- 监听器
- 关于Java，你应该掌握的
- PHP之linux(三) 编辑器(vi/vim) 网络配置
- 像鱼儿离不开水，未来我们将高度依赖机器学习
- HDH 1264 Counting Squares （线段树+扫描线|暴力）
- map使用
