SQLite Autoincrement
来源:互联网 发布:香港hr软件排名 编辑:程序博客网 时间:2024/05/22 17:19
SQLite Autoincrement
In SQLite, every row of every table has an 64-bit signed integer ROWID. The ROWID for each row is unique among all rows in the same table.
You can access the ROWID of an SQLite table using one the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.
If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to theINTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.
When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:
CREATE TABLE test1(a INT, b TEXT);INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with anSQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.
The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
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.
SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. 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.
- SQLite Autoincrement
- Autoincrement In SQLite
- SQLite学习笔记九: NULL, Autoincrement
- SQLite中的自增关键字:AUTO_INCREMENT、INTEGER PRIMARY KEY与AUTOINCREMENT
- sqlite数据库中自增key的设定,autoincrement 和 rowid
- Sqlite中的rowid字段和integer primary key autoincrement的字段区别
- sqlite数据库默认自增标号RecNo与integer primary key autoincrement的区别?
- 3.3.2 AutoIncrement
- Learning Perl: 10.6. Autoincrement and Autodecrement
- mysql-innodb/myisam 批量insert和Autoincrement
- mysqldump - Export structure only without autoincrement
- 在InnoDB和MyISAM中更新Autoincrement列值时的差异
- oracle 没有autoincrement,使用sequence和trigger实现自增
- INTEGER PRIMARY KEY AUTOINCREMENT 和 INTEGER PRIMARY KEY 区别
- SQLite
- SQLite
- SQLite
- SQLite
- jxl
- C++ primer 第十六章
- 正则表达式 -- QQ微信、优酷前端 邮箱正则表达式验证 Bug
- TreeView的子节点和父节点级联选中
- set_ip_bat
- SQLite Autoincrement
- warning: iPhone apps with a deployment target lower than 4.3 should include an armv6 architecture (c
- 在Ubuntu上为Android系统编写Linux内核驱动程序
- project Euler第七题
- java—读取Excel 、导出Excel
- 棒材匹配bug些许
- Cloudera SCM安装配置
- linux 添加删除用户和组
- MAC 下显示或隐藏所有文件脚本