SQLite3 小技巧

rowid 和自增列

每个表默认都有 rowid 列，除非创建表时指定了 WITHOUT ROWID, 它现在是 64 位长的。在查询时用 select * from table1 里没有它，要显式的用 select rowid, * from table1 就会列出它来。
ROWID 除了可用 rowid 查出它之外，还可用别名 ROWID 和 OID，都不分大小写的, 例如 select oid from table1。另外我们还可以自定义一个 ROWID 的别名，用 INTEGER PRIMARY KEY 标识的列也是 ROWID 的一个别名，比如我们用 id 来作为 ROWID 的别名。

ROWID 的表示也是个自增列，需要注意的是 SQLite 的 ROWID 是可以自己赋值的。

自增列序列表(也是 ROWID)

用 INTEGER PRIMARY KEY AUTOINCREMENT 标识的列就是个自增列，说到底它也是 ROWID 别名。数据库中存在自增列后，SQLite 就会创建一个 sqlite_sequence 表。所有表的自增列都共享这个表，sqlite_sequence 分别维护着每个自增列的当前值，所以自增列的计数也是单独的。

自增列的好处就能查看到当前的序列值，sqlite_sequence 中的值也是可以修改的，不过一般人不会这么干，可以用 select last_insert_rowid() 得到刚刚插入的 ROWID 值。

示例

有一个数据库，其自增列为 text

这个时候要实现查找某一项的下一条记录可以这样实现

// rowid 为 10 的下一条记录
SELECT rowid,* FROM table WHERE rowid > 10 ORDER BY rowid ASC LIMIT 1
// rowid 为 10 的上一条记录
SELECT rowid,* FROM table WHERE rowid< 10 ORDER BY rowid DESC LIMIT 1

复合主键和多主键

符合主键就是将多个字段的值组合起来作为主键。对于“主键是唯一的索引”，这个时候用于组成主键的字段值可以是相同的，但是组合起来的值必定是唯一的
比如表 A 有三个字段a，b，c。将a，b设置为联合组建，a=”hehe”, b=”hehe”，这是可以的，但是 a 和 b 组成的键值 “hehehehe” 在表中必然是唯一的。
可以用如下方式创建联合主键

sqlite> create table t2 (id1 int , id2 int, col varchar(20), constraint pk_t2 primary key (id1,id2));
// 或者
sqlite> create table t2 (id1 int , id2 int, col varchar(20), primary key (id1,id2));

这个时候 pk_t2 必然是唯一的，但是 id1 和 id2 的值可能相同。需要注意，创建联合主键时，创建联合主键的代码要放在最后，否则会创建失败

另外还存在一种双主键的情况，其实就是存在多个字段有唯一（unique）索引

sqlite> create table t1 (id int primary key, col varchar(20));  
sqlite> create unique index uk_t1 on t1 (col); 

参考文章

《SQLite 使用主键，ROWID 及自增列》