sqlite3 插入问题处理

用insert语句插入数据，为避免重复插入又不打断数据处理。

首先要避免重复插入，就必须在插入时引发冲突。在表中设置了id字段，该字段为UNIQUE属性，当插入的id已存在时引发冲突。

引发冲突后insert会做一些处理，处理方式由OR字句定义。包含如下：

ROLLBACK

当发生约束冲突，立即ROLLBACK，即结束当前事务处理，命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务(除了每一条命令创建的默认事务以外)，则该算法与ABORT相同。

ABORT

当发生约束冲突，命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK，所以前面的命令产生的改变将予以保留。缺省采用这一行为。

FAIL

当发生约束冲突，命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如，若一条UPDATE语句在100行遇到冲突100th，前99行的改变将被保留，而对100行或以后的改变将不会发生。

IGNORE

当发生约束冲突，发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变，且不返回错误信息。

REPLACE

当发生UNIQUE约束冲突，先存在的，导致冲突的行在更改或插入发生冲突的行之前被删除。这样，更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突，导致冲突的NULL值会被字段缺省值取代。若字段无缺省值，执行ABORT算法

为避免操作打断，我选择了IGNORE。最后完整的用法如下：

create table test(
    id integer primary key AUTOINCREMENT,
    s1 text not null default "",
    s2 text not null default "",
    s3 text not null default "",
    s4 text not null default "",
    s5 text not null default "",
    s6 text not null default "1900-01-01 08:00:00",
    s7 text not null default "",
    status text not null default "",
    unique(s1, status)
    );
sqlite> insert  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
sqlite> select * from test
   ...> ;
1|1|||||1900-01-01 08:00:00||0
2|2|||||1900-01-01 08:00:00||0
3|2|||||1900-01-01 08:00:00||1
           

ignore

ignore 插入新数据~时忽略，冲突不会报错，也不执行更新；

sqlite> insert  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
Error: UNIQUE constraint failed: test.s1, test.status
sqlite> insert or ignore  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
sqlite> select * from test
   ...> ;
1|1|||||1900-01-01 08:00:00||0
2|2|||||1900-01-01 08:00:00||0
3|2|||||1900-01-01 08:00:00||1
           

replace

数据被删除，插入新数据，id更新了

sqlite> insert or replace  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
sqlite> select * from test;
7|1|||||1900-01-01 08:00:00||0
8|2|||||1900-01-01 08:00:00||0
9|2|||||1900-01-01 08:00:00||1
           

rollback、fail、abort

中断操作

sqlite> insert or abort  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
Error: UNIQUE constraint failed: test.s1, test.status
sqlite> insert or fail  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
Error: UNIQUE constraint failed: test.s1, test.status
sqlite> insert or rollback  into test(s1, status) values("1", "0"),("2", "0"),("2", "1");
Error: UNIQUE constraint failed: test.s1, test.status
           

last_insert_rowid

获取最后插入的id

sqlite> .headers on
sqlite> select last_insert_rowid() as id from test;
id
9
9
9
sqlite> .mode column
sqlite> select last_insert_rowid() as id from test;
id
----------
9
9
9
sqlite> select distinct last_insert_rowid() as id from test;
id
----------
9