sqlite SQL中的数据库缓存或更正自动增量[重复]

0pizxfdo  于 2022-11-24  发布在  SQLite
关注(0)|答案(1)|浏览(174)
    • 此问题在此处已有答案**:

How to get rid of gaps in rowid numbering after deleting rows?(4个答案)
两个月前关门了。
我在SQL(sqlite3 on cmd)中的表中创建了2行,然后删除了其中的1行。

CREATE TABLE sample1( name TEXT, id INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO sample1 VALUES ('ROY',1);
INSERT INTO sample1(name) VALUES ('RAJ');
DELETE FROM sample1 WHERE id = 2;

后来当我插入另一行时,系统给它的id是3而不是2。

INSERT INTO sample1 VALUES ('AMIE',NULL);
SELECT * FROM sample1;

picture of table
我如何纠正它,使下一个值自动获得正确的id?或者我如何清除sql数据库缓存来解决它?

1l5u6lss

1l5u6lss1#

解决您描述的问题的最简单的修复方法是省略AUTOINCREMENT。
你的测试结果就会如你所愿。
但是,仍然会生成rowid(如果指定了INTEGER PRIMARY KEY,则id列是其别名,无论是否指定了AUTOINCREMENT),并且可能比现有的最高id(rowid的别名)大1。
使用和不使用AUTOINCREMENT之间有细微的区别。

  • 如果没有AUTOINCREMENT,则rowid的生成值及其别名将是表的最高现有rowid加1(但不能绝对保证)。
  • 对于AUTOINCREMENT,生成的值将是1加上以下两者中的较高者:-
  • 最高的现有rowid,或
  • 使用的最高rowid
  • 在某些情况下,最高的可能只是短暂的存在

在您的示例中,由于使用了2,因此即使删除了2,2 + 1 = 3。
使用AUTOINCREMENT是低效的,因为要知道上次使用的值是什么,需要一个系统表sqlite_sequence,并且要访问该表以存储最新的id并检索该id。
SQLite AUTOINCREMENT documentation中,这样写道:
AUTOINCREMENT关键字会增加额外的CPU、内存、磁盘空间和磁盘I/O开销,如果不是严格需要,应该避免使用。通常不需要使用该关键字。
还有其他不同之处,例如使用AUTOINCREMENT时,如果已达到ID 9223372036854775807,则再次插入将导致SQLITE_FULL错误。而如果没有AUTOINCREMENT,则会出现未使用的ID(由于当前存储设备无法容纳该数量的行,因此会有一个ID)。

    • id的意图(rowid's)是唯一标识行**,并且如果按id访问行,则能够有效地访问该行。其目的不是将其用作序列/顺序。将其用作序列/顺序编号可能会导致意外的序列或试图维护此类序列/顺序的低效率开销。您应该始终认为行是无序的,除非由对输出进行排序的子句(如ORDER BY子句)进行了特别排序。
    • 但是**,如果您将示例进一步扩展,忽略AUTOINCREMENT,仍可能导致顺序/序列问题,例如,删除ID为1而不是2的行,则最终将得到ID为2和3的行。

也许考虑以下内容,它显示了a)您提出的有限问题如何在没有AUTOINCREMENT的情况下得到解决,以及b)如果删除的不是最高的id,它就不是解决方案:-

DROP TABLE IF EXISTS sample1;
CREATE TABLE IF NOT EXISTS sample1( name TEXT, id INTEGER PRIMARY KEY);
INSERT INTO sample1 VALUES ('ROY',1);
INSERT INTO sample1(name) VALUES ('RAJ');
DELETE FROM sample1 WHERE id = 2;
INSERT INTO sample1 VALUES ('AMIE',NULL);
/* Result 1 */
SELECT * FROM sample1;

/* BUT if a lower than the highest id is deleted */
DELETE FROM sample1 WHERE id=1;
INSERT INTO sample1 VALUES ('EMMA',NULL);
/* Result 2 */
SELECT * FROM sample1;
    • 结果1**(您的问题已解决)

    • 结果2**(如果未删除最高id)

相关问题