blocks|key|2794074|text|id+INTEGER+PRIMARY+KEY本身保证(需要)一个唯一的整数值，如果没有特别赋值，将提供一个，直到最高值达到64位有符号整数(9223372036854775807)的最高允许值，之后可以找到并应用未使用的值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2794075|对于AUTOINCREMENT，保证(如果没有规避)总是提供更高的值，但是如果达到9223372036854775807，而不是分配一个未使用的数字，则将导致SQLITE_FULL错误。从分配数字的角度来看，这是唯一的区别。|2794076|两者都不能保证单调递增的值。|2794077|如果没有自动递增，计算/算法等同于|2794078|2794079|1+%2B+max(rowid)，如果该值大于9223372036854775807，则会尝试查找未使用的较小值。|unordered-list-item|2794080|2794081|-+I've+not+seen+that+anyone+has+come+across+the+situation+where+a+random+unused+value+has+not+been+assigned.|code-block|syntax|javascript|2794082|​|2794083|使用自动递增时，计算/算法为|2794084|从1%2B+max+(行is+)中取较大值或SELECT+seq+|2794085|+sqlite_sequence+WHERE+name+=，如果值大于9223372036854775807，则出现错误。|2794086|注意到，无论哪种方式，最大行is都有可能是最终未被插入的行，因此有可能出现间隙。|2794087|2794088|2794089|最好的答案可能是:最好/推荐将id列仅用于其预期的目的，即有效地标识行，而不是作为处理其他数据需求的手段，如果这样做了，就不需要自动增量(具有overheads)。|BOLD|2794090|总之|2794091|2794092|是否保证id会自动递增？|blockquote|2794093|2794094|2794095|NO|2794096|是否不会重用已删除行的|2794097|值？|2794098|2794099|2794100|给定代码的NO|2794101|适用:-|2794102|CREATE+TABLE+names+(id+INTEGER+PRIMARY+KEY+AUTOINCREMENT+name+TEXT+NOT+NULL)|2794103|如果达到9223372036854775807，则将再次出现NO，否则将导致SQLITE_FULL错误，否则为YES。|2794104|2794105|因此，AUTOINCREMENT只有在插入9223372036854775807行时才真正相关(如果id按预期/预期使用)。|2794106|或许可以考虑以下几点：|2794107|DROP+TABLE+IF+EXISTS+table1;
DROP+TABLE+IF+EXISTS+table2;

CREATE+TABLE+IF+NOT+EXISTS+table1+(id+INTEGER+PRIMARY+KEY,+somecolumn+TEXT);
CREATE+TABLE+IF+NOT+EXISTS+table2+(id+INTEGER+PRIMARY+KEY+AUTOINCREMENT,+somecolumn+TEXT);

INSERT+INTO+table1+VALUES+(9223372036854775807,'blah');+
INSERT+INTO+table2+VALUES+(9223372036854775807,'blah');
INSERT+INTO+table1+(somecolumn)+VALUES(1),(2),(3);+
SELECT+*+FROM+table1;
INSERT+INTO+table2+(somecolumn)+VALUES(1),(2),(3);|2794108|这将创建两个相似的表，唯一的区别是使用了AUTOINCREMENT。每一行都插入了id列的最高允许值。|2794109|然后，尝试在将由SQLite分配id的位置插入3行。|2794110|在不使用AUTOINCREMENT的情况下将3行插入到表中，但在使用AUTOINCREMENT时不插入任何行。详情如下：|2794111|CREATE+TABLE+IF+NOT+EXISTS+table1+(id+INTEGER+PRIMARY+KEY,+somecolumn+TEXT)
>+OK
>+Time:+0.098s


CREATE+TABLE+IF+NOT+EXISTS+table2+(id+INTEGER+PRIMARY+KEY+AUTOINCREMENT,+somecolumn+TEXT)
>+OK
>+Time:+0.098s


INSERT+INTO+table1+VALUES+(9223372036854775807,'blah')
>+Affected+rows:+1
>+Time:+0.094s


INSERT+INTO+table2+VALUES+(9223372036854775807,'blah')
>+Affected+rows:+1
>+Time:+0.09s


INSERT+INTO+table1+(somecolumn)+VALUES(1),(2),(3)
>+Affected+rows:+3
>+Time:+0.087s


SELECT+*+FROM+table1
>+OK
>+Time:+0s


