每天执行一次查询 [英] Perform query once a day

问题描述

基本上，我有一个 MySQL 数据库，该数据库带有一个表，用于存储来自我的网站用户的请求.我想自动执行一个查询，该查询会基于列删除所有早于180天的已完成请求(已完成表示状态 Status = 3 ).

Basically I have a MySQL database with a table that stores requests from the users of my website. I would like to automatically perform a query that removes all completed requests (completed means that the column Status = 3) older than 180 days basing on the column ArchivingDate.

我的请求表:

My Requests table:

Requests(
    RequestID INT AUTO_INCREMENT PRIMARY KEY,
    Ticket VARCHAR(40) NOT NULL UNIQUE,
    RequestDate DATETIME NOT NULL,
    ReplacementOrRefund INT NOT NULL,
    ItemName VARCHAR(200) NOT NULL,
    Total NUMERIC(15, 2) NOT NULL,
    AccountName VARCHAR(200) NOT NULL,
    Email VARCHAR(254) NOT NULL,
    BillingAddress VARCHAR(200) NOT NULL,
    OrderNumber VARCHAR(50) NOT NULL,
    OrderDate DATETIME NOT NULL,
    DeliveryDate DATETIME NOT NULL,
    WhoSigned VARCHAR(200),
    WhereLeft VARCHAR(200),
    Status INT NOT NULL,
    ArchivingDate DATETIME,
    PayPalTransactionID VARCHAR(100),
    Toggle TINYINT(1) NOT NULL,
    StoreID_FK INT,
    FOREIGN KEY (StoreID_FK) REFERENCES Stores(StoreID)
);

我已经编写了查询以删除超过180天的行:

I have already written the query to remove rows that are older than 180 days:

$ remove =更新请求SET Toggle = 0，状态= 3 AND ArchivingDate< DATE_SUB(NOW()，间隔180天)";

注意::我不从表中删除数据，我只是通过将列 Toggle 设置为来隐藏"它0 .

NOTE: I do not remove data from the table, I simply "hide" it by setting the column Toggle to 0.

问题::如何自动使MySQL每天执行一次此查询(如果可能的话)?

Question: How do I automatically make MySQL perform this query once a day (if it's possible)?

非常感谢您！

推荐答案

是的，请参阅此文章:

yes, it's possible, see this article: http://www.sitepoint.com/how-to-create-mysql-events/

这篇关于每天执行一次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！