本文介绍了SQLite插入或替换的怪异行为的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试增加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插入或替换的怪异行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 04:10