在SQLite中使用带自动增量键的upsert时如何避免消耗key-id？

h7appiyu 于 2022-11-30 发布在 SQLite

关注(0)|答案(2)|浏览(175)

我有一个如下表：
id是PK和自动增量。

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+

当我使用upsert查询bleow时，和100同时请求该api。

INSERT INTO Views (kind, count)
VALUES(1, 1)
ON CONFLICT(kind)
DO UPDATE SET count = count + 1

我会得到：

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+
| 100 | kind-1 |   100  |
+-----+--------+--------+

2条答案

可以如下操作sqlite_sequence表：

UPDATE sqlite_sequence SET seq=(SELECT MAX(id) FROM Views) WHERE name="Views"

其将“重置”序列到最后使用id。
然而，这一点用处都没有。在批量UPSERT期间更新sqlite_sequence肯定会破坏性能，并且在操作之后这样做也不会避免键序列中的“漏洞”。
另一个选择是重写所有的id，但是您真的需要这样做吗？

我猜你的table是用

create table Views (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind VARCHAR UNIQUE,
  count INTEGER
)

并且运行100次查询INSERT INTO Views (kind, count) VALUES(1, 1) ON CONFLICT(kind) DO UPDATE SET count = count + 1，您不会得到您所写的内容，但是

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
+-----+-------+--------+

可能不需要的行为是，当使用相同的插入查询插入另一个值（例如2）时，您将得到

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
| 101 |     2 |     1  |
+-----+-------+--------+

只需删除create table查询中的AUTOINCREMENT关键字，您就可以得到我认为您想要的结果，即

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
|   2 |     2 |     1  |
+-----+-------+--------+

而且，正如@DinoCoderSaurus所说，AUTOINCREMENT只在棘手的情况下才需要，“以防止重用以前删除的行中的ROWID”（SQLite文档）。