日期过期时自动删除表中的记录？

I would like to know how can I auto delete a record when date is expired, I'm creating a Air-ticket booking website. I need to delete from my mysql database all the expired flight details. I read somewhere that I can use cron but I have no idea how to do it. Any Help for the script will be very helpful.

我想知道如何在日期过期时自动删除记录，我正在创建一个机票预订网站。我需要从我的mysql数据库中删除所有过期的航班详细信息。我在某处读到了我可以使用cron但我不知道该怎么做。脚本的任何帮助都将非常有用。

6 个解决方案

#1

You may try to use MySQL Events for that:

您可以尝试使用MySQL事件：

CREATE EVENT IF NOT EXISTS `dbName`.`eventName`
ON SCHEDULE
    EVERY 1 DAY // or 1 HOUR
COMMENT 'Description'
DO
    BEGIN

    DELETE FROM `dbName`.`TableName` WHERE `DateCol` < NOW();

    END

NOTE that MySQL Event Scheduler need to be enabled on your server:

请注意，需要在服务器上启用MySQL事件调度程序：

SET GLOBAL event_scheduler = ON;

More info here.

更多信息在这里。

#2

Deleting records is not a good idea, I will suggest you Add ActiveFrom and ActiveTo DateTime Column in your Details table then do not display the expired records in your front end.

删除记录不是一个好主意，我建议你在Details表中添加ActiveFrom和ActiveTo DateTime列，然后不要在前端显示过期的记录。

#3

You need the service to check expired records and delete periodically. You should use this SQL:

您需要该服务来检查过期记录并定期删除。你应该使用这个SQL：

DELETE YourTable WHERE RecordDate < @Expiration

删除YourTable WHERE RecordDate <@Expiration

#4

Cron is most suited in your situation.

Cron最适合您的情况。

Create your php file that deletes or flag the unwanted flight, the script will simply connect to the database, execute your query, and maybe output a short success/failure message.
创建删除或标记不需要的航班的php文件，脚本将只是连接到数据库，执行查询，并可能输出一个简短的成功/失败消息。
Setup the cron job that executes that php script every X hours or whatever you want.
设置每X小时或任何你想要的执行该PHP脚本的cron作业。

If you are using CPanel, you can setup your cron job from it

如果您使用的是CPanel，则可以从中设置您的cron作业

#5

Make use of CRON Jobs. Have a look at the below representation.

利用CRON Jobs。看看下面的表示。

Minutes [0-59]  
|   Hours [0-23]  
|   |   Days [1-31]  
|   |   |   Months [1-12]  
|   |   |   |   Days of the Week [Numeric, 0-6]  
|   |   |   |   |  
*   *   *   *   * home/path/to/command/the_command.sh

If you want to schedule a task to run every Saturday at 8:30am , it would most probably look like this.

如果你想安排一个任务在每个星期六上午8:30运行，它很可能看起来像这样。

30 8 * * 6 home/path/to/command/the_command.sh

Just play around with your settings and you will eventually get it.

只需使用您的设置，您最终将获得它。

#6

If your table currently only has 2 fields, you'll need to add a timestamp field (with default as CURRENT_TIMESTAMP) to be able to tell when the row was added.

如果您的表当前只有2个字段，则需要添加时间戳字段（默认为CURRENT_TIMESTAMP）以便能够判断何时添加了该行。

Then you can run the following MySQL query...

然后你可以运行以下MySQL查询...

DELETE FROM `table` WHERE `timestamp` &gt; DATE_SUB(NOW(), INTERVAL 10 MINUTE)

; You will need to run this as a cron though.

;你需要将它作为cron运行。

As far as I know, it can't be done in MySQL alone.

据我所知，仅在MySQL中无法完成。

#1