sqlite 自增列 起始值和值不连续的问题

前端之家收集整理的这篇文章主要介绍了sqlite 自增列 起始值和值不连续的问题前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
@H_502_0@一个表中只能拥有一个自增列 如果多个列需要自增 其它的列只能通过触发器来完成列值的自增

@H_502_0@

@H_502_0@自增列: 每次都会按顺序递增,可以保证在一个表里的主键不重复 该列类型为integer

@H_502_0@ 定义形式:定义形式不同 生成序号的规则大方向相同也有差异 各有各的优势

@H_502_0@ 1:INTEGER PRIMARY KEY AUTOINCREMENT 整形 主键 自增

@H_502_0@ 2:INTEGER PRIMARY KEY 整形 主键

@H_502_0@

@H_502_0@seq值: sqlite数据库用来保存表中自增列中存储的最大自增值 新插入的记录自增值默认为该值+1 存储在sqlite_sequence表的seq列中

@H_502_0@定义形式一:

@H_502_0@用seq列保存最大自增值 自动生成的自增值为正数 最小从1开始

@H_502_0@想插入0和负数的自增值必须插入时手动指定

@H_502_0@删除自增值最大那条记录或删除整个表记录时seq值不变 默认新插入的自增值为seq+1

@H_502_0@

@H_502_0@插入操作:

@H_502_0@ 待插入的记录有自增列值并不为null时使用待插入的自增值插入

@H_502_0@ 待插入的记录没有自增列值或自增列值为null时

@H_502_0@ 获取seq值成功:

@H_502_0@ seq值<表中自增列最大值:让表中自增列最大值+1 赋给待插入的自增列插入

@H_502_0@ seq值>=表中自增列最大值:让seq值+1 赋给待插入的自增列插入

@H_502_0@ 获取seq值失败:用初始值1 赋给待插入的自增列插入

@H_502_0@插入结果:

@H_502_0@ 插入失败:在自增列中待插入的自增列值

@H_502_0@ 插入成功:在自增列中没有待插入的自增列值

@H_502_0@跟新seq值:前提插入成功

@H_502_0@ 没有seq值:

@H_502_0@ 待插入的自增值<=0:seq值为0

@H_502_0@ 待插入的自增值>0:seq值为待插入的自增值

@H_502_0@ 有seq值:

@H_502_0@ 待插入的自增值<=seq值:seq值不变

@H_502_0@ 待插入的自增值>seq值:seq值为待插入的自增值

@H_502_0@

@H_502_0@delete 表操作 不会修改seq值 每删除一条记录会记录到事物日志中等待回滚

@H_502_0@truncate 表操作会干掉seq值 事物日志中记录删除表操作 不能回滚 sqlite3不支持

@H_502_0@

@H_502_0@在sqlite3中在逻辑上做到truncate操作执行以下2条语句 不过效率比truncate差

@H_502_0@一:drop掉该表 在重建

@H_502_0@二:1. delete from table 2.update sqlite_sequence set seq=0 where name='table' 或 delete from sqlite_sequence where name='table'

@H_502_0@经过测试2的效率比一微微微高

@H_502_0@

@H_502_0@定义形式二:

@H_502_0@不使用sqlite_sequence表seq列获取最大自增值 而是获取表中自增列中的最大序号

@H_502_0@自动生成的自增值为整数(负数 0 正数)

@H_502_0@删除最大自增值那条记录或整个表记录 新插入的记录的自增值还是递增值

@H_502_0@插入记录时没指定自增值:

@H_502_0@ 表中没记录 :由1开始

@H_502_0@ 表中有记录:自增列中最大值+1 类似于insert into table (column1,column2) values((select max(column1) from table)+1,column2value);

@H_502_0@

@H_502_0@

@H_502_0@定义形式一:

@H_502_0@创建含有自增列的表

@H_502_0@CREATE TABLE music (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,author TEXT,note TEXT,ower_id INTEGER );

@H_502_0@插入若干条记录

@H_502_0@insert into music (name,author,note,ower_id) values ('aaaa',"bbb","ccc",111)

@H_502_0@insert into music (name,111)

@H_502_0@查看表中记录和seq值

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 3 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

@H_502_0@RecNo name seq
1 music 3

@H_502_0@删除自增值最大那条记录 查看表中记录和seq值 得知删除成功且seq值没有发送变化

@H_502_0@delete from music where id=(select max(id) from music)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 3 @H_502_0@

插入一条新记录在看表中的记录和seq值 发现表中自增列中的自增值最大那条记录自增值不是递增的

@H_502_0@insert into music (name,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 4 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 4 @H_502_0@

@H_502_0@修改seq值为200 插入一条新纪录 观察表记录和seq值

@H_502_0@update sqlite_sequence set seq=200 where name='music'

@H_502_0@insert into music (name,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 4 aaaa bbb ccc 111
4 201 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 201 @H_502_0@

@H_502_0@修改seq值为100 插入一条新纪录 观察表记录和seq值

@H_502_0@update sqlite_sequence set seq=100 where name='music'

@H_502_0@insert into music (name,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 4 aaaa bbb ccc 111
4 201 aaaa bbb ccc 111
5 202 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 202 @H_502_0@

@H_502_0@插入一条自增值为100的记录

@H_502_0@insert into music (id,name,ower_id) values (100,'aaaa',111)

@H_502_0@观察表记录和seq值

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 4 aaaa bbb ccc 111
4 100 aaaa bbb ccc 111
5 201 aaaa bbb ccc 111
6 202 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 202 @H_502_0@干掉自增值最大的2条记录 在插入一条新记录 待插入记录自增值为101 或修改seq为100

@H_502_0@delete from music where id=(select max(id) from music)

@H_502_0@delete from music where id=(select max(id) from music)

@H_502_0@执行(

@H_502_0@update sqlite_sequence set seq=100 where name='music'

@H_502_0@insert into music (name,111)

@H_502_0@) 或insert into music (name,101)

@H_502_0@

查看表记录和seq值 @H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 1 aaaa bbb ccc 111
2 2 aaaa bbb ccc 111
3 4 aaaa bbb ccc 111
4 100 aaaa bbb ccc 111
5 101 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 101 @H_502_0@

@H_502_0@清空整个表查看seq值

@H_502_0@select * from sqlite_sequence where name='music'

@H_502_0@RecNo name seq
1 music 101

@H_502_0@

@H_502_0@插入新记录查看表记录和seq值

@H_502_0@insert into music (name,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 102 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 102 @H_502_0@

@H_502_0@干掉重建表 查看seq值为空 表示在插入记录时id为1

@H_502_0@drop table music

@H_502_0@CREATE TABLE music (id INTEGER PRIMARY KEY AUTOINCREMENT,ower_id INTEGER );

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
@H_502_0@插入一条id为-120的记录查看表记录和seq

@H_502_0@insert into music (id,ower_id) values (-120,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 -120 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq
1 music 0 @H_502_0@插入一条新记录查看表记录和seq

@H_502_0@insert into music (name,111)

@H_502_0@select * from music

@H_502_0@RecNo id name author note ower_id
1 -120 aaaa bbb ccc 111
1 1 aaaa bbb ccc 111

@H_502_0@select * from sqlite_sequence where name='music'

RecNo name seq 1 music 1

猜你在找的Sqlite相关文章