如何在时间戳字段过期后自动更新 MySQL [英] How to AUTO update MySQL after timestamp field expierd

问题描述

如何在过期时间戳时自动更新或插入 MySQL?

How do I automate MySQL Update or Insert upon expire timestamp?

所以假设时间戳是 2013-06-30 20:10:00，我想在通过该日期和时间后自动更新 MySQL DB，所以今天 2013-06-03 20:10:00.

So let say timestamp is 2013-06-30 20:10:00 and I would like to auto update MySQL DB upon passing that date and time so today after 2013-06-03 20:10:00.

我想更新我的项目，但我的网站无法打开浏览器，所以我想我需要某种服务器触发器??我怎么做?非常感谢

I want to update my item, but I will not have browser open with my website, so I think so I need some kind of server trigger?? How do I do that? Many thanks

我有以下查询每 1 秒执行一次，但它不起作用:

I have following Query to execute every 1 second and it doesn't work:

CREATE EVENT e_second
    ON SCHEDULE
      EVERY 1 SECOND
    DO
UPDATE online_auctions.auction_status
   SET auction_status = 'ENDED' WHERE TIMESTAMP(auction_end_date) < (select now());

推荐答案

Use can be used for that

Use can use for that

1. Mysql events(恕我直言，最佳人选)
2. cron 作业或 Windows 任务计划程序(如果您使用的是 Windows 平台)

1. Mysql events (IMHO the best candidate)
2. cron job or Windows Task Scheduler (if you're on Windows platform)

如果您选择选项 1，您需要创建一个事件

If you go with option 1 you need to create an event

CREATE EVENT myevent 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
UPDATE myschema.mytable 
   SET mycol = mycol + 1;

使用 SHOW PROCESSLIST 检查是否启用了事件调度程序.如果它是 ON，您应该看到用户event_scheduler"的进程Daemon".如果当前未启用调度程序，请使用 SET GLOBAL event_scheduler = ON; 启用它.有关配置事件调度程序的更多信息此处.

Use SHOW PROCESSLIST to check if event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler". Use SET GLOBAL event_scheduler = ON; to enable the scheduler if it's currently not enabled. More on configuring event scheduler here.

如果您想查看架构中的事件

If you want to see events that you've in your schema

SHOW EVENTS;

UPDATE 你的更新语句应该看起来像

UPDATE Your update statement should look like

UPDATE online_auctions 
   SET auction_status = 'ENDED' 
 WHERE auction_end_date < NOW();

这是SQLFiddle 演示

Here is SQLFiddle demo

这篇关于如何在时间戳字段过期后自动更新 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！