sql – P.S. Let.Me.Show.You.The.Way

以一个数据表为例, 创建sql如下:

CREATE TABLE http_table (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    Host     TEXT    DEFAULT "",
    IP       TEXT    DEFAULT "",
    Port     INTEGER DEFAULT (0),
    Info     TEXT    DEFAULT "",
    Uptime   TEXT    DEFAULT (datetime('now', 'localtime') ) 
);

当前该表已有不少数据存在，只有id为自增长主键，未创建约束。

之前创建数据过程，先查询（1），不存在新插入一条（2），存在更新该条记录（3）：

（1）先查询

SELECT id FROM http_table WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80' order by Uptime desc LIMIT 1;

（这里，可能是多条，只返回一条）

（2）不存在新插入一条

INSERT INTO http_table (Host, IP, Port, Info) VALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info');

（3）存在更新该条记录

UPDATE http_table SET Info = 'new_info' WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80';

本身这个更新并不严谨，没有指定唯一id条件，可能会更新多条，但这不是要解决的问题。

问题在于在大批量的数据插入时，每次都要查询，再判断是插入还更新，速度很慢，现需要一条sql一次完成查询，并完成插入或更新。

尝试修改：在 SQLite 中，可以使用 INSERT INTO...ON CONFLICT 子句配合 DO UPDATE 动作，实现根据特定条件存在则更新记录，不存在则插入新记录的功能。

INSERT INTO http_table (Host, IP, Port, Info)
VALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info')
ON CONFLICT(Host, IP, Port) DO UPDATE SET
    Info=excluded.Info;

解释：

INSERT INTO http_table (Host, IP, Port, Info) VALUES (...) 尝试插入一条新记录。

ON CONFLICT(Host, IP, Port) 指定当 Host, IP, Port 这三个字段的组合发生冲突时（即已存在这样的记录时）应采取的动作。

DO UPDATE SET Info=excluded.Info 指定当冲突发生时，更新已有记录的 Info 字段。excluded 是一个特殊的表别名，它引用尝试插入但因冲突而未能插入的值。

这样，如果记录已存在，Info 字段将被更新为新的值；如果记录不存在，则将插入新的记录

首先，你需要确保你的表有一个唯一约束（UNIQUE CONSTRAINT）来检测冲突。在这个例子中，需要设定 Host, IP, 和 Port 的组合是唯一的，UNIQUE(Host, IP, Port)否则会报错。

CREATE TABLE http_table(
    Host TEXT,
    IP TEXT,
    Port TEXT,
    Info TEXT,
    UNIQUE(Host, IP, Port)
);

由于之前未添加唯一约束，执行INSERT INTO http_table ... ON CONFLICT(Host, IP, Port) DO UPDATE ... 会如下报错：

执行 SQL 查询时发生错误：ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

解决方法：建立约束前,清洗现有数据

在建立唯一性约束之前，如果现有数据中存在违反该约束的重复记录，你需要进行数据清洗。数据清洗的过程通常包括识别重复记录、决定如何处理这些记录（如删除、合并或修改），以及执行相应的操作。以下是一些步骤和建议，帮助你清洗现有数据以便能够添加唯一性约束：

‌识别重复记录‌：

使用SQL查询来找出在Host、IP和Port列上有重复组合的记录。可以使用GROUP BY和HAVING子句来识别这些重复项。以下查询将找出所有重复的Host、IP和Port组合以及它们出现的次数：

SELECT Host, IP, Port, COUNT(*) as count FROM http_table GROUP BY Host, IP, Port HAVING COUNT(*) > 1;

‌决定如何处理重复记录‌：

‌删除重复项‌：如果重复记录没有保留价值，你可以选择删除它们。确保在删除之前备份数据，以防万一。
‌合并记录‌：如果重复记录包含有用的信息，你可能想要合并它们。这通常涉及更新一条记录以包含所有相关信息，并删除其他重复记录。
‌修改记录‌：在某些情况下，你可能能够通过修改重复记录中的一个或多个字段来消除重复。

‌执行数据清洗操作‌：

根据你的决定，编写SQL语句来执行删除、合并或修改操作。

如果你决定删除重复的记录，并且只保留每个组合的第一条记录，你可以使用以下查询（请注意，这个查询假设你有一个唯一标识每条记录的id字段）：

DELETE FROM http_table WHERE id NOT IN ( SELECT MIN(id) FROM http_table GROUP BY Host, IP, Port );

这个查询会删除每个Host、IP和Port组合中除id最小的一条记录之外的所有记录。

‌验证数据清洗结果‌：

在执行数据清洗操作后，再次运行识别重复记录的查询，确保所有重复项都已被处理。

检查数据以确保没有其他问题，如数据丢失或不一致。

‌添加唯一性约束‌：

确认数据已经清洗干净，没有违反唯一性约束的重复记录，可以安全地添加唯一性约束了。使用ALTER TABLE语句来添加约束：

ALTER TABLE http_table 
ADD CONSTRAINT unique_host_ip_port UNIQUE(Host, IP, Port);

这时又发生错误！

执行 SQL 查询时发生错误：near "CONSTRAINT": syntax error

在SQLite中，ALTER TABLE 语句用于修改表的结构，比如添加列、重命名表、添加约束等。但是，SQLite 在使用 ALTER TABLE 添加约束时有一些限制，特别是关于唯一性约束（UNIQUE CONSTRAINT）。

在SQLite中，如果你想要给一个已经存在的表添加一个新的唯一性约束，并且这个约束涉及到多个列，你不能直接在 ALTER TABLE 语句中使用 ADD CONSTRAINT 语法。SQLite不支持这种直接添加多列唯一性约束的方式。

相反，你需要采取一种间接的方法来实现这一点。以下是一种可能的解决方案：

‌创建一个新表‌：这个新表应该包含与原始表相同的列，并且包含你想要添加的唯一性约束。
‌复制数据‌：将原始表中的数据复制到新表中，同时确保没有违反唯一性约束的数据被插入。
‌删除原始表‌（可选）：如果数据复制成功，并且你确定新表包含了所有需要的数据，你可以删除原始表。
‌重命名新表‌：将新表重命名为原始表的名称。

务必注意：备份数据‌：

在进行任何数据修改或结构更改之前，始终确保你有最新的数据备份。这是防止数据丢失或损坏的重要步骤。

请记住，数据清洗是一个可能对数据产生重大影响的操作。在执行任何删除或修改操作之前，务必仔细考虑并备份你的数据。

最终执行的SQL语句是：

-- 1. 创建一个新表，包含唯一性约束
CREATE TABLE new_http_table (
    id INTEGER PRIMARY KEY, -- 假设原始表有一个id列作为主键
    Host TEXT,
    IP TEXT,
    Port TEXT,
    -- 其他列...
    UNIQUE(Host, IP, Port)
);

-- 2. 复制数据到新表（确保没有重复项）
INSERT INTO new_http_table (id, Host, IP, Port, /* 其他列... */)
SELECT id, Host, IP, Port, /* 其他列... */
FROM http_check_records
WHERE (Host, IP, Port) IN (
    SELECT Host, IP, Port
    FROM http_check_records
    GROUP BY Host, IP, Port
    HAVING COUNT(*) = 1
);

-- 3. （可选）删除原始表
DROP TABLE http_table;

-- 4. 重命名新表为原始表的名称
ALTER TABLE new_http_table  RENAME TO http_table;

分类： sql

对sqlite已有数据,添加多列的唯一性约束

解决方法：建立约束前,清洗现有数据