SQLite3中自增主键相关知识总结

一、SQLite清空表并将自增列归零

SQL标准中有TRUNCATE TABLE语句，用来清空表的所有内容。
但SQLite不支持这个语句。在SQLite中直接使用 DELETE FROM TableName 就可以了。
对于大多数DBMS来说，用DELETE不如用TRUNCATE 速度快，因为TRUNCATE 不用访问整个表，不用记录数据的变动。

SQLite虽然不支持TRUNCATE，但它对DELETE做了优化：
通常在清空表的时候，还需要把自增列归零。在SQLite中定义自增列的方法如下：


代码如下:

CREATE TABLE TableName ( id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar, type Integer );

当SQLite数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。
这个表包含两个列：name和seq。name记录自增列所在的表，seq记录当前序号（下一条记录的编号就是当前序号加1）。
如果想把某个自增列的序号归零，只需要修改 sqlite_sequence表就可以了。


代码如下:

UPDATE sqlite_sequence SET seq = 0 WHERE name='TableName';

也可以直接把该记录删掉：      


代码如下:

DELETE FROM sqlite_sequence WHERE name='TableName';

要想将所有表的自增列都归零，直接清空sqlite_sequence表就可以了：


代码如下:

DELETE FROM sqlite_sequence;

二、Sqlite中INTEGER PRIMARY KEY AUTOINCREMENT和rowid的使用

在用sqlite设计表时，突然想到一个问题，就是我设计的表中，每个表都有一个自己的整形id值作为主键，
其实可以不指定这么一个id值，sqlite内部本来就会为每个表加上一个 rowid，这个rowid可以当成一个隐含的字段使用，

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

Sqlite中，一个自增长字段定义为 INTEGER PRIMARY KEY AUTOINCREMENT
那么在插入一个新数据时，只需要将这个字段的值指定为NULL，即可由引擎自动设定其值，
引擎会设定为最大的rowid+1 当然，也可以设置为非NULL的数字来自己指定这个值，但这样就必须自己小心，不要引起冲突。

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值，但看不到。

赞 (0)

