两种方式实现SQLite 主键自动增加

SQLite 主键自动增加(autoincrease)可以实现,经过测试,有以下两种方法:
下面所有的代码和结果,都是真实实验的结果。

1、使用类似 MySQL 的 autoincrease 参数,例如:
Create table meta_struct(id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar, type Integer);
程序修改(DataAccess.cs第84行):
string sql = "CREATE TABLE " + tableName + " (";
改为
string sql = "CREATE TABLE " + tableName + " (ids INTEGER PRIMARY KEY AUTOINCREMENT, ";
其他都不需要修改。

2、系统默认有一个伪列(rowid)已经实现了自增,可以当做主键来使用,不需要修改程序。

PS:
autoincrease使用的是MySQL语法,所以,处理起来和MySQL数据库一模一样。
rowid(伪列)使用的是Oracle的特性,所以,处理起来和Oracle一模一样。
C#版的SQLite继承了,MySQL和Oracle的特性。
Java版的SQLite(即,常用的sqlitejdbc.jar),不能直接使用autoincrease,需要借助native方法(即 sqlitejdbc.dll)。
内部机理,使用的是Oracle序列(sequence),来实现自增。

http://www.cnblogs.com/peida/archive/2008/11/29/1343832.html

在用sqlite设计表时,突然想到一个问题,就是我设计的表中,每个表都有一个自己的整形id值作为主键,其实可以不指定这么一个id值,sqlite内部本来就会为每个表加上一个 rowid,这个rowid可以当成一个隐含的字段使用,但是由sqlite引擎来维护的,在3.0以前rowid是32位的整数,3.0以后是64位的整数,为什么不直接使用这个内部的rowid作为每个表的id主键呢。

想到就立即先查找一下sqlite的文档,看看用指定INTEGER PRIMARY KEY AUTOINCREMENT 和不指定自增长字段用rowid有什么区别。相关的文档在这里:
http://www.sqlite.org/autoinc.html

http://www.sqlite.org/faq.html

使用自增长字段为主键有不少问题,比如维护或是在大型分布应用中主键冲突的解决等。在一些大型分布应用中主键一般选用 guid,这可以有效的避免主键冲突,减少对主键维护的工程。当然,对于中小型的应用,自增长字段的好处更多一些,简单、快速。

Sqlite中,一个自增长字段定义为INTEGER PRIMARY KEY AUTOINCREMENT        ,那么在插入一个新数据时,只需要将这个字段的值指定为NULL,即可由引擎自动设定其值,引擎会设定为最大的rowid+1。当然,也可以设置为非 NULL的数字来自己指定这个值,但这样就必须自己小心,不要引起冲突。当这个rowid的值大于所能表达的最大值 9223372036854775807 (3.0及以后版本的rowid最大值)后,rowid的新值会这个最大数之前随机找一个没被使用了的值。所以在rowid达到最大值前,rowid的值是严格单调增加的。

INTEGER PRIMARY KEY AUTOINCREMENT 自增长字段的算法与rowid稍微有些不同。


第一,在达到最大值后,rowid会找已被删除的字段对应的rowid作为新值,而自增长字段则会丢出一个SQLITE_FULL的错误。

第二,自增长字段在增加新值时,是找一个从没被使用过的rowid作为新值,而 rowid则是找最大已存在的rowid+1。这里对应用的影响会比较大,尤其是一些对id值有依赖的元记录,只适合使用自增长字段而不能用rowid。
比如,我们设计一个元记录表:

Create table meta_struct(id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar, type Integer);


然后,定义一个一级表,来描述其它表的结构:


Create table meta_table(tableid INTEGER, table_field integer)

最后,我们的应用可以根据这个一级表来产生实际使用的二级表。
这样为保证兼容性meta_struct中的id必须是唯一的,如果有字段被删除,也不能重复使用这个字段的id值,不然,在数据库合并时,一级表和二级表就会混乱。所以meta_struct表中的主键只能使用自增长字段,而不能用rowid。

第三,使用自增长字段,引擎会自动产生一个sqlite_sequence表,用于记录每个表的自增长字段的已使用的最大值,用户可以看到,并可以用使用Update、Delete和Insert操作,但不建议这么使用,这会让引擎混乱。如果使用rowid,也会有这么一个内部表,用户可以维护rowid值,但看不到。

这么看来,如果直接使用rowid来代替自增加字段,根据两者的细微的差别,需要注意是否与自己的应用冲突,如果没有冲突,那么用rowid会更快一点。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页