INSERT+INTO+table2+(somecolumn)+VALUES(1),(2),(3)
>+database+or+disk+is+full
>+Time:+0s|2794112|table1的SELECT结果(可能因随机性而有所不同)为:-|2794113|2794114|📷|atomic|2794115|2794116|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/45384919/what-are-the-overheads-of-using-autoincrement-for-sqlite-on-android/45384920#45384920|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/5356d4d28dde21168217f7169c028faa.png|imageAlt^0|0|M|0|2|D|0|0|0|0|0|0|0|0|0|0|0|0|0|0|F|2|1Z|9|0|0|0|0|0|0|0|0|2|0|0|0|0|0|5|2|0|0|0|U|2|1J|3|0|0|0|0|0|15|2|0|G|2|0|0|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|2Y|8|@$9|2Z|A|30|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|31|8|@$9|32|A|33|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|34|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|35|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|36|8|@]|D|@]|E|$]]|$1|M|3|N|5|O|7|37|8|@]|D|@]|E|$]]|$1|P|3|-4|5|6|7|38|8|@]|D|@]|E|$]]|$1|Q|3|R|5|S|7|39|8|@]|D|@]|E|$T|U]]|$1|V|3|W|5|6|7|3A|8|@]|D|@]|E|$]]|$1|X|3|Y|5|6|7|3B|8|@]|D|@]|E|$]]|$1|Z|3|10|5|6|7|3C|8|@]|D|@]|E|$]]|$1|11|3|12|5|O|7|3D|8|@]|D|@]|E|$]]|$1|13|3|14|5|O|7|3E|8|@]|D|@]|E|$]]|$1|15|3|-4|5|6|7|3F|8|@]|D|@]|E|$]]|$1|16|3|-4|5|6|7|3G|8|@]|D|@]|E|$]]|$1|17|3|18|5|6|7|3H|8|@$9|3I|A|3J|B|19]]|D|@$9|3K|A|3L|1|3M]]|E|$]]|$1|1A|3|1B|5|6|7|3N|8|@]|D|@]|E|$]]|$1|1C|3|-4|5|6|7|3O|8|@]|D|@]|E|$]]|$1|1D|3|1E|5|1F|7|3P|8|@]|D|@]|E|$]]|$1|1G|3|-4|5|6|7|3Q|8|@]|D|@]|E|$]]|$1|1H|3|-4|5|6|7|3R|8|@]|D|@]|E|$]]|$1|1I|3|1J|5|6|7|3S|8|@$9|3T|A|3U|B|19]]|D|@]|E|$]]|$1|1K|3|1L|5|6|7|3V|8|@]|D|@]|E|$]]|$1|1M|3|1N|5|1F|7|3W|8|@]|D|@]|E|$]]|$1|1O|3|-4|5|6|7|3X|8|@]|D|@]|E|$]]|$1|1P|3|-4|5|6|7|3Y|8|@]|D|@]|E|$]]|$1|1Q|3|1R|5|6|7|3Z|8|@$9|40|A|41|B|19]]|D|@]|E|$]]|$1|1S|3|1T|5|6|7|42|8|@]|D|@]|E|$]]|$1|1U|3|1V|5|S|7|43|8|@]|D|@]|E|$T|U]]|$1|1W|3|1X|5|6|7|44|8|@$9|45|A|46|B|19]|$9|47|A|48|B|19]]|D|@]|E|$]]|$1|1Y|3|-4|5|6|7|49|8|@]|D|@]|E|$]]|$1|1Z|3|20|5|6|7|4A|8|@]|D|@]|E|$]]|$1|21|3|22|5|6|7|4B|8|@]|D|@]|E|$]]|$1|23|3|24|5|S|7|4C|8|@]|D|@]|E|$T|U]]|$1|25|3|26|5|6|7|4D|8|@$9|4E|A|4F|B|19]]|D|@]|E|$]]|$1|27|3|28|5|6|7|4G|8|@$9|4H|A|4I|B|19]]|D|@]|E|$]]|$1|29|3|2A|5|6|7|4J|8|@]|D|@]|E|$]]|$1|2B|3|2C|5|S|7|4K|8|@]|D|@]|E|$T|U]]|$1|2D|3|2E|5|6|7|4L|8|@]|D|@]|E|$]]|$1|2F|3|W|5|6|7|4M|8|@]|D|@]|E|$]]|$1|2G|3|2H|5|2I|7|4N|8|@]|D|@$9|4O|A|4P|1|4Q]]|E|$]]|$1|2J|3|W|5|6|7|4R|8|@]|D|@]|E|$]]|$1|2K|3|-4|5|6|7|4S|8|@]|D|@]|E|$]]]|2L|$2M|$5|2N|2O|2P|E|$2Q|2R]]|2S|$5|2T|2O|2U|E|$2V|2W|2X|-4]]]]

<code>id INTEGER PRIMARY KEY</code> on it's own guarantees (requires) a unique integer value and will if no value is specifically assigned provide one until the highest value has reached the highest allowed value for a 64 bit signed integer (9223372036854775807) after which an unused value may be found and applied.

With <code>AUTOINCREMENT</code> there is a guarantee (if not circumvented) of always providing a higher value BUT if 9223372036854775807 is reached instead of allocating an unused number an SQLITE_FULL error will result. That is the only difference from the point of view of what number will be assigned.

