SQLite：自增和插入或忽略将产生未使用的自增键

Question

我正在使用一个带有自增Id和邮件地址的表格Mail。该表格被用在其他4个表格中，主要用于节省存储空间（字符串只保存一次而不是4次）。我使用INSERT OR IGNORE来将邮件地址盲目添加到表格中，如果已经存在则忽略更新。这种方法比使用SELECT ...检查是否存在并进行必要的INSERT要快得多。

对于每次INSERT OR IGNORE，无论是否忽略或执行，自增计数器都会增加。在一次运行中，我大约有500k数据来处理。因此，在每次运行后，最后一个自增键会增加500k。我知道有2^63-1个可能的键，所以需要很长时间才能用完它们。

我也尝试了INSERT OR REPLACE，但这会在每次运行命令时增加数据集的Id，因此根本不是解决方案。 有没有办法防止每次INSERT OR IGNORE都增加自增键？

    mIdMail   mMail
    "1"       ""
    "7"       "[e-mail]"
    "15"      "[e-mail]"
    "17"      "[e-mail]"
    "19"      "[e-mail]"
    "23"      "[e-mail]"
    ...

    INSERT OR IGNORE 
    INTO MAIL 
    ( mMail  ) 
    VALUES ( ? );

    CREATE TABLE IF NOT EXISTS MAIL (
       mIdMail          INTEGER PRIMARY KEY AUTOINCREMENT, 
       mMail            CHAR(90) UNIQUE 
    ); 

- notes-jj

---

这并不回答你的问题，但我猜想你不会使用not exists来提高速度，但我认为内部机制会进行相同的检查，所以它是否更快呢？如果确实更快，一个选项可能是在批处理结束时将下一个增量重置为max+1，这可能会节省一些浪费。否则就接受它吧，浪费掉一些也没关系，但我猜想你担心在你死后会用完 :) - Ab Bennett

@AbBennett 它速度非常快。我没有在这个项目中测量，但在其他使用Mysql/Mariadb的项目中测量过。它是一个命令，由SQLite引擎有效地运行，并且不是两个命令。我正在运行多线程，因此我必须在读取结果和插入之间实现一些锁定，这也会减慢程序的速度。我曾经写过一个有关MySQL/MariaDB的答案：https://stackoverflow.com/a/34360548/5330578。 - notes-jj

很棒。我以前从未使用过这种语法，所以学到了不少。我必须做的一件事是将负增量的负载分成两个表，并在这两个表上包装一个视图。这是一个相当普通的解决方案，但它有效。 - Ab Bennett

Answer 1

为了获得没有间隔的自动递增值，请删除AUTOINCREMENT关键字。(是的，即使没有它，您也可以获得自动递增值。)（请参考这里）。

- CL.

---

很好。我之前没有找到这篇文章。而且它似乎更快。 - notes-jj

Answer 2

自增键的行为方式是由数据库保证的，无论并发事务和事务失败如何。

自增键有两个保证：

• 它们是递增的，因此后面插入的值比前面的值大。
• 它们保证唯一性。

分配键的机制不能保证没有间隙。为什么呢？因为无间隙会给数据库带来更多开销。基本上，表上的每个事务都需要完全串行化（即完成并提交）才能进行下一个事务。从性能角度来看，这通常是一个非常糟糕的想法。

不幸的是，SQLite 没有最简单的解决方案，即在自增键上调用 row_number()。你可以尝试使用触发器实现无间隙自增，但会显著降低应用程序的速度。

我的真正建议是接受间隙，认同它们，投降。这就是内置方法的工作原理，也是有充分理由的。现在设计数据库/应用程序时请记住这一点。

- Gordon Linoff

Answer 3

我遇到了同样的问题，将“INSERT OR IGNORE”更改为“INSERT OR FAIL”解决了问题，因此现在在插入失败时ID值不会递增。

- Pinonirvana