问题描述
我正在尝试增加SQLite数据库中行的计数(如果该行存在),或者如果该行不存在,则添加一个新行(在此帖子.但是,当我尝试快速连续多次执行此SQL proc时,出现了一些奇怪的行为.作为示例,我尝试运行以下代码:
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插入或替换的怪异行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!