Neither guarantees a monotonically increasing value.

Without AUTOINCREMENT the calculation/algorithm is equivalent to

<ul>
<li>1 + max(rowid) and if the value is greater than 9223372036854775807 an attempt is made to find an unused and therefore lower value.

<ul>
<li>I've not seen that anyone has come across the situation where a random unused value has not been assigned.</li>
</ul></li>
</ul>

With AUTOINCREMENT the calculation/algorithim is 

<ul>
<li>the greater of 1 + max(rowid) or SELECT seq FROM sqlite_sequence WHERE name = 'the_table_name_the_rowid_is_being_assigned_to' and if the value is greater than 9223372036854775807 then SQLITE_FULL ERROR.</li>
<li>noting that either way there is the possibility that the max rowid is for a row that eventually doesn't get inserted and therefore the potential for gaps.</li>
</ul>

The answer is perhaps best put as: it's best/recommended to use the id column solely for it's intended purpose, that of efficiently identifying a row and not as a means of handling other data requirements, and if done so, there there is no need for AUTOINCREMENT (which has <a href="https://stackoverflow.com/questions/45384919/what-are-the-overheads-of-using-autoincrement-for-sqlite-on-android/45384920#45384920">overheads</a>)

<h1>In short</h1>

<blockquote>
 Does it guarantee that the id will be auto-incremented
</blockquote>

NO

<blockquote>
 values for deleted rows will not be reused?
</blockquote>

NO for the given code

for :-

<pre><code>CREATE TABLE names (id INTEGER PRIMARY KEY AUTOINCREMENT name TEXT NOT NULL)
</code></pre>

again NO as if 9223372036854775807 is reached then an SQLITE_FULL error will result, otherwise YES.

<hr>

So really AUTOINCREMENT is only really relevant (if the id used as expected/intended) when the 9223372036854775807'th row has been inserted. 

Perhaps consider the following :-

<pre><code>DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;

CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, somecolumn TEXT);
CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, somecolumn TEXT);

INSERT INTO table1 VALUES (9223372036854775807,'blah'); 
INSERT INTO table2 VALUES (9223372036854775807,'blah');
INSERT INTO table1 (somecolumn) VALUES(1),(2),(3); 
SELECT * FROM table1;
INSERT INTO table2 (somecolumn) VALUES(1),(2),(3);
</code></pre>

This creates the two similar tables, the only difference being the use of AUTOINCREMENT. Each has a row inserted with the highest allowable value for the id column.

An attempt is then made to insert 3 rows where the id will be assigned by SQLite.

3 rows are inserted into the table without AUTOINCREMENT but no rows are inserted when AUTOINCREMENT is used. as per :-

<pre><code>CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, somecolumn TEXT)
&gt; OK
&gt; Time: 0.098s


CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, somecolumn TEXT)
&gt; OK
&gt; Time: 0.098s


INSERT INTO table1 VALUES (9223372036854775807,'blah')
&gt; Affected rows: 1
&gt; Time: 0.094s


INSERT INTO table2 VALUES (9223372036854775807,'blah')
&gt; Affected rows: 1
&gt; Time: 0.09s


INSERT INTO table1 (somecolumn) VALUES(1),(2),(3)
&gt; Affected rows: 3
&gt; Time: 0.087s


SELECT * FROM table1
&gt; OK
&gt; Time: 0s


INSERT INTO table2 (somecolumn) VALUES(1),(2),(3)
&gt; database or disk is full
&gt; Time: 0s
</code></pre>

The result of the SELECT for table1 (which may differ due to randomness) was :-

<a href="https://i.stack.imgur.com/mzhhe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mzhhe.png" alt="enter image description here"></a>

Consider the following table definition:

<pre><code>CREATE TABLE names (
 id INTEGER,
 name TEXT NOT NULL,
 PRIMARY KEY (id)
)
</code></pre>

Does it guarantee that the <code>id</code> will be auto-incremented for every new insert AND that the values for deleted rows will not be reused?

I looked up in the documentation for Sqlite3, but couldn't find the answer.

Does PRIMARY KEY constraint defined on table level guarantees AUTOINCREMENT and no values reuse?

SQLite

考虑下表的定义：CREATE TABLE names (    id INTEGER,    name TEXT NOT NULL,    PRIMARY KEY (id))它是否保证每次新插入时id都会自动递增，并且删除行的值不会被重用？我在Sqlite3的文档中查找，但找不到答案。

问在表级定义的PRIMARY KEY约束是否保证AUTOINCREMENT且不重用任何值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在表级定义的PRIMARY KEY约束是否保证AUTOINCREMENT且不重用任何值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在表级定义的PRIMARY KEY约束是否保证AUTOINCREMENT且不重用任何值？
EN