SQLite递增主键(Autoincrement)

翻译:https://www.sqlite.org/autoinc.html
 
概述
1 自增主键(TheAUTOINCREMENT keyword)占用额外CPU,内存,磁盘空间,并且增加磁盘I/O的开销(disk I/O overhead),所以如无必要,应该禁用。通常情况下是不需要的。
2 SQLite中,指定其中的一列的属性为INTEGERPRIMARY KEY,和指定为ROWID,效果是一样的(an alias for the ROWID),(除非在创建表的时候,指定了WITHOUT_ROWID),存储类型是64位符号整型。
3 在插入操作中,如果ROWID或者INTEGERPRIMARY KEY列没有指定值,SQLITE将会自动填充一个没有使用的整型,通常大于已经使用的任何一个ROWID值。不管是否已经指定了AUTOINCREMENT关键字,效果还是一样。
4 如果AUTOINCREMENT关键字紧随INTEGEPRIMARY KEY关键字,将会改变ROWID的分配算法,不再使用已经被使用的ROWID值,换句话说,就是AUTOINCREMENT将阻止ROWID的重用,尽管该ROWID已经随着某一行的数据删除,而没有被使用。
 
背景
对于SQLite来说,每一个表的每一行都会有一个64位符号整型ROWID,该ROWID在该表中唯一表示(指定了WITHOUT_ROWID表例外)
 
你可以通过特殊的列名(如ROWID,_ROWID,或者OID)访问SQLITE表中的ROWID的值。除非你在表中定义了与上述相同名称的列名,如果真的定义了,该名字将只会应用到定义的列而不是内置的ROWID.
 
如果表中有定义为INTEGER PRIMARY KEY的属性列,将产生和ROWID同样的效果(an alias for),我想翻译成别名,但是或许没有人理解。这个时候,可以通过四种不同的方式访问ROWID,前三种方式请参考前面一段话,最后一种方式自然是指定了INTEGER PRIMARY KEY属性的那一列!!都是别名而已。
 
当一新行将被插入到SQLITE表中,ROWID可以在插入语句中指定,或者让数据库引擎自动赋值。手动指定ROWID的例子如下:
CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5,'hello');
 
如果在插入语句中没有指定ROWID,或者ROWID指定了一个NULL值,数据库引擎会自定指定一个合适的ROWID值。通用的算法将创建一个新的ROWID,这个值比以前分配的任何ROWID值都大,反映了增长的情况。如果表为空,ROWID就会被指定为1.If the largest ROWID is equal to the largest possibleinteger (9223372036854775807) then the database engine starts picking positivecandidate ROWIDs at random until it finds one that is not previously used.
如果没有找到一个未曾使用的ROWID值,插入操作将会以失败告终,并且返回一个SQLITE_FULL错误码!如果ROWID没有在插入语句中显示指定,自动生成的ROWID值总是比0大。
 
正常的ROWID选择算法会产生单调递增并且唯一的ROWID,只要你没有使用最大的ROWID值,并且也从来没有删除最大的ROWID数据列。如果你删除某些行,或者你曾经创建一个最大的ROWID值,之前被删除行使用的ROWID,可能被新插入的行重用。
 
自增主键
如果某一属性列指定了INTEGER PRIMARY KEYAUTOINCREMENT属性,ROWID选取算法会稍微不同。产生的ROWID比表中已经存在的记录中的ROWID都大。如果之前表为空,那么ROWID就会分配数值1.如果用户指定了ROWID的值是最大值,接下来就不会允许任何的插入操作,与此同时,任何的插入操作都会返回SQLITE_FULL的错误码。Only ROWID values frompreviously transactions that were committed are considered. ROWID values thatwere rolled back are ignored and can be reused.
 
SQLite在内部维护了一个名字叫“sqlite_sequence”的表,记录了一个表已经使用的最大ROWID.不管一个表有没有指定AUTOINCREMENT列的属性值,该表都会被自动创建,然后进行初始化。该表也可以通过一般的UPDATE,INSERT和DELETE语句进行编辑修改。但是修改该表会影响到AUTOINCREMENT生成算法。当你修改的时候,请确保你知道自己正在干嘛!!
 
指定AUTOINCREMENT关键字的实现和默认的实现有所不同。对于AUTOINCREMENT,每列得到的ROWID值对于相同的数据库相同的表来说,都是未曾使用的。生成的ROWID单调递增。对于某些应用这是非常重要的属性。但是如果你的应用程序不需要这些属性,你可以直接采用默认的方式,而不是指定AUTOINCREMENT。毕竟AUTOINCREMENT在插入的过程中需要做一些额外的工作,导致插入的速度有所缓慢。
 
请注意单调递增并不意味着ROWID总是加1(increases by exactly one).增1是最常见的。但是如果在插入的过程中失败了,被分配的ROWID不会被接下来的插入数据重用到,导致了ROWID序列出现缺口。AUTOINCREMENT确保了ROWID是自动递增的,但是没有保证是连续递增的。
 
因为AUTOINCREMENT关键字改变了ROWID的选取算法,所以AUTOINCREMENT不能应用在指定了WITHOUT_ROWID的表或者指定了其他的列为INTEGER PRIMARYKEY.任何尝试应用AUTOINCREMENT关键字在上面两种情况下都会导致错误。
注意:
1)指定了WITHOUT_ROWID属性,就不能再使用AUTOINCREMENT
2) 如果已经有表中的某一列指定了INTEGER PRIMARY KEY属性,AUTOINCREMENT不能修饰表的其他任何一列。

创新互联建站专业为企业提供榆社网站建设、榆社做网站、榆社网站设计、榆社网站制作等企业网站建设、网页设计与制作、榆社企业网站模板建站服务,10年榆社做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。

如下是扩展阅读:
摘自:http://www.jb51.net/article/50049.htm
针对于用户使用自定义的自增主键,还是系统内部维护的ROWID,上面已经描述的非常清楚,并且链接也已经提供了操作ROWID的方法。
现在重点讨论:在大型的分布式应用中,自增主键的使用,当然SQLite数据只能够是一只麻雀,对于我们来说,完全没有任何的大数据量的概念,针对于作者提及的一级表以及二级表的使用概念,有着深远的好奇,但是目前没有任何的操作环境。
摘自文中:使用自增长字段为主键有不少问题,比如维护或是在大型分布应用中主键冲突的解决等。
在一些大型分布应用中主键一般选用guid,这可以有效的避免主键冲突,减少对主键维护的工程。
当然,对于中小型的应用,自增长字段的好处更多一些,简单、快速。
    采用自增主键的理由,在什么情况下,需要建立一个主键,尤其是分布式数据库!!


名称栏目:SQLite递增主键(Autoincrement)
网站网址:http://scyanting.com/article/pgopcd.html