INSERT OR REPLACE
会将其他字段替换为默认值。
CREATE TABLE Book (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT,
TypeID INTEGER,
Level INTEGER,
Seen INTEGER
);
INSERT INTO Book VALUES (1001, 'C++', 10, 10, 0);
sqlite> INSERT OR REPLACE INTO Book(ID, Name) VALUES(1001, 'SQLite');
sqlite> SELECT * FROM Book;
1001|SQLite|||
如果您想保留其他字段
sqlite> SELECT * FROM Book;
1001|C++|10|10|0
sqlite> INSERT OR IGNORE INTO Book(ID) VALUES(1001);
sqlite> UPDATE Book SET Name='SQLite' WHERE ID=1001;
sqlite> SELECT * FROM Book;
1001|SQLite|10|10|0
使用UPSERT(UPSERT
在SQLite 3.24.0版本(2018-06-04)中添加)
INSERT INTO Book (ID, Name)
VALUES (1001, 'SQLite')
ON CONFLICT (ID) DO
UPDATE SET Name=excluded.Name;
前缀excluded.
等于VALUES
中的值('SQLite'
)。
UPSERT
是什么意思? - ashleedawg