mysql - 如何分析，更新行时发生了什么

Question

我有一个这样的表:

| id |   address   |  name  |    oid    | state | event_id | ctrl |
-------------------------------------------------------------------
| 1  | test_addr_1 | test_1 | 25.345.17 |   1   |     0    |  15  |

我需要在更新行中的数据时获取 event_id。

我想做这样的事情:

如果新名称与旧名称不相等 event_id = event_id + 1
如果新的 oid 不等于旧的 oid event_id = event_id + 2
如果新状态不等于旧状态 event_id = event_id + 4
如果新 ctrl 大于旧 ctrl event_id = event_id + 8

# Params to procedure
PROCEDURE Write(IN pAddr VARCHAR(20), IN pName VARCHAR(20), IN pOid VARCHAR(20), IN pState TINYINT, IN pCtrl INT)

#procedure body
SET @ev = 0;
SELECT
CASE
    WHEN name != pName THEN SET @ev = @ev + 1
    WHEN oid != pOid THEN SET @ev = @ev + 2
    WHEN state != pState THEN SET @ev = @ev + 4
    WHEN ctrl > pCtrl THEN SET @ev = @ev + 8
END 
FROM table1 

UPDATE table1 SET ..... , event_id = @ev WHERE address = pAddr

我该怎么做？或者不借助 SQL 会更好吗？

Answer 1

如前所述，audit trigger可以在此处使用以确保捕获所有来源的更改。但是，我建议进行两处更改:

1) 审计表 - 使用其他表来保存审计数据，因为这些表往往会增长并且不建议将操作和审计数据混合在相同的结构中(即使在相同的数据库中) )

2) 使用更友好的更改标志 - 从您的示例来看，您似乎正在将位设置为整数值。虽然这提供了紧凑的数据(在单个整数中捕获许多更改)，但它需要更复杂的操作来查看名称何时更改。审计表可以简单地包含 BIT(1) 列，如 nameChanged、oidChanged 等。

CREATE TRIGGER table1Audit BEFORE UPDATE ON <table1>
FOR EACH ROW BEGIN
    SET @ev = 
        (CASE WHEN OLD.name != NEW.name THEN 1 ELSE 0 END) + 
        (CASE WHEN OLD.oid != NEW.oid THEN 2 ELSE 0 END) + 
        (CASE WHEN OLD.state != NEW.state THEN 4 ELSE 0 END) +  
        (CASE WHEN OLD.ctrl != NEW.ctrl THEN 8 ELSE 0 END)

    -- INSERT INTO someaudittable
    --'table1', @ev
END;