SQLite中的“如果不存在则插入”语句-“Insertifnotexists”statementinSQLite

82  

If you have a table called memos that has two columns id and text you should be able to do like this:

如果你有一个名为memos的表，它有两列id和text，你应该可以这样做：

INSERT INTO memos(id,text) 
SELECT 5, 'text to insert' 
WHERE NOT EXISTS(SELECT 1 FROM memos WHERE id = 5 AND text = 'text to insert');

If a record already contains a row where text is equal to 'text to insert' and id is equal to 5, then the insert operation will be ignored.

如果记录已包含文本等于“要插入的文本”且id等于5的行，则将忽略插入操作。

I don't know if this will work for your particular query, but perhaps it give you a hint on how to proceed.

我不知道这是否适用于您的特定查询，但它可能会提示您如何继续。

I would advice that you instead design your table so that no duplicates are allowed as explained in @CLs answer below.

我建议您改为设计表格，以便不允许重复，如下面的@CLs答案中所述。

330  

If you never want to have duplicates, you should declare this as a table constraint:

如果您从不想要重复项，则应将其声明为表约束：

CREATE TABLE bookmarks(
    users_id INTEGER,
    lessoninfo_id INTEGER,
    UNIQUE(users_id, lessoninfo_id)
);

(A primary key over both columns would have the same effect.)

（两列上的主键都会产生相同的效果。）

It is then possible to tell the database that you want to silently ignore records that would violate such a constraint:

然后可以告诉数据库您要静默忽略违反此类约束的记录：

INSERT OR IGNORE INTO bookmarks(users_id, lessoninfo_id) VALUES(123, 456)

3  

For a unique column, use this:

对于唯一列，请使用以下命令：

INSERT OR REPLACE INTO table () values();

For more information, see: sqlite.org/lang_insert

有关更多信息，请参阅：sqlite.org/lang_insert

2  

insert into bookmarks (users_id, lessoninfo_id)

select 1, 167
EXCEPT
select user_id, lessoninfo_id
from bookmarks
where user_id=1
and lessoninfo_id=167;

This is the fastest way.

这是最快的方法。

For some other SQL engines, you can use a Dummy table containing 1 record. e.g:

对于其他一些SQL引擎，您可以使用包含1条记录的Dummy表。例如：

select 1, 167 from ONE_RECORD_DUMMY_TABLE

-2  

INSERT INTO Database.dbo.Table
     SELECT *
       FROM Database.dbo.Table
      WHERE ID not in (select ID from Database.dbo.Table)