mysql - 根据行值和时间戳计算日期

Question

所以我正在尝试创建一个图书馆数据库，并且想知道是否可以通过使用触发器或类似工具来实现我正在尝试实现的目标。

我有一张名为“类别”的表格，其中包含您可以借阅的不同类别，例如书籍或 dvd。

并且根据您借出的产品类别，您有不同的借出时间，例如您可以借出 3 周的书，但只允许借出 3 天的 dvd。

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | int(11)       | NO   | PRI | NULL    | auto_increment |
| name         | varchar(100)  | NO   |     | NULL    |                |
| time_allowed | time          | NO   |     | NULL    |                |
| days_allowed | varchar(400)  | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+

然后我有表“借用”，看起来像这样

+---------------+-------------------+-------+-----+----------------------+-----------------------------+
| Field         | Type              | Null  | Key | Default              | Extra                       |
+---------------+-------------------+-------+-----+----------------------+-----------------------------+
| id            | int(11)           | NO    | PRI | None                 | auto_increment              |
| item_id       | int(11)           | NO    |     | NULL                 |                             |
| date_borrowed | timestamp         | NO    |     | CURRENT_TIMESTAMP    |                             |
| return_by     | datetime          | NO    |     | None                 |                             | 
| returned      | enum('No','Yes')  | NO    |     | No                   |                             |
| borrower_id   | int(11)           | NO    |     | None                 |                             |
| time_returned | timestamp         | Yes   |     | CURRENT_TIMESTAMP    | ON UPDATE CURRENT_TIMESTAMP |
+---------------+-------------------+-------+-----+----------------------+-----------------------------+

现在我想做的是从“借用”+“time_allowed”+“类别”表中的“days_allowed”中获取“date_borrowed”，为“借用”表中的“return_by”创建日期时间值。

因此，如果“date_borrowed”是“2016-01-01 12:00:00”并且借了一张 DVD，那么“return_by”应该是“2016-01-04 12:00:00”。

这可以通过触发器来完成吗？

非常感谢任何和所有的反馈。

更新:

CREATE TABLE `borrowed` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) DEFAULT NULL,
 `date_borrowed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `return_by` datetime NOT NULL,
 `returned` enum('No','Yes') CHARACTER SET utf8 NOT NULL DEFAULT 'No',
 `borrower_id` int(11) NOT NULL,
 `time_returned` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `borrower_id_idx` (`borrower_id`),
 KEY `item_id_idx` (`item_id`),
 CONSTRAINT `borrowed_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `borrower_id` FOREIGN KEY (`borrower_id`) REFERENCES `borrower` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

---

CREATE TABLE `category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) CHARACTER SET utf8 NOT NULL,
 `time_allowed` time NOT NULL,
 `days_allowed` varchar(400) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

---

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_name` varchar(150) CHARACTER SET utf8 NOT NULL,
 `item_uniqueid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT 'This can be a Serial Number or a ISBN number. But has to be unique for that item',
 `item_status` enum('Ok','Damaged','Broken') CHARACTER SET utf8 NOT NULL,
 `item_comment` longtext CHARACTER SET utf8,
 `item_retired` enum('No','Yes') CHARACTER SET utf8 NOT NULL DEFAULT 'No',
 `item_dop` date NOT NULL COMMENT 'item_dop = Item Date Of Purchaes',
 `item_category` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_category_idx` (`item_category`),
 CONSTRAINT `id_category` FOREIGN KEY (`item_category`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Answer 1

首先，您需要为 return_by 列允许 NULL:

ALTER TABLE `borrowed`
    ALTER `return_by` DROP DEFAULT;
ALTER TABLE `borrowed`
    CHANGE COLUMN `return_by` `return_by` DATETIME NULL AFTER `date_borrowed`;

然后为借来的表创建一个BEFORE INSERT触发器:

DELIMITER //
CREATE TRIGGER `borrowed_before_insert` BEFORE INSERT ON `borrowed` FOR EACH ROW BEGIN
    set new.return_by = (
        select new.date_borrowed 
            + interval c.days_allowed day
            + interval time_to_sec(c.time_allowed) second
        from items i
        join category c on c.id = i.item_category
        where i.id = new.item_id
    );
END//
DELIMITER ;

SQLFiddle-Demo

如果您曾经更新过 date_borrowed 列，您还需要一个 UPDATE 触发器来调整 return_by 值。