时间戳

首页 > 解决方案 > sqlite3：插入或忽略或更新多个唯一列

sqlite - sqlite3：插入或忽略或更新多个唯一列

问题描述

我的简化数据库如下所示：

CREATE TABLE my_table(hash TEXT, timestamp DATE, value1 TEXT, value2 REAL, UNIQUE(hash, timestamp));

该hash列是行中所有内容的 MD5 哈希。

问题

脚本会将以下示例行放入表中：

INSERT INTO my_table VALUES("7494ab07987ba112bd5c4f9857ccfb3f", "2019-12-19", "temp", 1);

然后脚本将在一小时后运行，并且value2可能会或可能不会更改。这相当于hash改变或保持不变。如果给定日期的哈希值发生了变化，我想用新值更新该行。

举一个具体的例子，下面是三个可能的语句，它们可以在上述语句之后 1 小时运行。

(case 1) INSERT INTO my_table VALUES("7494ab07987ba112bd5c4f9857ccfb3f", "2019-12-19", "temp", 1);

    or...

(case 2) INSERT INTO my_table VALUES("64a5dcbe3f4af29ca58b8d99b1c3a9f2", "2019-12-19", "temp", 2);

    or...

(case 3) INSERT INTO my_table VALUES("514292c37cc92b2ee2cd797328bed2d5", "2019-12-20", "temp 2", 3);
  1. 如果（案例 1）运行，则该INSERT语句应为IGNOREd。
  2. 如果 (case 2) 运行，INSERT语句应该是UPDATE.
  3. 如果（案例 3）运行，该INSERT语句应该正常运行。

对于给定的时间戳（new_timestamp），它可能已经在我的表中，也可能不在我的表中，并且哈希（new_hash）我需要一个语句来做这样的事情：

if new_timestamp in my_table:
    if new_hash == old_hash:
        IGNORE
    else:
        UPDATE
else:
    INSERT

任何帮助是极大的赞赏。

编辑：该行的唯一键timestamp与我最初的问题所说的相反。时间戳永远不会改变。通过观察哈希，我会知道行中的其他一些值发生了变化，但我使用时间戳来确保我回到了正确的行。案例 2 可能是我所关心的替换，但如果哈希更改给定时间戳，我不想为该时间戳创建新行。时间戳将始终是唯一的。

标签： sqlite

解决方案

假设时间戳被定义为唯一的（与当前问题的详细信息不匹配），添加UPSERT子句将满足所有三种情况：

INSERT INTO my_table (hash, timestamp, value1, value2) 
   VALUES ('7494ab07987ba112bd5c4f9857ccfb3f', '2019-12-19', 'temp', 1)
   ON CONFLICT (timestamp) DO UPDATE
   SET hash = excluded.hash, value1 = excluded.value1, value2 = excluded.value2
   WHERE hash != excluded.hash;

（仅供参考，标准字符串分隔符是单引号'，而不是双引号"。）

推荐阅读