正确使用 SQLite ON CONFLICT 子句 [英] Correct usage of the SQLite ON CONFLICT clause

查看:70
本文介绍了正确使用 SQLite ON CONFLICT 子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个 SQLite 数据库,其中包括下表.

I have a SQLite database which, amongst other things, has the following table.

CREATE TABLE IF NOT EXISTS biases
(
 data INTEGER NOT NULL,
 link INTEGER DEFAULT 0,
 bias_type INTEGER,
 ignores INTEGER DEFAULT 0,
 desists INTEGER DEFAULT 0,
 encashes INTEGER DEFAULT 0, 
 accesses INTEGER DEFAULT 0,
 scraps INTEGER DEFAULT 0,
 CONSTRAINT pk_bias_mix PRIMARY KEY(data,link,bias_type)
);  

约束 pk_bias_mix 用于确保所有三列 datalink 和 <代码>bias_type 列.所以假设我这样做

The constraint pk_bias_mix is being used to ensure that no two rows can have the same values for all three columns data, link and bias_type columns. So suppose I do

INSERT INTO biases (data,link,bias_type,ignores) VALUES(1,1,1,1);

事情按预期工作 - 表中插入了一个新行.如果我再次发出相同的 INSERT,我会收到错误

things work as expected - a new row is inserted in the table. If I issue the same INSERT again I get the error

唯一约束失败:biases.data,biases.link,biases.bias_type

UNIQUE CONSTRAINT FAILED: biases.data,biases.link,biases.bias_type

正如预期的那样.我尝试使用 SQLite ON CONFLICT 子句,因此

just as expected. I tried to use the SQLite ON CONFLICT clause thus

INSERT INTO biases (data,link,bias_type,ignores) VALUES(1,1,1,1)
ON CONFLICT(data,link,bias_type) DO UPDATE SET ignores = ignores + 1;

它如我所愿地工作 - SQLite 没有添加新行或抛出错误,而是使用匹配的 dataignores 列的值>、linkbias_type 值.

and it worked as I had hoped - instead of adding a new row or throwing up an error SQLite incremented the value of the ignores column in the row with the matching data, link and bias_type values.

然而,这只是一个实验的结果.我从 SQLite 文档中并不清楚这确实是 ON CONFLICT 应该如何表现 - 即它可以被赋予两个或多个要检查的冲突约束.我所说的两个或多个约束是指在 CONFLICT(...) 中指定多个逗号分隔的列,就像我在上面的示例中所做的那样.

However, this is just the result of an experiment. It is not immediately clear to me from the SQLite docs that this is indeed how ON CONFLICT is supposed to behave - i.e it can be given two or more conflict constraints to be checked. What I mean by two or more constraints is specifying multiple, comma separated, columns inside CONFLICT(...) as I have done in the example above.

我怀疑这是正确的用法,因为我只是指定了一个复制我指定的 CONSTRAINTCONFLICT 条件.但是,我在文档中的任何地方都看不到这一点的明确解释.我将非常感谢任何能够证实这一点的人.

I suspect that this is the right usage since I am merely specifying a CONFLICT condition that replicates my indicated CONSTRAINT. However, I cannot see this explained explicitly anywhere in the docs. I'd be much obliged to anyone who might be able to confirm this.

推荐答案

来自 UPSERT:

UPSERT 是 INSERT 的特殊语法附加,它导致 INSERT如果 INSERT 会违反唯一性约束.

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

和:

特殊的 UPSERT 处理仅针对唯一性约束发生在接收 INSERT 的表上.

The special UPSERT processing happens only for uniqueness constraint on the table that is receiving the INSERT.

所以 DO UPDATE 部分不是由任何约束冲突触发,而是由唯一约束冲突触发.

So the DO UPDATE part is not triggered by any constraint conflict but only by a unique constraint violation.

还有:

出现在ON CONFLICT"和DO"关键字之间的语法被称为冲突目标".冲突目标指定了一个特定的唯一性约束将触发 upsert.

The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert.

因此不可能在一个语句中检查两个或多个冲突约束.
但是,您可以使用单独的 UPSERT 语句来检查 2 种不同的唯一约束违规.

查看简化的演示,其中我又添加了 1 个 UNIQUE 对您的表的约束:

So it is not possible to have two or more conflict constraints to be checked in one statement.
However you can use separate UPSERT statements to check for 2 different unique constraint violations.

See a simplified demo where I added 1 more UNIQUE constraint to your table:

CONSTRAINT con_scraps UNIQUE(scraps)

这篇关于正确使用 SQLite ON CONFLICT 子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