db.Clauses(clause.OnConflict{UpdateAll: true}).Create(users)` 导致主键不连续

[sheng7564]

users := []*User{
		{Username: "usertest", Email: "[email protected]"},
		{ID: 1, Username: "user2", Email: "[email protected]"},
		// 添加更多用户...
	}

	// 批量插入数据
	result := db.Clauses(clause.OnConflict{UpdateAll: true}).Create(users)`

使用这段代码的时候，假如数据库有一行数据，主键ID为1，执行create会又插入又更新，这时候数据库的数据会变成两行，但是在插入下一条数据的时候，主键ID会变成4，而不是3，应该是create执行的时候出现了问题，请问是如何造成的

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ivila]

因为按照你描述的，你插入过程中发生了Conflict（不然就应该是三条数据）。
数据库里面插入数据前会把自增列+1获取自增值，后续操作就算失败了也不会减回去的（也减不回去）。
你可以去了解一下数据库的MVCC逻辑。

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[sheng7564]

因为按照你描述的，你插入过程中发生了Conflict（不然就应该是三条数据）。 数据库里面插入数据前会把自增列+1获取自增值，后续操作就算失败了也不会减回去的（也减不回去）。 你可以去了解一下数据库的MVCC逻辑。

我看了一下，好像是MYSQL insert on duplicate key ，对于及更新又插入会统一计算ID自增，所以会导致主键不连续，不是gorm的问题，gorm底层也是insert on duplicate key

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ivila]

因为按照你描述的，你插入过程中发生了Conflict（不然就应该是三条数据）。 数据库里面插入数据前会把自增列+1获取自增值，后续操作就算失败了也不会减回去的（也减不回去）。 你可以去了解一下数据库的MVCC逻辑。

我看了一下，好像是MYSQL insert on duplicate key ，对于及更新又插入会统一计算ID自增，所以会导致主键不连续，不是gorm的问题，gorm底层也是insert on duplicate key

本来这件事就跟gorm没什么关系，gorm更多的只是一个数据库调用框架（SQL builder），具体的相关逻辑实际上还是要看数据库引擎的实现。这也是为什么上面提到让你去看看数据库的MVCC逻辑。
实际上这个也不是on duplicate key的问题，而是发生了冲突的问题。你尝试着直接INSERT 一个会冲突的数据（或者回滚一个事务），会发现你的下一个ID也是直接出现了一个GAP的。也就是我上面提到的数据库里面插入数据前会把自增列+1获取自增值，后续操作就算失败了也不会减回去的（也减不回去），数据库主键连续这件事本身就是个伪命题，没有引擎会去保证这件事。

[sheng7564]

insert into users values (1,"users","test","2024-10-27",null);
ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' 主键冲突之后我使用 insert into users values (default,"users","test","2024-10-27",null);并没有出现主键不连续的情况，ID是正常的

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ivila]

insert into users values (1,"users","test","2024-10-27",null); ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY' 主键冲突之后我使用 insert into users values (default,"users","test","2024-10-27",null);并没有出现主键不连续的情况，ID是正常的

因为你是指定了自增键。。。。测试不是这么测试的，你改成不指定自增键就好。

[sheng7564]

抱歉，我不知道您这个我该怎么测，我是一个菜鸟

[ivila]

假设你用的是MySQL，你可以按照这样测试，首先创建一张表，带一个主键和一个唯一键（唯一键是为了创建conflict条件）：

CREATE TABLE `test_table` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `value` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE uniq_value(`value`)
) ENGINE=InnoDB;

然后插入一条数据，其ID值应该是1：

 INSERT INTO test_table(value) values(3);

然后插入同样的数据造成冲突后，再插入一个不冲突的数据，第二条成功插入的数据ID应该是3：

// 这条报错ERROR 1062 (23000): Duplicate entry '3' for key 'uniq_value'，然后把ID 2也弄走了
INSERT INTO test_table(value) values(3);
// 这条是成功的，对应ID是3
INSERT INTO test_table(value) values(4);

接着你可以手动开启一个事务后回滚，再插入一条新的

// 这里事务使用了ID 4，然后又不要了
BEGIN;
INSERT INTO test_table(value) values(5);
ROLLBACK;
// 这条数据对应的ID是5
INSERT INTO test_table(value) values(5);

最后的数据应该是这样子：

select * from test_table;

id	value
1	3
3	4
5	5

在MySQL里你还使用show create table test_table可以看到你的下个ID值应该是哪个值，这个时候它的response应该是这样：

[sheng7564]

非常感谢您的例子，学习到了，网络上没有这种例子，真是给我这个菜鸟上了一课

[sheng7564]

我之前出现的问题是干业务的时候出现的，业务表中没有唯一索引，对于批量进行insert 的时候会出现主键不连续的问题，已经解决，https://byzer.csdn.net/6571542eb8e5f01e1e4437f3.html?dp_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpZCI6MTg0MjU2OSwiZXhwIjoxNzMwNjMzNjQwLCJpYXQiOjE3MzAwMjg4NDAsInVzZXJuYW1lIjoibTBfNTI4OTgzODkifQ.0kIMch5-3BcjnbqKvwpL0pat1jRMm8giZCLyYx0RgGQ
对于主键递增还不是很了解，看了您的例子更加了解了相关机制，真是十分感谢。

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