在 MySQL 中將字元串更改為日期時間的儲存過程

這是對這個問題的跟進。原來我正在使用的驅動程序（無法更改）與時間戳值不兼容。可以說，我必須將數據作為字元串類型載入到我的 MySQL 8.0 數據庫中。我用來讀取 PLC 並寫入數據庫的軟體中沒有本機功能，可以將字元串更改為時間戳。

我現在想弄清楚的是如何設置一個觸發器，將字元串轉換為 insert 上的時間戳。這個想法是軟體的幫助熱線向我提出的，但如果你有更好的想法，我也願意接受。到目前為止，我有兩列：一列是我“接收”類型為 VARCHAR(30) 的字元串，另一列是我用觸發器寫入時間戳值。

這種設置對我來說感覺非常愚蠢，因為我最終得到了 2 列具有相同數據但格式不同的列，其中一個完全沒用。

到目前為止，我的桌子看起來像：

CREATE TABLE `test` (
 `id` mediumint NOT NULL AUTO_INCREMENT,
 `Date_VARCHAR` varchar(30) DEFAULT NULL,
 `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

觸發器是：

DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT`     
   BEFORE INSERT ON `db_test`.`test` FOR EACH ROW
   BEGIN  
   SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ;
   END$$
DELIMITER ;

有沒有更好的方法來避免兩次擁有相同的數據？我想製作一個觸發器，它只轉換數據並將其全部寫入一列，而不是我想出的設置。

這裡快速瀏覽一下我正在做的事情。我已經插入了一些隨機值，但實際上都是通過 KEPServerEx 的數據記錄器外掛完成的。

根據您可以申請的小提琴

ALTER TABLE test
DROP COLUMN Date_TIMESTAMP,
ADD COLUMN Date_TIMESTAMP TIMESTAMP AS (STR_TO_DATE(Date_VARCHAR, '%Y-%m-%dT%h-%i-%s'));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=104e72ae4730b2c8c30f84f61bf392d7

該列Date_TIMESTAMP可以定義為 STORED（佔用磁碟空間，快速訪問）或 VIRTUAL（每次檢索其值時計算，不佔用磁碟空間）。

---

或者，您可以使用 BEFORE INSERT 和 BEFORE UPDATE 觸發器，它們會將插入的數據轉換為正確的日期時間文字（但該列仍將保持 VARCHAR）。在 BEFORE UPDATE 中，您可以檢查目前數據格式以避免雙重轉換導致錯誤。

CREATE TRIGGER trigger_name  
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.Date_VARCHAR = STR_TO_DATE(NEW.Date_VARCHAR, '%Y-%m-%dT%h-%i-%s');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=08eb56a0b8846297c81c07e2703c0ab9

引用自：https://dba.stackexchange.com/questions/291352