如何安排 MySQL 查询?

2021-11-20 00:00:00 scheduled-tasks mysql

我可以在 MySQL 中安排任务以指定的时间间隔运行吗?

Can I schedule a task to run at a specified interval in MySQL?

我有一个库存 MySQL 数据库.结构如下:

I have an inventory MySQL database. The structure is as follows:

table_1            fields: itmcode, avgcost
table_2            fields: itmcode(FK to table_1(itmcode)), quantity

报告的基础是我希望了解过去日期的库存估价详细信息.

The basis of the report is when I want the inventory valuation details item wise for a past date.

avgcost 和 quantity 字段在新购买发布到系统时发生更改.我可以运行查询以查看当前的股票估值，但我也希望能够查看前一天的股票估值.我怎么做?对于数量，我可以添加销售额并从当前日期向后扣除采购，直到报告要求的任何日期，但平均成本是最新的，因为每次发布采购时都会更新.

The avgcost and quantity fields is changed when a new purchase is posted into system. I can run a query to see the current stock valuation, but I want to be able to see the stock valuation at a previous date as well. How do I do that? For quantity, I can add the sales and deduct the purchases backwards from the current date until whatever date the report requires, but the avgcost is current since this gets updated each time a purchase is posted.

我想知道是否可以执行类似于此的每日自动转储:

I was wondering if an automatic daily dump could be executed, similar to this:

SELECT itmcode, quantity, avgcost, (avgcost * quantity) as ttlval
FROM table_1 
JOIN table_2 ON table_1.itmcode = table_2.itmcode

是否可以直接在 MySQL 中安排此任务，或者是否有其他解决方案?

Is this task possible to schedule directly in MySQL or is there some other solution?

推荐答案

您有 2 个基本选项(至少):

you have 2 basic options (at least):

1、看看Event Scheduler

首先创建表，例如.带字段的 stock_dumps

First create table eg. stock_dumps with fields

商品代码、数量、平均成本、ttlval、dump_date (DATETIME)

itemcode, quantity, avgcost, ttlval,dump_date (DATETIME)

CREATE EVENT `Dumping_event` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
  FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;
END

请按照说明如何在上面发布的链接上启用调度程序.注意:旧版本的mysql没有事件调度器

Please follow instructions how to enable scheduler on link posted above. Note : Old versions of mysql don't have event scheduler

2、创建 cron 作业/windows 定时作业:

2, Create cron job/windows scheduled job:

创建sql文件:

INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;

安排这个命令:

mysql -uusername -ppassword < /path/to/sql_file.sql

相关文章