Sqlite 插入具有唯一名称，获取 id

Question

我有一个要插入数据库的字符串列表。 它们必须是独一无二的。 当我插入时，我想要它们的ID（用作另一个表中的外键），所以我使用last_insert_rowid。 我遇到两个问题。

1. 如果我使用替换，他们的ID （整数主键）更新 破坏了我的数据库（条目指向 不存在的 ID）
2. 如果我使用忽略，rowid 不会更新，所以我没有获得正确的 ID

我如何获得他们的 ID？ 如果我不需要，我不想使用 select 语句来检查并插入字符串（如果它不存在）。 我应该怎么做？

Answer 1

当发生 UNIQUE 约束违规时，REPLACE 算法会在插入或更新当前行之前删除导致约束违规的预先存在的行，并且命令继续正常执行。 这会导致 rowid 更改并产生以下问题：

Y:> sqlite3 test
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table b (c1 integer primary key, c2 text UNIQUE);
sqlite> insert or replace into b values (null,'test-1');
sqlite> select last_insert_rowid();
1
sqlite> insert or replace into b values (null,'test-2');
sqlite> select last_insert_rowid();
2
sqlite> insert or replace into b values (null,'test-1');
sqlite> select last_insert_rowid();
3
sqlite> select * from b;
2|test-2
3|test-1

解决方法是更改​​ c2 列的定义，如下所示：

create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);

并删除 或替换insert 中的 子句；

那么在insert之后进行测试时，需要执行以下sql：select last_insert_rowid(),changes();

sqlite> create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);
sqlite> insert into b values (null,'test-1');
sqlite> select last_insert_rowid(), changes();
1|1
sqlite> insert into b values (null,'test-2');
sqlite> select last_insert_rowid(), changes();
2|1
sqlite> insert into b values (null,'test-1');
sqlite> select last_insert_rowid(), changes();
2|0

第三个之后changes的返回值insert 将通知您的应用程序，您需要查找 "test-1" 的 rowid，因为它已经在文件中。 当然，如果这是一个多用户系统，您也需要将所有这些都包装在一个事务中。

Answer 2

我目前使用以下

insert into tbl(c_name) select 'val' where not exists(select id from tbl where c_name ='val');
select id from tbl where c_name ='val';

Answer 3

“它们必须是唯一的”，是否意味着您确定它们是唯一的，或者如果它们不是，您希望出现错误？ 如果您只是将字符串本身作为其表中的键，那么我不明白 1 或 2 怎么会成为问题——如果出现不需要的重复，您将收到所需的错误，否则将收到正确的 ID。 也许你可以用你正在使用的 SQL 代码的一个小例子、有问题的表、你正在观察的行为以及你想要的行为来澄清你的问题......？

编辑：感谢您的编辑，但我仍然不清楚 SQL 给您带来了什么问题！ 如果您的表来自，例如：

CREATE TABLE Foo(
  theid INTEGER PRIMARY KEY AUTOINCREMENT,
  aword TEXT UNIQUE ABORT
  )

那么任何插入重复单词的尝试都将失败（ABORT 关键字是可选的，因为它是 UNIQUE 的默认值） - 不是'鉴于您说“必须是唯一的”，这就是您想要的，即，如果不是，那就是一个错误？

Answer 4

您的问题的正确答案是：这不能在 sqlite 中完成。 您必须进行额外的选择查询。 引用last_insert_rowid的文档：

由于违反约束而失败的 INSERT 不是成功的 INSERT，并且不会更改此例程返回的值。 因此，INSERT OR FAIL、INSERT OR IGNORE、INSERT OR ROLLBACK 和 INSERT OR ABORT 在插入失败时不会更改此例程的返回值

Answer 5

在 2022 年遇到同样的问题，但从 SQLite3 版本 3.35.0 (2021-03-12) 开始，我们有 返回。
结合UPSERT，现在可以实现这一目标

sqlite> create table test (id INTEGER PRIMARY KEY, text TEXT UNIQUE);
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> select * from test;
1|a
2|b
sqlite> insert into test(text) values("b") on conflict do nothing returning id;
sqlite>

，遗憾的是，这仍然是一种解决方法，而不是一个优雅的解决方案...
当发生冲突时，插入变为更新。 这意味着您的更新触发器将会触发，因此您可能需要远离它！

当插入转换为更新时，它需要做一些事情（参见链接）。 然而，我们不想做任何事情，所以我们通过更新 id 本身来执行无操作。
然后，返回 id 给我们我们想要的东西。

注意：

• 我们的空操作实际上会进行更新，因此会花费时间，并且触发器 on update 将触发。 但如果没有触发器，对数据没有影响
• 使用on冲突不做任何返回id不会失败，但不返回id。
• 如果可用（再次检查触发器），并且如果所有表都使用主键 id，那么此技术不需要任何专门化：只需复制/粘贴冲突时更新集 id = id 返回 id;