相关推荐

  • SQLite 性能优化实例分享

    最早接触 iOS 开发了解到的第一个缓存数据库就是 SQLite,后面一直也以 SQLite 作为中坚力量使用,以前没有接触到比较大量数据的读写,所以在性能优化方面关注不多,这次对一个特定场景的较多数据批量读写做了一个性能优化,使性能提高了十倍. 大致应用场景是这样: 每次程序启动会从服务器拉取一些数据,对本地数据库两个表进行同步更新,不存在就写入,存在就更新其字段.数据少的时候几十条,多的上千条. 由于缓存的数据可能会存在异步同时读写,所以做了一个后台同步队列,所有的缓存数据库操作都在这个队列

  • android创建数据库(SQLite)保存图片示例

    复制代码 代码如下: //1.创建数据库public class DBService extends SQLiteOpenHelper { private final static int VERSION = 1;private final static String DATABASE_NAME = "uniteqlauncher.db"; public DBService(Context context) {    this(context, DATABASE_NAME, null,

  • SQLite3中的日期时间函数使用小结

    复制代码 代码如下: import sqlite3conn = sqlite3.connect('/tmp/sqlite.db')cur = conn.cursor() 接下来干嘛呢?建一张表吧.这里需要注意的是,SQLite不支持在创建表的同时创建索引,所以要分两步走,先创建表然后再创建索引 复制代码 代码如下: create_table_stmt = '''CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOI

  • SQLite优化方法

    例如:向数据库中插入100万条数据,在默认的情况下如果仅仅是执行 sqlite3_exec(db, "insert into name values 'lxkxf', '24'; ", 0, 0, &zErrMsg); 将会重复的打开关闭数据库文件100万次,所以速度当然会很慢.因此对于这种情况我们应该使用"事务". 具体方法如下:在执行SQL语句之前和SQL语句执行完毕之后加上 rc = sqlite3_exec(db, "BEGIN;"

  • C#操作SQLite方法实例详解

    本文实例讲述了C#操作SQLite方法.分享给大家供大家参考.具体分析如下: 地址: System.Data.Sqlite入手... 首先import/using: 复制代码 代码如下: using System.Data.SQLite; Connection和Command: private SQLiteConnection conn; private SQLiteCommand cmd; 连接db: conn = new SQLiteConnection("Data Source=c:\\t

  • SQLite3中自增主键相关知识总结

    一.SQLite清空表并将自增列归零 SQL标准中有TRUNCATE TABLE语句,用来清空表的所有内容.但SQLite不支持这个语句.在SQLite中直接使用 DELETE FROM TableName 就可以了.对于大多数DBMS来说,用DELETE不如用TRUNCATE 速度快,因为TRUNCATE 不用访问整个表,不用记录数据的变动. SQLite虽然不支持TRUNCATE,但它对DELETE做了优化:通常在清空表的时候,还需要把自增列归零.在SQLite中定义自增列的方法如下: 复制

  • 浅谈MySQL中的自增主键用完了怎么办

    在面试中,大家应该经历过如下场景 面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!"    (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得

  • 深入谈谈MySQL中的自增主键

    深入谈谈MySQL中的自增主键

    MySQL的主键可以是自增的,那么如果在断电重启后新增的值还会延续断电前的自增值吗?自增值默认为1,那么可不可以改变呢?下面就说一下 MySQL的自增值. 特点 保存策略 1.如果存储引擎是 MyISAM,那么这个自增值是存储在数据文件中的: 2.如果是 InnoDB引擎,1)在 5.6之前是存储在内存中,没有持久化,在重启后会去找最大的键值,举个例子,如果一个表当前数据行里最大 id是10,AUTO_INCREMENT=11.这时候,我们删除 id=10 的行,AUTO_INCREMENT 还

  • Oracle数据库中创建自增主键的实例教程

    在设计数据库表的时候发现Oracle没有自增主键的设置,Google了解到Oracle本身并不支持自增主键,需要通过序列(Sequence)和触发器(Trigger)实现. 创建表Student Create Table Student( id number(12) primary key, --通过序列和触发器实现id的自增 name varchar2(20) , age number(3) , sex number(1) ) 创建序列Sequence Create Sequence SEQ_

  • oracle数据库表实现自增主键的方法实例

    目录 一.前言 二.实现主键自动增长 1.创建表格 2.创建自增序列 3.创建触发器 4.测试新增语句 总结 一.前言 几天建表需要用到自增主键,于是使用序列(sequence)和触发器(trigger)来实现主键自增,在网上查了一些知识,顺便记录下: 二.实现主键自动增长 1.创建表格 CREATE TABLE "APP_COMM_T" ( "ID" NUMBER, "BASE_KEY" VARCHAR2(50 BYTE), "BAS

  • Mybatis使用useGeneratedKeys获取自增主键的方法

    摘要 我们经常使用useGenerateKeys来返回自增主键,避免多一次查询.也会经常使用on duplicate key update,来进行insertOrUpdate,来避免先query 在insert/update.用起来很爽,但是经常踩坑,还不知为何.本篇就是深入分析获取自增主键的原理. 问题 首先摘两段我司一些老代码的bug 批量插入用户收藏 for (tries = 0; tries < MAX_RETRY; tries++) { final int result = colle

  • 详解MySQL自增主键的实现

    详解MySQL自增主键的实现

    目录 一.自增值保存在哪儿? 二.自增值修改机制 三.自增值的修改时机 四.自增锁的优化 五.自增主键用完了 一.自增值保存在哪儿? 不同的引擎对于自增值的保存策略不同 1.MyISAM引擎的自增值保存在数据文件中 2.InnoDB引擎的自增值,在MySQL5.7及之前的版本,自增值保存在内存里,并没有持久化.每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+步长作为这个表当前的自增值 select max(ai_col) from table_name

  • 为什么mysql自增主键不是连续的

    为什么mysql自增主键不是连续的

    目录 一 前言 二 自增值存储说明 三 自增值修改机制 四 自增值修改时机 五 导致自增值不连续的原因 5.1 唯一键冲突 5.2 事务回滚 5.3 批量写库操作 六 参考文档 一 前言 提出这个问题,是因为在工作中发现 mysql 中的 user 表的 id 默认是自增的,但是数据库存储的结果却不是连续的. user 表结构: CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id

  • 详解mybatis插入数据后返回自增主键ID的问题

    1.场景介绍: ​开发过程中我们经常性的会用到许多的中间表,用于数据之间的对应和关联.这个时候我们关联最多的就是ID,我们在一张表中插入数据后级联增加到关联表中.我们熟知的mybatis在插入数据后返回的是插入成功的条数,那么这个时候我们想要得到相应的这条新增数据的ID,该怎么办呢? 2.插入数据返回自增主键ID方法(一) 在映射器中配置获取记录主键值xml映射: 在xml中定义useGeneratedKeys为true,返回主键id的值,keyProperty和keyColumn分别代表数据库

  • Mybatis插入时返回自增主键方式(selectKey和useGeneratedKeys)

    目录 Mybatis插入时返回自增主键 Mybatis批量插入返回自增主键 解决办法 Mybatis插入时返回自增主键 通过selectKey在插入操作前或者操作后获取key值,做为字段插入或返回字段.(此段代码获取的序列值id作为字段值插入到实体类中返回) <insert id="insert"> <selectKey keyProperty="id" resultType="int" order="AFTER&qu

随机推荐