sqlite - SQLite 中的 "Insert if not exists"语句

Question

我有一个 SQLite 数据库。我正在尝试在表 bookmarks 中插入值(users_id、lessoninfo_id)，前提是两者之前都不存在。

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+")))

这会报错:

db error near where syntax.

Answer 1

如果您不想重复，您应该将其声明为表约束:

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

(两列的主键具有相同的效果。)

然后可以告诉数据库你想要 silently ignore records that would violate such a constraint :

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