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

Question

我有一个SQLite数据库。我正在尝试将值(users_id, lessoninfo_id)插入到bookmarks表中，仅当它们在一行中之前都不存在。

INSERT INTO bookmarks(users_id,lessoninfo_id) 
VALUES(
    (SELECT _id FROM Users WHERE User='"+$('#user_lesson').html()+"'),
        (SELECT _id FROM lessoninfo 
        WHERE Lesson="+lesson_no+" AND cast(starttime AS int)="+Math.floor(result_set.rows.item(markerCount-1).starttime)+") 
        WHERE NOT EXISTS (
            SELECT users_id,lessoninfo_id from bookmarks 
            WHERE users_id=(SELECT _id FROM Users 
            WHERE User='"+$('#user_lesson').html()+"') AND lessoninfo_id=(
                SELECT _id FROM lessoninfo
                WHERE Lesson="+lesson_no+")))

出现错误，显示：

数据库错误，附近语法有误。

- user2780638

Answer 1

如果您不希望出现重复项，您应将其声明为表约束：

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

(如果两列都设置为主键将具有相同的效果。)

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

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

- CL.

---

什么是获取新插入行或已存在行ID的最佳方法，以便我可以将其插入到另一张表中并建立外键关系？例如，我有两个表person（id，name unique）和cat（person_id，name unique），我想插入许多(person_name，cat_name)对。 - Aur Saraf

读取现有行的ID只能通过SELECT查询实现。但这是一个不同的问题。 - CL.

在“CREATE TABLE”中加入“ON CONFLICT IGNORE”可能会更方便一些。 - defhlt

@rightaway717 "Ignore" 的意思是什么都不会发生；这个问题与更新无关。 - CL.

请记住，使用 INSERT IGNORE 时，如果查询失败，您将失去洞察力，这可能意味着您正在默默地丢失数据。这不是数据库的设计目的。仅在您完全了解后果时使用：INSERT IGNORE。如果可能，请改用 ON CONFLICT DO NOTHING（需要SQLite 3.24+），对于任何旧版本的SQLites，INSERT INTO ... SELECT ... WHERE NOT EXISTS(SELECT 1 ... FROM WHERE ...) 更安全...尽管相当繁琐。 - NeverEndingQueue

Answer 2

如果您有一个名为 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');

如果一条记录已经包含了一个text等于'text to insert'并且id等于5的行，则插入操作将被忽略。

我不知道这是否适用于您特定的查询，但也许它可以给您提供如何继续的提示。

我建议您设计表格，使其不允许重复，如下所述：@CLs answer。

- Cyclonecode

Answer 3

对于唯一的列，请使用以下代码：

INSERT OR REPLACE INTO tableName (...) values(...);

更多信息请参见：sqlite.org/lang_insert

- Ali Bagheri

---

这对我不起作用。它总是像在 insert or replace into my_table (col1, col2) values('1','2'); 中一样插入，会添加多行的 1 2。 - Peter Moore

我想补充一点，如果放置了约束条件 Unique(col1,col2) 并且您还有 col3，那么它确实可以很好地工作，因为插入或忽略会失败，而这将更新 col3 的新值。insert or replace into my_table values('1','2','5') 会替换行 '1','2','3'。 - Peter Moore

Answer 4

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;

这是最快的方法。

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

select 1, 167 from ONE_RECORD_DUMMY_TABLE

- suat dmk

Answer 5

对于我来说，这对于非唯一列（这里是“姓名”）有效。

CREATE TABLE InsertDemo ( ID INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, Name TEXT )
    
INSERT OR IGNORE INTO InsertDemo (ID, Name)    -- insert first row
    VALUES ( ( SELECT ID FROM InsertDemo WHERE Name = 'Miller' ), 'Miller' );    
INSERT OR IGNORE INTO InsertDemo (ID, Name)    -- but no second
    VALUES ( ( SELECT ID FROM InsertDemo WHERE Name = 'Miller' ), 'Miller' );
        
SELECT Count(ID) FROM InsertDemo;   -- returns 1

- Jim Beem