Sqlite插入具有唯一名称的记录，获取ID

Question

Sqlite插入具有唯一名称的记录，获取ID

sqliteuniqueidentityauto-generate

6

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

如果我使用replace，它们的ID (INTEGER PRIMARY KEY) 会更新，这可能会导致我的数据库出现问题（条目指向不存在的ID）
如果我使用ignore，则rowid不会更新，因此我无法得到正确的ID

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

- user34537

5个回答

3

我目前使用以下内容

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';

- user34537

1

“它们必须是唯一的”，是指您确定它们是唯一的，还是说如果它们不是唯一的，您希望得到一个错误结果？如果您将字符串本身作为其表中的键，则我不明白问题1或2如何成为问题-如果出现意外重复，则会得到所需的错误，否则则是正确的ID。也许您可以通过使用SQL代码示例、相关表格、观察到的行为以及您想要的行为来澄清您的问题……？

编辑：感谢编辑，但我仍然不清楚SQL给你哪些问题！如果您的表来自例如：

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

那么，任何尝试插入重复单词的操作都将失败（ABORT关键字是可选的，因为它是UNIQUE的默认值）-- 这不正是你想要的吗？毕竟你说过这些单词“必须是唯一的”，如果它们不是唯一的，那就是一个错误了吧？

- Alex Martelli

我希望last_insert_rowid给我文本的ID（PK），而不是出现错误。到目前为止，替换更改了ID，破坏了先前的条目，并且错误没有返回rowid。看起来我需要分两步完成这个任务？如果不存在，则先进行选择语句，然后进行插入语句。 - user34537

0

你的问题的正确答案是：在sqlite中无法完成此操作。您必须进行额外的选择查询。引用docs中的last_insert_rowid：

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

- spawn

0

2022年也遇到了同样的问题，但自从SQLite3版本3.35.0（2021-03-12）以来，我们有了 RETURNING。结合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 conflict do nothing returning id不会失败，但也不会返回id。
如果可用（再次检查您的触发器），并且如果所有表都使用主键id，那么这种技术不需要任何专业知识：只需复制/粘贴on conflict do update set id = id returning id;

- Mat

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Noah · Accepted Answer

当发生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

解决方法是将列的定义更改为以下内容：

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

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