SQLite插入或替换的怪异行为 [英] Weird behavior with SQLite insert or replace

问题描述

我正在尝试增加SQLite数据库中行的计数(如果该行存在)，或者如果该行不存在，则添加一个新行(在此

I am trying to increment the count of a row in an SQLite database if the row exists, or add a new row if it doesn't exist the way it is done in this SO post. However I'm getting some weird behavior when I try to execute this SQL proc many times in quick succession. As an example I tried running this code:

db = connect_to_db()
c = db.cursor()
for i in range(10):
    c.execute("INSERT OR REPLACE INTO subject_words (subject, word, count) VALUES ('subject', 'word', COALESCE((SELECT count + 1 FROM subject_words WHERE subject = 'subject' AND word = 'word'), 1));")
    db.commit()
db.close()

然后将以下内容插入数据库

And it inserted the following into the database

sqlite> select * from subject_words;
subject|word|1
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2

总共有19个单词的单词'word'与主题'subject'.谁能解释这种奇怪的行为?

Which totals to 19 entries of the word 'word' with subject 'subject'. Can anyone explain this weird behavior?

推荐答案

我认为您不了解INSERT或REPLACE的实际作用.如果无法进行插入，则REPLACE子句才会起作用，因为违反了唯一约束.例如，如果您的subject列是主键.

I don't think you've understood what INSERT OR REPLACE actually does. The REPLACE clause would only come into play if it was not possible to do the insertion, because a unique constraint was violated. An example might be if your subject column was the primary key.

但是，在没有任何主键或其他约束的情况下，插入具有相同主题的多行并不会违反任何内容；因此没有理由调用REPLACE子句.

However, without any primary keys or other constraints, there's nothing being violated by inserting multiple rows with the same subject; so there's no reason to invoke the REPLACE clause.

这篇关于SQLite插入或替换的怪异行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！