MySQL插入新行，更新重复键，如果一个键发生变化则触发

Question

我正在使用两个表，定义如下。

CREATE TABLE dns_addresses_resolved (
    id INT(13) NOT NULL REFERENCES dns_addresses(id),
    malware_id INT(13) REFERENCES malware_families(id),
    type VARCHAR(10),
    ttl int(10),
    address VARCHAR(50),
    first_resolved DATETIME,
    last_resolved DATETIME,
        PRIMARY KEY (id, address)

)

CREATE TABLE dns_addresses_resolved_archive (
    id INT(13) NOT NULL REFERENCES dns_addresses(id),
    malware_id INT(13) REFERENCES malware_families(id),
    type VARCHAR(10),
    ttl int(10),
    address VARCHAR(50),
    first_resolved DATETIME,    /* When was this IP first seen */
    archived_date DATETIME,     /* The date that the IP changes, and the 'old' IP is archived */
        PRIMARY KEY (id, address)

)

What I'm trying to do is:

1. INSERT row if dns_addresses_resolved.id AND dns_addresses_resolved.resolved_address don't exist
2. ON DUPLICATE KEY {in dns_addresses_resolved} UPDATE last_resolved = NOW()
3. Here's the part I get stuck on. When dns_addresses_resolved.resolved_address changes, move the data currently in that row to dns_addresses_resolved_archive. I was looking at using a trigger to do this last part, but where I get stuck is when just resolved_address changes.

I'm thinking that I came up with two of the three parts, as seen below and I was just wondering of anyone could shed some light on this for me! I know that I could use logic within code to do this, but I'm making life hard on myself because .. well, learning is fun!

INSERT INTO dns_addresses_resolved (id, type, ttl, resolved_address, first_resolved, last_resolved)       VALUES ( ?, ?, ?,

?, NOW(), NOW() ) ON DUPLICATE KEY UPDATE last_resolved = NOW()

CREATE TRIGGER dns_resolved_archive   BEFORE UPDATE ON dns_addresses_resolved.resolved_address    FOR EACH ROW    BEGIN       INSERT

INTO dns_addresses_resolved_archive VALUES ($oldTable.id, $oldTable.malware_id, $oldTable.type, $oldTable.ttl, $oldTable.resolved_address, $oldTable.first_resolved, NOW()); END

I'm also more than open to suggestions if anyone has a more logical way of doing what I'm trying to do. I'd love your input!

编辑:我找到了一个触发器，尽管对“地址”的更新失败，并导致 mysql 错误，我不知道为什么。错误是

ERROR 1054 (42S22): Unknown column 'dns_addresses_resolved.id' in 'field list'

乍一看这似乎完全简单。当我使用触发器运行以下更新时，出现错误。当我在没有触发器的情况下运行相同的查询时，更新工作正常。

update dns_addresses_resolved set address = 'google.com' where id = 1;

下面是我的触发器。

CREATE TRIGGER archive_old_dns_resolution
BEFORE UPDATE ON dns_addresses_resolved
FOR EACH ROW
BEGIN
    IF NEW.address <> OLD.address THEN
        INSERT INTO dns_addresses_resolved_archive
        VALUES ($oldTable.id, $oldTable.malware_id, $oldTable.type, $oldTable.ttl, $oldTable.address, $oldTable.first_resolved, NOW());
    END IF;
END

鉴于触发器似乎是我想要做的事情的最合乎逻辑的解决方案，为什么这个触发器会在更新时导致 SQL 错误？

Answer 1

如果 resolved_address 发生更改，您将不会获得重复项，因为该 id/ 不存在复合主键solved_address 组合。在这种情况下，将会发生直插入。我也不认为 UPDATE 触发器适合您，因为即使您为 id 寻址单独的索引，以便触发更新，您也无法知道更新是否在仅应更新时间戳的情况下或在提供新的 resolved_address 时触发。

我猜测您正在尝试通过对服务器的单个查询来完成此操作，但我不知道您是否有一个干净的解决方案，因为本质上您有两个唯一索引(一个位于 id 和 id、resolved_address 上的一个)，您希望在尝试插入时为已存在的条目提供不同的行为。

我的猜测是，您要么需要创建一个存储过程(如果您想要单个数据库调用)，要么需要使用多个查询来实现您想要做的事情。