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/15 03:04:06 UTC
[GitHub] [shardingsphere] lcx1989210 opened a new issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
lcx1989210 opened a new issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351
Mysql Server version : 5.7.22;
shardingsphere version: 4.1.1
The maven info as follow:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
The sql is :
insert into consume_374 (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,product_code, citys, categorys,
status, created_at, modified_at,extension1, extension2, keep_at,info_id
, id)
values
(37744590551312, 2, 482568302627979264, 482568302422458368, 1.00, 1594781777191, '2020_04_23_wyk_997', 'token_004', '3', '', '', 1,
1594781777191, 1594781777191, '', '', null, null, 490113583909240833),
(37744590551312, 2, 482568813330628609, 482568813192216577, 1.00, 1594781777207, '2020_04_23_wyk_997', 'token_004', '3', '', '', 1,
1594781777207, 1594781777207, '', '', null, null, 490113583909240832).
When i insert one record ,I want the id return to me.But it will return only one Id when execute batch insert!
So,what can I do to solve this problem?
So,what can I do to solve this problem?
----------------------------------------------------------------
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 #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659276311
Hi @lcx1989210
I prefer @xiaoma20082008 's comment. Maybe you can give it a test.
Hi @xiaoma20082008 Thanks for your response. :-)
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] kimmking commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659307839
@xiaoma20082008 not exactly.
it's not last_insert_id issue, but resultSet.getGeneratedKeys.
----------------------------------------------------------------
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] lcx1989210 edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
The picture
<insert id="insetConsumeLog" useGeneratedKeys="true" keyProperty="id">
insert into consume (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,
product_code, citys, categorys,
status, created_at, modified_at,
extension1, extension2, keep_at,info_id
)
values
<foreach collection="list" item="c" separator=",">
(#{c.userId}, #{c.userType},
#{c.balanceId}, #{c.orderId}, #{c.amount},
#{c.consumeAt}, #{c.consumeKey}, #{c.consumeToken},
#{c.productCode}, #{c.citys}, #{c.categorys},
#{c.status}, #{c.createdAt}, #{c.modifiedAt},
#{c.extension1}, #{c.extension2}, #{c.keepAt},#{c.infoId}
)
</foreach>
</insert>
And mybatis version is 3.4.2.
----------------------------------------------------------------
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] lcx1989210 edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
<insert id="insetConsumeLog" useGeneratedKeys="true" keyProperty="id">
insert into consume (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,
product_code, citys, categorys,
status, created_at, modified_at,
extension1, extension2, keep_at,info_id
)
values
<foreach collection="list" item="c" separator=",">
(#{c.userId}, #{c.userType},
#{c.balanceId}, #{c.orderId}, #{c.amount},
#{c.consumeAt}, #{c.consumeKey}, #{c.consumeToken},
#{c.productCode}, #{c.citys}, #{c.categorys},
#{c.status}, #{c.createdAt}, #{c.modifiedAt},
#{c.extension1}, #{c.extension2}, #{c.keepAt},#{c.infoId}
)
</foreach>
</insert>
And mybatis version is 3.4.2.
----------------------------------------------------------------
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 #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658524897
@lcx1989210 Hi Thanks for your issue.
> When i insert one record ,I want the id return to me.But it will return only one Id when execute batch insert!
How did you do to get IDs? My guess is,
`preparedStatement.getGeneratedKeys()`, right?
----------------------------------------------------------------
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] lcx1989210 commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658609066
Please tell me why there is such a phenomenon？
----------------------------------------------------------------
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] kimmking commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659162155
It's complex, you can see prevous issues.
Batch insertion with generated key has several bugs now, and the sharding result may be wrong.
So I VERY SUGGEST YOU don't use this way, you can use a for-each for one row insertion instead.
----------------------------------------------------------------
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] menghaoranss commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658524279
Duplicated with #5858
----------------------------------------------------------------
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] lcx1989210 commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
mybatis version is 3.4.2.
----------------------------------------------------------------
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] lcx1989210 commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659185880
OK, I've used it that way。Thanks a lot.
----------------------------------------------------------------
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] lcx1989210 edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
The picture will invisible sometimes.
<insert id="insetConsumeLog" useGeneratedKeys="true" keyProperty="id">
insert into consume (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,
product_code, citys, categorys,
status, created_at, modified_at,
extension1, extension2, keep_at,info_id
)
values
<foreach collection="list" item="c" separator=",">
(#{c.userId}, #{c.userType},
#{c.balanceId}, #{c.orderId}, #{c.amount},
#{c.consumeAt}, #{c.consumeKey}, #{c.consumeToken},
#{c.productCode}, #{c.citys}, #{c.categorys},
#{c.status}, #{c.createdAt}, #{c.modifiedAt},
#{c.extension1}, #{c.extension2}, #{c.keepAt},#{c.infoId}
)
</foreach>
</insert>
And mybatis version is 3.4.2.
There was no problem at shardingsphere version 3.1.0.
----------------------------------------------------------------
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] kimmking closed issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351
----------------------------------------------------------------
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] kimmking edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659162155
It's complex, you can see prevous issues.
Batch insertion with generated key has several bugs now, and the sharding result may be wrong.
So I VERY MUCH SUGGEST YOU don't use this way, you can use a for-each for one row insertion instead.
----------------------------------------------------------------
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] xiaoma20082008 commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
xiaoma20082008 commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-659260555
I think it's not a bug, mysql only return the first the last_insert_id when insert with multi values. other values need you increment the last_insert_id one by one by yourself.
----------------------------------------------------------------
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] lcx1989210 edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
The picture will invisible sometimes.
<insert id="insetConsumeLog" useGeneratedKeys="true" keyProperty="id">
insert into consume (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,
product_code, citys, categorys,
status, created_at, modified_at,
extension1, extension2, keep_at,info_id
)
values
<foreach collection="list" item="c" separator=",">
(#{c.userId}, #{c.userType},
#{c.balanceId}, #{c.orderId}, #{c.amount},
#{c.consumeAt}, #{c.consumeKey}, #{c.consumeToken},
#{c.productCode}, #{c.citys}, #{c.categorys},
#{c.status}, #{c.createdAt}, #{c.modifiedAt},
#{c.extension1}, #{c.extension2}, #{c.keepAt},#{c.infoId}
)
</foreach>
</insert>
And mybatis version is 3.4.2.
There was no problem at shardingsphere version 3.1.0.
----------------------------------------------------------------
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] kimmking commented on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658605188
We had analysis many cases for batch insertion and will fix it in 5.x branch.
And this issue will be close soon.
----------------------------------------------------------------
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] lcx1989210 edited a comment on issue #6351: Just return one ID when batch insert at shardingsphere 4.1.1
Posted by GitBox <gi...@apache.org>.
lcx1989210 edited a comment on issue #6351:
URL: https://github.com/apache/shardingsphere/issues/6351#issuecomment-658565000
I use mybatis as the persistence layer.And the config is :
The picture will invisible sometimes.
<insert id="insetConsumeLog" useGeneratedKeys="true" keyProperty="id">
insert into consume (user_id, user_type,
balance_id, order_id, amount,
consume_at, consume_key, consume_token,
product_code, citys, categorys,
status, created_at, modified_at,
extension1, extension2, keep_at,info_id
)
values
<foreach collection="list" item="c" separator=",">
(#{c.userId}, #{c.userType},
#{c.balanceId}, #{c.orderId}, #{c.amount},
#{c.consumeAt}, #{c.consumeKey}, #{c.consumeToken},
#{c.productCode}, #{c.citys}, #{c.categorys},
#{c.status}, #{c.createdAt}, #{c.modifiedAt},
#{c.extension1}, #{c.extension2}, #{c.keepAt},#{c.infoId}
)
</foreach>
</insert>
And mybatis version is 3.4.2.
----------------------------------------------------------------
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