You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/07/31 07:29:06 UTC
[GitHub] [shardingsphere] 291277058 opened a new issue #6551: Question:update sql executed in all of the table
291277058 opened a new issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551
Does shardingsphere support table chose when use update sql.In my point, i think update sql can chose the right table by the strategy field,doed not need update all of it.,But i got the follow result.Does some body can explain it for me about it,thanks.
`2020-07-31 07:05:07.823 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Rule Type: sharding
2020-07-31 07:05:07.823 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Logic SQL: update order_item set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=?
2020-07-31 07:05:07.825 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : SQLStatement: DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=order_item, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=order_item, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=13, logicSQL=update order_item set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=?), deleteStatement=false, updateTableAlias={order_item=order_item}, updateColumnValues={Column(name=app_id, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@78553a80, Column(name=create_date, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQ
LPlaceholderExpression@63a63f17, Column(name=order_info, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2c07b787, Column(name=orderno, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@61248ad2, Column(name=original_price, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@30742d4c, Column(name=pay_date, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6278f2d2, Column(name=pay_source, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7352ca95, Column(name=price, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@36e5bc37, Column(name=product_list, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpressio
n@276ae9ea, Column(name=source_orderno, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@22171b7, Column(name=status, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@67421504, Column(name=user_id, tableName=order_item)=org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7c5d19df}, whereStartIndex=179, whereStopIndex=188, whereParameterStartIndex=12, whereParameterEndIndex=12)
2020-07-31 07:05:07.825 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: master ::: update order_item20207 set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=? ::: [9b035964-c491-4ce5-b727-3929772e8b96, 2020-07-31 07:05:00.166, {}, 202007310705005, 0.0, 2020-07-31 07:05:06.428, google, 0.0, {"purchaseTimeMillis":"1596179102735","purchaseState":0,"consumptionState":0,"developerPayload":"","orderId":"GPA.3354-6366-5211-19811","purchaseType":0,"acknowledgementState":1,"kind":"androidpublisher#productPurchase","obfuscatedExternalAccountId":"202007310705005","obfuscatedExternalProfileId":"{\"skuType\":\"inapp\"}"}, GPA.3354-6366-5211-19811, 10, edf14b79-87a2-4bfe-9cc7-2ea20e7a7a5a, 2c9fb08a73a386e40173a3b032080004]
2020-07-31 07:05:07.825 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: master ::: update order_item20211 set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=? ::: [9b035964-c491-4ce5-b727-3929772e8b96, 2020-07-31 07:05:00.166, {}, 202007310705005, 0.0, 2020-07-31 07:05:06.428, google, 0.0, {"purchaseTimeMillis":"1596179102735","purchaseState":0,"consumptionState":0,"developerPayload":"","orderId":"GPA.3354-6366-5211-19811","purchaseType":0,"acknowledgementState":1,"kind":"androidpublisher#productPurchase","obfuscatedExternalAccountId":"202007310705005","obfuscatedExternalProfileId":"{\"skuType\":\"inapp\"}"}, GPA.3354-6366-5211-19811, 10, edf14b79-87a2-4bfe-9cc7-2ea20e7a7a5a, 2c9fb08a73a386e40173a3b032080004]
2020-07-31 07:05:07.827 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: master ::: update order_item20217 set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=? ::: [9b035964-c491-4ce5-b727-3929772e8b96, 2020-07-31 07:05:00.166, {}, 202007310705005, 0.0, 2020-07-31 07:05:06.428, google, 0.0, {"purchaseTimeMillis":"1596179102735","purchaseState":0,"consumptionState":0,"developerPayload":"","orderId":"GPA.3354-6366-5211-19811","purchaseType":0,"acknowledgementState":1,"kind":"androidpublisher#productPurchase","obfuscatedExternalAccountId":"202007310705005","obfuscatedExternalProfileId":"{\"skuType\":\"inapp\"}"}, GPA.3354-6366-5211-19811, 10, edf14b79-87a2-4bfe-9cc7-2ea20e7a7a5a, 2c9fb08a73a386e40173a3b032080004]
2020-07-31 07:05:07.827 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: master ::: update order_item20221 set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=? ::: [9b035964-c491-4ce5-b727-3929772e8b96, 2020-07-31 07:05:00.166, {}, 202007310705005, 0.0, 2020-07-31 07:05:06.428, google, 0.0, {"purchaseTimeMillis":"1596179102735","purchaseState":0,"consumptionState":0,"developerPayload":"","orderId":"GPA.3354-6366-5211-19811","purchaseType":0,"acknowledgementState":1,"kind":"androidpublisher#productPurchase","obfuscatedExternalAccountId":"202007310705005","obfuscatedExternalProfileId":"{\"skuType\":\"inapp\"}"}, GPA.3354-6366-5211-19811, 10, edf14b79-87a2-4bfe-9cc7-2ea20e7a7a5a, 2c9fb08a73a386e40173a3b032080004]
2020-07-31 07:05:07.827 info 1172 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: master ::: update order_item20227 set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=? ::: [9b035964-c491-4ce5-b727-3929772e8b96, 2020-07-31 07:05:00.166, {}, 202007310705005, 0.0, 2020-07-31 07:05:06.428, google, 0.0, {"purchaseTimeMillis":"1596179102735","purchaseState":0,"consumptionState":0,"developerPayload":"","orderId":"GPA.3354-6366-5211-19811","purchaseType":0,"acknowledgementState":1,"kind":"androidpublisher#productPurchase","obfuscatedExternalAccountId":"202007310705005","obfuscatedExternalProfileId":"{\"skuType\":\"inapp\"}"}, GPA.3354-6366-5211-19811, 10, edf14b79-87a2-4bfe-9cc7-2ea20e7a7a5a, 2c9fb08a73a386e40173a3b032080004]`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
`
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
# ---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
`
And my shardingAlgorithm
`
package com.sino.gameplus.sdkserver.payment.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.*;
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
#---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
And my shardingAlgorithm
`
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-675568094
The sharding column you configured are `create_date and orderno`, but the update SQL is updated by id: `where id=?`
The full route is expected behaviour. What is your expected?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 commented on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-668368437
> @291277058 Thanks for your feedback.
> Have you tested your `sharding Algorithm` with the SQL mentioned above? Did this algorithm return the correct targets with the value of `sharding key` in that SQL?
When insert the sharding method was excuted.But when update ,the sharding method was not used.I guess it`s only used when sql `where` follow the sharding field which i set.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
#---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,orderno
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
And my shardingAlgorithm
`
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
`
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
#---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
`
And my shardingAlgorithm
`
package com.sino.gameplus.sdkserver.payment.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.*;
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
`
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
#---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
`
And my shardingAlgorithm
`
package com.sino.gameplus.sdkserver.payment.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.*;
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 commented on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
`
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
# 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
`
And my shardingAlgorithm
`
package com.sino.gameplus.sdkserver.payment.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.*;
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667829230
Hi @291277058
Could give more clear info? Like SQL, sharding key, sharding algorithm.
All the tables will be updated if there is no `sharding rule` for this table or your query condition does not contain `sharding key`.
Thanks,
Trista
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-668353047
@291277058 Thanks for your feedback.
Have you tested your `sharding Algorithm` with the SQL mentioned above? Did this algorithm return the correct targets with the value of `sharding key` in that SQL?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-667901195
This is my config.
`
#---------------------------- 数据库分片配置
spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds0.order_item20207,ds0.order_item20211,ds0.order_item20217,ds0.order_item20221,ds0.order_item20227
#---------------------------- 配置分表策略
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.sharding-columns=create_date,ordernoComplexShardingConfig
spring.shardingsphere.sharding.tables.order_item.table-strategy.complex.algorithm-class-name=com.sino.gameplus.sdkserver.payment.config.ComplexShardingConfig
`
And my shardingAlgorithm
`package com.sino.gameplus.sdkserver.payment.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.*;
@Slf4j
@Component
public class ComplexShardingConfig implements ComplexKeysShardingAlgorithm<String> {
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// TODO: 2020/7/27 需要理清complex的应用组合不断完善该方法；判断value为多个值时为什么情况，>< >= 等等
String logicTableName = complexKeysShardingValue.getLogicTableName();
Map<String, LinkedList> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
LinkedList<String> orderno = map.get("orderno");
LinkedList<Timestamp> createDate = map.get("create_date");
Set<String> result = new HashSet<String>() {
};
if (!ObjectUtils.isEmpty(orderno)) {
for (String order : orderno) {
TemporalAccessor parse = dateTimeFormatter.parse(order.substring(0, 6));
String tableName = getTableName(logicTableName, parse.get(ChronoField.YEAR), parse.get(ChronoField.MONTH_OF_YEAR));
if (collection.contains(tableName))
result.add(tableName);
}
} else if (!ObjectUtils.isEmpty(createDate)) {
for (Timestamp dateTime : createDate) {
LocalDateTime localDateTime = dateTime.toLocalDateTime();
String tableName = getTableName(logicTableName, localDateTime.getYear(), localDateTime.getYear());
if (collection.contains(tableName))
result.add(tableName);
}
} else {
LocalDateTime now = LocalDateTime.now();
String tableName = getTableName(logicTableName, now.getYear(), now.getMonthValue());
result.add(tableName);
}
if (ObjectUtils.isEmpty(result))
log.error("table not found or table list is null");
return result;
}
private String getTableName(String logicTableName, int year, int month) {
String tableMonth = "";
if (month <= 5)
tableMonth = "1";
else
tableMonth = "7";
return logicTableName + year + tableMonth;
}
}`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 edited a comment on issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551#issuecomment-668368437
> @291277058 Thanks for your feedback.
> Have you tested your `sharding Algorithm` with the SQL mentioned above? Did this algorithm return the correct targets with the value of `sharding key` in that SQL?
When insert the sharding method was excuted.But when update ,the sharding method was not used.I guess it's only used when sql `where` follow the sharding field which i set.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] 291277058 closed issue #6551: Question:update sql executed in all of the table
Posted by GitBox <gi...@apache.org>.
291277058 closed issue #6551:
URL: https://github.com/apache/shardingsphere/issues/6551
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org