正确使用 SQLite ON CONFLICT 子句 [英] Correct usage of the SQLite ON CONFLICT clause
问题描述
我有一个 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
用于确保所有三列 data
、link
和 <代码>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 没有添加新行或抛出错误,而是使用匹配的 data
ignores 列的值>、link
和 bias_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.
我怀疑这是正确的用法,因为我只是指定了一个复制我指定的 CONSTRAINT
的 CONFLICT
条件.但是,我在文档中的任何地方都看不到这一点的明确解释.我将非常感谢任何能够证实这一点的人.
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屋!