SQLITE:替换而不使用 auto_increment [英] SQLITE: Replace without auto_increment

问题描述

我有一个 sqlite 表:例如

I have a sqlite table: for example

CREATE TABLE TEST (id integer primary key, number integer unique, name text); 

但是，我需要在更新时不自动增加 id 字段的情况下进行 REPLACE.

However, i need to make REPLACE without autoincrementing the id field when it updates.

例如:表是空的

REPLACE INTO TEST (null, 23, 'Bill');
REPLACE INTO TEST (null, 52, 'Jack');

当我执行查询时，SELECT * FROM TEST; 我得到...

When I execute the query, SELECT * FROM TEST; I get...

1|23|Bill
2|52|Jack

如果我这样做...

REPLACE INTO TEST VALUES (null, 52, 'John');

从查询 SELECT * FROM TEST; 我得到..

1|23|Bill
3|52|John

但我需要...

1|23|Bill
2|52|John

有人知道如何做到这一点吗?

Does anyone have an idea on how do do this ?

推荐答案

单条命令无法做到这一点.

This is not possible with a single command.

REPLACE 总是在插入新记录之前删除旧记录(如果存在).

REPLACE always deletes the old record (if it exists) before inserting the new one.

要保留自动增量值，您必须保留记录.也就是说，就地更新旧记录，只有在旧记录不存在时才插入新记录:

To keep the autoincrement value, you have to keep the record. That is, update the old record in place, and insert a new one only if no old one existed:

db.execute("UPDATE Test SET Name = 'John' WHERE Number = 52")
if affected_records == 0:
    db.execute("INSERT INTO Test(Number, Name) VALUES(52, 'John')")

这篇关于SQLITE:替换而不使用 auto_increment的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！