SQLite：如何避免插入重复行但不失败？

Question

我阅读了关于在SQLite表中防止重复行的问题/答案，建议对必要的列放置约束。然而，据我所知，如果要插入多行，例如在

insert into tableA
select *
from tableB

如果其中一个破坏了约束条件，整个插入操作都会失败。

有没有一种避免重复的方法，可以插入不违反约束条件的行，同时那些违反约束条件的行不被插入？

我的“天真”方法是基于需要唯一的列，在源数据和目标表之间进行内部连接，并删除匹配的源数据行，但我想知道是否有一种方法可以让我将任意数量的行投放到目标表中，而不必添加额外的步骤。

- Julian Drago

Answer 1

您可以使用INSERT OR IGNORE INTO来实现......：

create table tableA(
  col1 int, 
  col2 text unique
);
insert into tableA(col1, col2) values
(1, 'a'), (2, 'b');

create table tableB(
  col1 int, 
  col2 text unique
);
insert into tableB(col1, col2) values
(1, 'a'), (2, 'b'), (3, 'c');

insert or ignore into tableA(col1, col2) 
select col1, col2 
from tableB;

select * from tableA;

请查看演示。
结果：

| col1 | col2 |
| ---- | ---- |
| 1    | a    |
| 2    | b    |
| 3    | c    |

- forpas

Answer 2

您可以使用 INSERT INTO ... SELECT，并使用存在逻辑来检查是否已经存在重复项：

INSERT INTO tableA (col1, col2, col3)
SELECT col1, col2, col3
FROM tableB b
WHERE NOT EXISTS (SELECT 1 FROM tableA a WHERE a.col1 = b.col1 AND a.col2 = b.col2);

假定“重复”是由两个或更多记录具有相同的col1和col2值来确定的。您可以根据需要调整此逻辑。基本思路是整个选择应该运行，仅排除那些您知道会导致约束失败的记录。

- Tim Biegeleisen

---

这是我在帖子中提到的“天真”方法，它肯定会起作用，但我想知道是否有一种不依赖于用户事先检查重复项的方法，即SQLite本身保留正确的行并丢弃其他行。这实际上使约束无意义，因为它防止了约束存在的错误。我想我的问题是关于错误处理的？ - Julian Drago

也许进一步澄清一下，我是用面向对象编程的方式考虑这个问题——一行可能会生成异常，但您可以捕获该异常并允许其继续处理其他行，如果这样说有意义的话。如果在SQLite中确实无法实现，那么这就是正确答案，但我想确认一下。 - Julian Drago

你可以采用我的答案，一次插入一条记录（即每次尝试插入一个事务），或者批量插入，并在出现任何约束违规情况时回滚所有操作。这是我所知道的你的选择。 - Tim Biegeleisen

请注意，您的约束并不无用；它仍然保证没有人可以违反您对数据的唯一限制。只是恰好可能会使插入大量数据变得更具挑战性。 - Tim Biegeleisen