# sqlite数据库默认自增标号RecNo与integer primary key autoincrement的区别？

Android中提供的SQLiteDatabase类，其中的几个方法返回的是rowid，这个rowid并不是插入的行号（row num或者说是RecNo），而是sqlite中自动维护的一个隐藏的列rowid，这个rowid和integer primary key autoincrement的字段有什么区别呢？

## Android中的数据库查询API

Returns:

the row ID ofthe newly inserted row, or -1ifan error occurred

RecNo和id：

rowid和id：

## rowid和id区别

The AUTOINCREMENT Keyword

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. Only ROWID values from previously transactions that were committed are considered. ROWID values that were rolled back are ignored and can be reused.

SQLite keeps track of the largest ROWID that a table has ever held using an internal table named “sqlite_sequence”. The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.

Note that “monotonically increasing” does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

rowid：单调递增，直到达到最大值后，复用已经删除的rowid作为新的值
id：可以自行设置，或者是数据库自动设置（rowid的最大值+1），到达最大值后不复用已经删除的值，抛异常SQLITE_FULL 。

## 特殊情况

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客