如何在SQLite中的空选择上插入时设置默认值

Question

我记录了对数据表的所有更改，以便该表存储所做的任何更改的旧值和新值。我正在使用带有select的insert来获取旧值。这很好用，但不遵守旧值的更改记录表默认值。

图式

CREATE TABLE IF NOT EXISTS rovers (
name TEXT PRIMARY KEY,
location TEXT
);
CREATE TABLE IF NOT EXISTS change_record (
rover TEXT NOT NULL,
key TEXT NOT NULL,
timestamp TEXT NOT NULL,
oldValue TEXT DEFAULT "",
newValue TEXT NOT NULL,
PRIMARY KEY(rover, key, timestamp)
);

添加或更新时，将使用以下方法：

INSERT INTO change_record (rover, timestamp, key, oldValue, newValue) VALUES (
"curiosity"
"2019-10-29T09:42:32.865Z",
"location",
(SELECT location FROM rovers WHERE name = "curiosity"),
"surface"
);
INSERT OR REPLACE INTO rovers (name, location) VALUES ("curiosity", "surface")

如果已经有一行表示name = "curiosity"，则oldValue在change_record中设置正确，但如果漫游车不存在，则oldValue设置为NULL。

以下有效，但对我来说似乎效率低下(需要两个选择)：

INSERT INTO change_record (rover, timestamp, key, oldValue, newValue) VALUES (
"curiosity"
"2019-10-29T09:42:32.865Z",
"location",
CASE
WHEN EXISTS (SELECT location FROM rovers WHERE name = "curiosity") THEN
(SELECT location FROM rovers WHERE name = "curiosity"),
ELSE
''
END,
"surface"
);

是否有可能在没有CASE块的情况下做到这一点，以某种方式IFNULL？另外，是否可以使用CREATE TABLE中定义的默认值，而不必复制它？

Answer 1

如果要避免INSERT查询中的 2 个SELECT，则可以使用CTE，如果存在，它将返回location列值，如果不存在，则返回NULL列值。
然后使用COALESCE()将NULL值更改为''：

WITH cte AS (SELECT location FROM rovers WHERE name = 'curiosity')
INSERT INTO change_record (rover, key, timestamp, oldValue, newValue) VALUES (
'curiosity',
'2019-10-29T09:42:32.865Z',
'location',
COALESCE((SELECT location FROM cte), ''),
'surface'
);

我像您的代码一样保留了列的顺序，但如果它不是拼写错误，我认为key和timestamp应该互换，因为它们现在会得到值：分别'2019-10-29T09:42:32.865Z'和'location'。