时间戳

首页 > 解决方案 > 将两列之间的时间差计算为表中的另一列 AFTER\BEFORE INSERT

mysql - 将两列之间的时间差计算为表中的另一列 AFTER\BEFORE INSERT

问题描述

我有一个用于管理机场的数据库。我想尝试在表 FLIGHT_SCHEDULES 中的任何 INSERT 之后，将 ArrivingDate 和 DepartingDate 列（均为 DATETIME 类型）之间的时间差计算到名为 Flight_time（TIME 类型）的第三列中。

我试图为此创建一个触发器，但没有成功。我已经在互联网上阅读了一些关于我的错误的内容，但找不到可以解决我困境的东西。

This is the table:

CREATE TABLE FLIGHT_SCHEDULES(
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Flight INT(10) UNSIGNED NOT NULL,
    Airplane INT(10) UNSIGNED NOT NULL,
    DepartingDate DATETIME NOT NULL,
    ArrivingDate DATETIME NOT NULL,
    Flight_time TIME DEFAULT '00:00:00',
    CONSTRAINT flight_unique UNIQUE (Flight),
    CONSTRAINT fk_scheduled_flight_id FOREIGN KEY (Flight) REFERENCES FLIGHTS(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_scheduled_airplane_id FOREIGN KEY (Airplane) REFERENCES AIRPLANES(id) ON UPDATE CASCADE ON DELETE CASCADE
);

This is the trigger I created:

delimiter //
CREATE TRIGGER calculate_flightTime2 BEFORE INSERT ON FLIGHT_SCHEDULES FOR EACH ROW
BEGIN
    UPDATE FLIGHT_SCHEDULES
    SET NEW.Flight_time = TIMEDIFF(new.ArrivingDate, new.DepartingDate);
END //

我可以毫无问题地创建触发器，但是我尝试在表中插入新行时收到以下错误消息：

错误代码：1442。无法更新存储函数/触发器中的表“flight_schedules”，因为它已被调用此存储函数/触发器的语句使用。0.000 秒

请注意，我也尝试创建一个 AFTER 触发器，但结果是一样的。

标签： mysqldatabasetriggers

解决方案

通常，将可以从其他人计算出来的值具体化并不是一个好主意。这承担了不一致的风险。例如，如果您更改到达时间会发生什么？

因此，最好删除该列Flight_time。

ALTER TABLE FLIGHT_SCHEDULES
            DROP Flight_time;

为方便起见，您可以创建一个包含计算值的视图。

CREATE VIEW FLIGHT_SCHEDULES_WITH_FLIGHT_TIME
AS
SELECT *,
       timediff(ArrivingDate, DepartingDate) Flight_time
       FROM FLIGHT_SCHEDULES;

在 MySQL 8+ 中，您也可以使用生成的列。这是一种安全的方式，因为 DBMS 可以保证这样的一致性。

ALTER TABLE FLIGHT_SCHEDULES
            ADD Flight_time time AS timediff(ArrivingDate, DepartingDate);

但是如果您坚持使用触发器，则不需要在伪记录UPDATE中设置值。new一个任务就够了。

...
SET NEW.Flight_time = TIMEDIFF(new.ArrivingDate, new.DepartingDate);
...

推荐阅读