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

一个表中只能拥有一个自增列 如果多个列需要自增 其它的列只能通过触发器来完成列值的自增


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

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

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

                         2:INTEGER PRIMARY KEY    整形 主键


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

定义形式一:

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

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

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


插入操作:

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

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

               获取seq值成功:

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

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

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

插入结果:

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

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

跟新seq值:前提插入成功

       没有seq值:

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

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

       有seq值:

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

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


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

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


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

一:drop掉该表  在重建

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

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


定义形式二:

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

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

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

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

           表中没记录 :由1开始

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

        


定义形式一:

创建含有自增列的表

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

插入若干条记录

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

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

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

查看表中记录和seq值

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music     3

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

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music     3



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

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music     4


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

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

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   201


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

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

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   202


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

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

观察表记录和seq值

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   202

干掉自增值最大的2条记录  在插入一条新记录  待插入记录自增值为101 或修改seq为100

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

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

执行(

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

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

) 或insert into music (name,author,note,ower_id) values ('aaaa',"bbb","ccc",101) 


查看表记录和seq值

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   101


清空整个表查看seq值

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   101


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

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music   102


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

drop table music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq

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

insert into music (id,name,author,note,ower_id) values (-120,'aaaa',"bbb","ccc",111)

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music    0

插入一条新记录查看表记录和seq

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

select * from music

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

select * from sqlite_sequence where name='music'

RecNo    name    seq
     1          music    1
发布了100 篇原创文章 · 获赞 42 · 访问量 26万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览