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

假設 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