Mysql

如何在多個表中使用相同的時間戳?

  • September 28, 2020

假設 a 有 2 個表,並且都有一個 datetime 列。

在表 A 上,在 datetime 列上,假設 ts1 為非 null,預設為 CURRENT_TIMESTAMP,更新為 CURRENT_TIMESTAMP;

另一個表 B,即日期時間列,假設 ts2 不為空;

兩個表上的日期時間值必須相同 (A.ts1==B.ts2)。

由於 ts1 在插入和更新時自動更新,因此我不需要提前獲取 current_stamp。

現在,如何以 B.ts2 與表 A 上的特定記錄具有相同時間戳值 (A.ts1) 的方式在表 B 上進行插入?

表之間是否存在關係以實現此目的或某些特殊的插入/選擇語句?

抱歉,如果答案很明顯…我是數據庫新手… PS:使用 mySql 5.7 innoDB。

PS2:我發現這行得通,但似乎很尷尬。

insert into B (y, ts2) values ('a value for y', (select ts1 from A where x=1));

還有其他更好的想法嗎?


@Lennart:感謝您的評論。是的,我在設計方面需要幫助。我願意接受建議。

進一步開發:

在表 A 上,x 是一個鍵。此外,ts1 不能是唯一的,因為多條記錄可以具有相同的 ts,因為可能並發訪問以在 A 上創建記錄。

目前,表 B 上沒有鍵。列 ts2 也不能是唯一的,因為表 B 儲存了多個具有相同時間戳 ts2 的 y 值;事實上,表 B 的例外用法類似於:

select * from B where ts2=(select ts1 from A where x=somevalue);  

我喜歡這聽起來如何“添加一個執行更新的觸發器”。你能多開發一下嗎?

PS3:@Lennart,我們開始吧:

CREATE TABLE `device` (
 `deviceID` varchar(12) NOT NULL,
 `snmpSet` varchar(30) NOT NULL,
 `firstSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `lastSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

ALTER TABLE `device`
 ADD PRIMARY KEY (`deviceID`);
COMMIT;

CREATE TABLE `snmpData` (
 `deviceID` varchar(12)  NOT NULL,
 `ts` datetime NOT NULL,
 `oid` varchar(45)  NOT NULL,
 `value` varchar(30)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COMMENT='hold all snmp data from each device';

如果是新設備,將其插入表設備並將其數據添加到 snmpData

INSERT INTO device (deviceID, snmpSet) VALUES ($id, $snmpSet);
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

否則,它是現有設備,我需要更新設備上的 lastSeen,然後在 snmpData 上進行相同的插入

UPDATE device SET lastSeen=current_timestamp() WHERE deviceID=$id;
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

總體構想:該數據庫將支持一個監控工具,用於儲存多個設備 (deviceID) 的 snmp 數據(oid/值對)。表 device 保存有關每個設備的基本資訊,而 snmpData 保存每個設備的數據本身。如果我想檢查從設備收到的最後一個值,我計劃執行以下操作:

select oid, value from snmpData where deviceID=$id and ts=(select lastSeen from device where device=$id);

結果,我將收到一份包含 50 條或更多條記錄的列表,每條記錄對應一個特定的指標及其值。這裡有一些簡化。在實際情況下,兩個表都有更多的列,但你明白了。

警告:寫入表 device 和 snmpData 的程式碼不在同一個邏輯塊上。它是一個 PHP 程序,對桌面設備的操作有自己的 try/catch 塊。如果設備表上的 INSERT/UPDATE 一切正常,那麼在表 snmpData 上的 INSERT 操作還有另一個 try/block。每個塊都有自己的準備/執行狀態,因此無法使用變數技巧“SET @ts := NOW();” @Rick James 建議的。順便說一句,我喜歡它,它對我來說是新的,我將在另一個環境中使用它。謝謝)

編輯:另一種方法

-- remove first- last- seen from device
CREATE TABLE device 
( deviceID varchar(12) NOT NULL primary key
, snmpSet varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

-- add p.k. and f.k. 
CREATE TABLE snmpData 
( deviceID varchar(12)  NOT NULL
, ts datetime NOT NULL
, oid varchar(45)  NOT NULL
, value varchar(30)  NOT NULL
,    constraint pk_snmpData primary key (deviceID, ts) -- assumed 
,    constraint fk_snmpData foreign key (deviceID) 
                           references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

現在,什麼是第一次看到和最後一次看到的?對我來說,這似乎是一種設備的聚合或總結。這種結構有時被稱為增量評估系統

CREATE TABLE device_summary 
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID) 
,   constraint fk_device_summary foreign key (deviceID) 
                                references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

更新 device_summary 的邏輯很容易用觸發器表示:

DELIMITER $$
CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
   -- if device exists, insert will be ignored
   INSERT IGNORE INTO device_summary
       (deviceID, firstSeen, lastSeen)
   VALUES (NEW.deviceID, NEW.ts, NEW.ts);

   -- update lastSeen
   UPDATE device_summary 
       SET lastSeen = NEW.ts
   WHERE deviceID = NEW.deviceId;
END$$
DELIMITER ;

由於設備很可能存在於 device_summary 中,因此先更新並檢查更新的行數(如果為 0,則插入)效率更高

CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
   -- update lastSeen
   UPDATE device_summary 
       SET lastSeen = NEW.ts
   WHERE deviceID = NEW.deviceId;

   IF ( ROW_COUNT() = 0 ) THEN
       -- No rows updated, i.e. the device does not exist
       INSERT INTO device_summary
           (deviceID, firstSeen, lastSeen)
       VALUES (NEW.deviceID, NEW.ts, NEW.ts);
   END IF;    
END

編輯:添加了一個fiddle,擴展了fiddle

要獲取每個設備的最新數據:

select x.oid, x.value 
from snmpData x 
join device_summary y 
   on x.deviceId = y.deviceId 
  and x.ts = y.lastSeen;
BEGIN;
SET @ts := NOW();
INSERT INTO tbl1 ... VALUES (..., @ts, ...);
INSERT INTO tbl1 ... VALUES (..., @ts, ...);
COMMIT;

引用自:https://dba.stackexchange.com/questions/243378