SQLite3中自增主鍵相關(guān)知識(shí)總結(jié)

一、SQLite清空表并將自增列歸零

SQL標(biāo)準(zhǔn)中有TRUNCATE TABLE語(yǔ)句，用來(lái)清空表的所有內(nèi)容。
但SQLite不支持這個(gè)語(yǔ)句。在SQLite中直接使用 DELETE FROM TableName 就可以了。
對(duì)于大多數(shù)DBMS來(lái)說(shuō)，用DELETE不如用TRUNCATE 速度快，因?yàn)門RUNCATE 不用訪問(wèn)整個(gè)表，不用記錄數(shù)據(jù)的變動(dòng)。

SQLite雖然不支持TRUNCATE，但它對(duì)DELETE做了優(yōu)化：
通常在清空表的時(shí)候，還需要把自增列歸零。在SQLite中定義自增列的方法如下：

當(dāng)SQLite數(shù)據(jù)庫(kù)中包含自增列時(shí)，會(huì)自動(dòng)建立一個(gè)名為 sqlite_sequence 的表。
這個(gè)表包含兩個(gè)列：name和seq。name記錄自增列所在的表，seq記錄當(dāng)前序號(hào)（下一條記錄的編號(hào)就是當(dāng)前序號(hào)加1）。
如果想把某個(gè)自增列的序號(hào)歸零，只需要修改 sqlite_sequence表就可以了。

也可以直接把該記錄刪掉：      

二、Sqlite中INTEGER PRIMARY KEY AUTOINCREMENT和rowid的使用

在用sqlite設(shè)計(jì)表時(shí)，突然想到一個(gè)問(wèn)題，就是我設(shè)計(jì)的表中，每個(gè)表都有一個(gè)自己的整形id值作為主鍵，
其實(shí)可以不指定這么一個(gè)id值，sqlite內(nèi)部本來(lái)就會(huì)為每個(gè)表加上一個(gè) rowid，這個(gè)rowid可以當(dāng)成一個(gè)隱含的字段使用，

為什么不直接使用這個(gè)內(nèi)部的rowid作為每個(gè)表的id主鍵呢。
使用自增長(zhǎng)字段為主鍵有不少問(wèn)題，比如維護(hù)或是在大型分布應(yīng)用中主鍵沖突的解決等。
在一些大型分布應(yīng)用中主鍵一般選用guid，這可以有效的避免主鍵沖突，減少對(duì)主鍵維護(hù)的工程。
當(dāng)然，對(duì)于中小型的應(yīng)用，自增長(zhǎng)字段的好處更多一些，簡(jiǎn)單、快速。

Sqlite中，一個(gè)自增長(zhǎng)字段定義為 INTEGER PRIMARY KEY AUTOINCREMENT
那么在插入一個(gè)新數(shù)據(jù)時(shí)，只需要將這個(gè)字段的值指定為NULL，即可由引擎自動(dòng)設(shè)定其值，
引擎會(huì)設(shè)定為最大的rowid+1 當(dāng)然，也可以設(shè)置為非NULL的數(shù)字來(lái)自己指定這個(gè)值，但這樣就必須自己小心，不要引起沖突。

rowid的新值會(huì)這個(gè)最大數(shù)之前隨機(jī)找一個(gè)沒(méi)被使用了的值。所以在rowid達(dá)到最大值前，rowid的值是嚴(yán)格單調(diào)增加的。
INTEGER PRIMARY KEY AUTOINCREMENT 自增長(zhǎng)字段的算法與rowid稍微有些不同。
第一，在達(dá)到最大值后，rowid會(huì)找已被刪除的字段對(duì)應(yīng)的rowid作為新值，而自增長(zhǎng)字段則會(huì)丟出一個(gè)SQLITE_FULL的錯(cuò)誤。
第二，自增長(zhǎng)字段在增加新值時(shí)，是找一個(gè)從沒(méi)被使用過(guò)的rowid作為新值，而rowid則是找最大已存在的rowid+1。

這里對(duì)應(yīng)用的影響會(huì)比較大，尤其是一些對(duì)id值有依賴的元記錄，只適合使用自增長(zhǎng)字段而不能用rowid。
比如，我們?cè)O(shè)計(jì)一個(gè)元記錄表：

最后，我們的應(yīng)用可以根據(jù)這個(gè)一級(jí)表來(lái)產(chǎn)生實(shí)際使用的二級(jí)表。
這樣為保證兼容性meta_struct中的id必須是唯一的，如果有字段被刪除，也不能重復(fù)使用這個(gè)字段的id值，不然，在數(shù)據(jù)庫(kù)合并時(shí)，
一級(jí)表和二級(jí)表就會(huì)混亂。所以meta_struct表中的主鍵只能使用自增長(zhǎng)字段，而不能用rowid。

第三，使用自增長(zhǎng)字段，引擎會(huì)自動(dòng)產(chǎn)生一個(gè)sqlite_sequence表，用于記錄每個(gè)表的自增長(zhǎng)字段的已使用的最大值，用戶可以看到，
并可以用使用Update、Delete和Insert操作，但不建議這么使用，這會(huì)讓引擎混亂。如果使用rowid，也會(huì)有這么一個(gè)內(nèi)部表，用戶可以維護(hù)rowid值，但看不到。