本人较菜,这种方法自己想的,没经过啥检验,有啥不对的地方请大神指教。
gorm当中插入一条信息使用的是create的方法,新填入的信息一般有一个自增的id作为主键。在mysql事务当中也会涉及到对create方法的使用,但是这里有一个问题,如果整个事务回退的话,主键的记录是不会回退的,这样的话,下次create的时候,主键就不会是顺序的,这里我就不演示了,具体看一下下表吧
mysql> select * from players;
+----+-------------------------+-------------------------+------------+----------------+------+
| id | created_at | updated_at | deleted_at | name | age |
+----+-------------------------+-------------------------+------------+----------------+------+
| 3 | 2023-04-10 02:32:03.979 | 2023-04-10 02:48:35.729 | NULL | gushuangshuang | 100 |
| 4 | 2023-04-10 02:32:16.453 | 2023-04-10 02:32:16.453 | NULL | u | 100 |
| 5 | 2023-04-10 02:45:52.749 | 2023-04-10 02:45:52.749 | NULL | u | 100 |
| 6 | 2023-04-10 02:46:32.515 | 2023-04-10 02:46:32.515 | NULL | u | 100 |
| 7 | 2023-04-10 02:48:35.721 | 2023-04-10 02:48:35.721 | NULL | u | 100 |
| 8 | 2023-04-10 02:51:39.242 | 2023-04-10 02:51:39.242 | NULL | u | 100 |
| 9 | 2023-04-10 03:02:44.799 | 2023-04-10 03:02:44.799 | NULL | u | 100 |
| 15 | 2023-04-10 06:08:21.008 | 2023-04-10 06:08:21.008 | NULL | zhangsongwen | 19 |
| 16 | 2023-04-10 06:14:19.115 | 2023-04-10 06:14:19.115 | NULL | zhangsongwen | 19 |
| 21 | 2023-04-10 06:15:41.141 | 2023-04-10 06:15:41.141 | NULL | zhangsongwen | 19 |
| 22 | 2023-04-10 06:16:11.440 | 2023-04-10 06:16:11.440 | NULL | zhangsong | 19 |
+----+-------------------------+-------------------------+------------+----------------+------+
上表中,明显看到9和15之间是有主键断层的,其实就是某个事务中创建了10~14的信息,但是回退了,取消了,接下来常见的表就从15开始了,同样的16~21之间也是回退了。这就逼死强迫症了。
我想了一个方法,就是每次遇到这种情况的时候获取max(id),也就是获取目前的顺序主键,然后++,具体写起来就是
var maxID uint
db.Model(&Player{}).Select("max(id)").First(&maxID)
p := Player{
Name: "zhangsong",
Age: 19,
}
p.ID = maxID + 1
db.Create(&p)
这样子的话应该行了,但是具体影不影响性能什么的我也不知道,我尝试使用了explain的方式,看看这种获取max(id)的操作是不是要全表扫描,结果是这样的
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.23 sec)
这type是null,应该不是全表扫描吧