When integrating MyBatis Plus for batch updates, only the first SQL sharding is correct and the rest are not effective.

[wqzcoder]

Question

For English only, other languages will not accept. The version of Shardingspher-jdbc-core I'm using is 5.1.1. I integrated MybatisPlus to perform batch updates as follows:

update operation_plan_target set tartget_key = #{item.targetKey}, target_type = #{item.targetType}, target_value = #{item.targetValue}, target_detail = #{item.targetDetail} where id = #{[item.id](http://item.id/)} and company_id = #{companyId}

I split tables based on the company_id field, but when using this batch update, the resulting SQL statements look like this:

update operation_plan_target_27 set tartget_key = 1, target_type = 1, target_value = 1, target_detail = 2 where id = 1 and company_id = 901117781768839168; update operation_plan_target set tartget_key = 2, target_type = 2, target_value = 2, target_detail = 2 where id = 2 and company_id = 901117781768839168.

However, when I choose to update them one by one, each table will be correctly sharded. Here is an example: ShardingSphere-SQL Actual SQL: db0 ::: UPDATE operation_plan_target_27 SET company_id=?, target_key=?, target_value=?, last_uid=? WHERE is_deleted=0 AND (id = ? AND company_id = ?) ::: [901117781768839168, sale, 48.38, 129080, 139338548000636928, 901117781768839168] <== Updates: 1 ShardingSphere-SQL Actual SQL: db0 ::: UPDATE operation_plan_target_27 SET company_id=?, target_key=?, target_value=?, last_uid=? WHERE is_deleted=0 AND (id = ? AND company_id = ?) ::: [901117781768839168, estimatedGrossProfitRate, 38.38, 129080, 139338548000636929, 901117781768839168] <== Updates: 1. Do you have a corresponding SPI interface to handle the failure of batch updates like this? It would be helpful for us developers to rewrite and inject, or do you have other suggestions?

As you can see, only the first SQL statement split the table based on the companyid, while the subsequent ones did not. Before asking a question, make sure you have:

This is a result that has been updated one by one after correcting the distribution table.

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

[zhaojinchao95]

Please translate your comment in English.

[corgy-w]

@zhaojinchao95 His non-English questions are comments

[wqzcoder]

Finally,This is mySharding yaml Config: shardingsphere: mode: type: Memory enabled: false enable: testRoute: true ak: enabled: true props: sql-show: true datasource: names: db0 db0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: resource:jdbc:mysql://xxx?autoReconnect=true&useSSL=false&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai rules: sharding: defaultDatabaseStrategy: standard: shardingColumn: company_id shardingAlgorithmName: murHash defaultTableStrategy: standard: shardingColumn: company_id shardingAlgorithmName: murHash2 sharding-algorithms: murHash: type: AK_HASH murHash2: type: AK_HASH_DOUBLE tables: operation_plan: actual-data-nodes: db0.operationplan$->{0..63} operation_plan_target: actual-data-nodes: db0.operation_plantarget$->{0..63} operation_plan_task: actual-data-nodes: db0.operation_plantask$->{0..63} operation_plan_template: actual-data-nodes: db0.operation_plantemplate$->{0..63} operation_plan_template_sys: actual-data-nodes: db0.operation_plan_templatesys$->{0..63} operation_plan_template_task: actual-data-nodes: db0.operation_plan_templatetask$->{0..63}

[wqzcoder]

Please translate your comment in English.

My comment has been changed to English, but my question has always been in English

[wqzcoder]

excuse me？ Can you help me answer the question?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[terrymanu]

The issue involves other third-party dependencies, but our focus is solely on ShardingSphere itself. Since ShardingSphere implements the JDBC interface, standard applications should be functional. We wish to allocate more effort towards enhancing the current version, and therefore will no longer handle such issues. Please read the documentation or provide more effective information when submitting an issue.