SQLite阶段性数据库维护

背景

SQLite利用insert插入数据时，可能发生主键(唯一键)冲突，若想在冲突时变更为update语句，可以借助于SQLite的ON CONFLICT ([key]) DO 语句

语法

INSERT INTO table_name (key ...)
VALUES (val ...)
ON CONFLICT ([key])
DO UPDATE SET key1 = val1, ... , keyn = valn;

创建表secs_ceid, 由此表举例

CREATE TABLE "secs_ceid" (
"ceid"  INT NOT NULL,
"name"  TEXT,
"type"  INT,
"remark"  TEXT,
"isuse"  BOOLEAN NOT NULL DEFAULT (0),
PRIMARY KEY ("ceid" ASC)
); 

INSERT INTO secs_ceid (ceid, name, type, remark, isuse) VALUES (1, 'STATE_CHANGED', 0, '状态改变', 1);
INSERT INTO secs_ceid (ceid, name, type, remark, isuse) VALUES (2, 'OFFLINE', 0, '离线', 1);

ceid[主键]	name	type	remark	isuse
1	STATE_CHANGED	0	状态改变	1
2	OFFLINE	0	离线	1

记录插入

ceid为唯一键，如果此时插入的ceid表中已经存在，则会报冲突异常
当再次执行这条语句时，SQLite引擎将会报错提示

INSERT INTO secs_ceid (ceid, name, type, remark, isuse) VALUES (1, 'STATE_CHANGED', 0, '状态改变', 1);

报错提示如下

[14:14:22] Error while committing new row: UNIQUE constraint failed: secs_ceid.ceid

此时应该借助SQLite的ON CONFLICT ([key]) DO 语句

INSERT INTO secs_ceid (ceid, name, type, remark, isuse) VALUES (1, 'STATE_CHANGED', 0, '状态改变', 1)
ON CONFLICT (ceid)
DO UPDATE SET  name='STATE_CHANGED',type=0,remark='状态改变',isuse=1;

使用场景

• 数据时常变更，需要定时同步到SQLite中，主键/唯一键一般不会修改或较少修改时，该方法可以代替delete+insert。
• 表中某些字段需要插入或阶段性变更时，可以利用ON CONFLICT (key)DO代替insert和update两种操作。