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/08/06 07:33:09 UTC
[GitHub] [shardingsphere] fantacy2001 opened a new issue #6665: sharding jdbc do not support batch update
fantacy2001 opened a new issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665
sharding jdbc do not support execute multiple SQL,like this:
mybatis
<foreach collection="list" item="item" separator=";">
update table
set name = #{item.name,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=BIGINT}
</foreach>
My question is :
1、Will it be supported this sql in the future?
2、How to replace this kind of SQL now?
if use single update sql, for loop in code, the efficiency is relatively low
----------------------------------------------------------------
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] yu199195 commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669830412
you can show real SQL,if like update table set name =? where id =? in loop, is support.
----------------------------------------------------------------
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 #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-671007846
@fantacy2001 Hi thanks for your feedback.
If the SQL below is one SQL request, I am sorry to say we currently do not support it and have no plan for it, as @wenweibin said.
```sql
update stock_change_detail
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ?
```
----------------------------------------------------------------
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 #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669832178
Sorry, @fantacy2001 You means many of single SQL like `update table set name = #{item.name,jdbcType=VARCHAR} where id = #{item.id,jdbcType=BIGINT}` run at once?
I am not sure `statement.executeBatch()` is whether your concerns. Maybe not?
----------------------------------------------------------------
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] fantacy2001 commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669780388
> which can only cut a little network overhead, and the performance improvement is very limited.
However, a single SQL must get the connection every time,In case of large quantity,The effect may be magnified
----------------------------------------------------------------
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] fantacy2001 commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-670335900
> Sorry, @fantacy2001 You means many of single SQL like `update table set name = #{item.name,jdbcType=VARCHAR} where id = #{item.id,jdbcType=BIGINT}` run at once?
> I am not sure `statement.executeBatch()` is whether your concerns. Maybe not?
The above ‘’foreach” is removed
i mean this:
mybatis mapper:
< foreach collection="list" item="item" separator=";" >
update table
set name = #{item.name,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=BIGINT}
< /foreach >
----------------------------------------------------------------
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] wenweibin commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
wenweibin commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669805642
> > which can only cut a little network overhead, and the performance improvement is very limited.
>
> However, a single SQL must get the connection every time,In case of large quantity,The effect may be magnified
Don't worry, this is usually not a problem because of connection pool.
----------------------------------------------------------------
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 #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-670389933
@wenweibin @fantacy2001
```
< foreach collection="list" item="item" separator=";" >
update table
set name = #{item.name,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=BIGINT}
< /foreach >
```
If so, what SQL will be generated and sent to ShardingJDBC? Could you provide an actual 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] wenweibin edited a comment on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
wenweibin edited a comment on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669774733
1.I don't think it will be supported in the future, because this kind of SQL may be routed to multiple data nodes, which will cause distributed transaction problems.
2. Just split batch SQL into a single sql , actually,batch update SQL with semicolon is just a pseudo batch-update , and the mysql server is still executed one by one, which can only cut a little network overhead, and the performance improvement is very limited.
----------------------------------------------------------------
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] yifanzzz commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
yifanzzz commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-727709594
`< foreach collection="list" item="item" separator=";" >
update table
set name = #{item.name,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=BIGINT}
< /foreach >` like this sql only first statement can rout right, i think this is an issue, and i want to know that anyone will fix it in feature?
----------------------------------------------------------------
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] fantacy2001 commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-670446238
> @wenweibin @fantacy2001
>
> ```
> < foreach collection="list" item="item" separator=";" >
> update table
> set name = #{item.name,jdbcType=VARCHAR}
> where id = #{item.id,jdbcType=BIGINT}
> < /foreach >
> ```
>
> If so, what SQL will be generated and sent to ShardingJDBC? Could you provide an actual SQL?
Logic SQL: update stock_change_detail
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ?
Actual SQL: ds_1 ::: update stock_change_detail_023
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ?
;
update stock_change_detail
SET update_time=now()
WHERE id = ?
AND tenant_id = ?
and warehouse_no = ? ::: [1, jd, 26, 2, jd, 26]
two sql,same sharding value , but table is different
----------------------------------------------------------------
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] wenweibin commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
wenweibin commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-669774733
1.I don't think it will be supported in the future, because this kind of SQL will be routed to multiple data nodes, which will cause distributed transaction problems.
2. Just split batch SQL into a single sql , actually,batch update SQL with semicolon is just a pseudo batch-update , and the mysql server is still executed one by one, which can only cut a little network overhead, and the performance improvement is very limited.
----------------------------------------------------------------
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] strongduanmu commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-671379286
@tristaZero @yu199195 @wenweibin @fantacy2001
I think batch update can be implemented by adding some restrictions. For example, multiple tables must be binding tables, and routing conditions must be the same to avoid distributed transaction problems.
I want to take some time to implement this feature, because our company also needs this feature.
----------------------------------------------------------------
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 closed issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665
----------------------------------------------------------------
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] fantacy2001 commented on issue #6665: sharding jdbc do not support batch update
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6665:
URL: https://github.com/apache/shardingsphere/issues/6665#issuecomment-670336094
> you can show real SQL,if like update table set name =? where id =? in loop, is support.
like this :
< foreach collection="list" item="item" separator=";" >
update table
set name = #{item.name,jdbcType=VARCHAR}
where id = #{item.id,jdbcType=BIGINT}
< /foreach >
----------------------------------------------------------------
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