如何在时间戳字段过期后自动更新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());

推荐答案

使用可以用于此目的

1. Mysql事件(恕我直言是最佳人选)
2. cron作业或Windows Task Scheduler(如果您在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 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屋！