MySQL默认date()+ 14天，用于列? [英] MySQL default date() + 14 days, for a column?

问题描述

我想知道是否可以通过MySQL进行以下操作，或者是否必须使用PHP来完成.

I was wondering if the following is possible to do through MySQL or will it have to be done using PHP.

任务-到期日期"

1. 用户输入产品名称
2. 用户点击提交表单"按钮
3. 数据已过POST，然后发送到MySQL
4. 到期日期=现在的日期+ 14天

我想要实现的是mysql在表列中插入"expiry_date"的方法，该日期等于在该表中创建该行的日期之后的14天.

What I am trying to achieve is a way for mysql to insert an "expiry_date" in a table column that will equal 14 days after the date the row was created in that table.

例如

product_name - foo
entry_date - 2012-02-01
expiry_date - 2012-02-15

我有一种感觉，除非使用存储过程，否则可能无法在mysql中完成.

I have a feeling it may not be possible to do in mysql unless using a stored procedure.

我很高兴用PHP进行此操作，但是我希望如果可以使用mysql进行操作，那么它将在我的系统中留下更小的错误范围.

I am happy to do it in PHP however I was hoping if I could do it with mysql it would leave less scope for error in my system.

谢谢

推荐答案

创建一个表并为该表设置触发器.

Create a table and set up a trigger for that table.

CREATE TABLE product(
    product_id INT PRIMARY KEY,
    product VARCHAR(40),
    entryDate DATETIME,
    expDate DATETIME
);

CREATE TRIGGER test_trigger BEFORE INSERT ON `product` 
FOR EACH ROW SET
    NEW.entryDate = IFNULL(NEW.entryDate, NOW()),
    NEW.expDate = TIMESTAMPADD(DAY, 14, NEW.entryDate);

每次插入表时，触发器将entryDate设置为当前时间，将expDate设置为14天时间.

On each insert into the table, the trigger sets the entryDate to the current time and expDate to 14 days time.

这篇关于MySQL默认date()+ 14天，用于列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！