如何安排 MySQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9621355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to schedule a MySQL query?
提问by mmdel
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:
我有一个库存 MySQL 数据库。结构如下：
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.
报告的基础是当我想要过去日期的库存估价详细信息时。
The avgcostand quantityfields 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.
当新采购过帐到系统时，avgcost和quantity字段会发生变化。我可以运行查询以查看当前的股票估值，但我也希望能够查看前一天的股票估值。我怎么做？对于数量，我可以添加销售额并从当前日期向后扣除采购，直到报告要求的任何日期，但 avgcost 是最新的，因为每次发布采购时都会更新。
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
Is this task possible to schedule directly in MySQL or is there some other solution?
是否可以直接在 MySQL 中安排此任务，或者是否有其他解决方案？
回答by rkosegi
you have 2 basic options (at least):
您有 2 个基本选项（至少）：
1, Take a look at Event Scheduler
1、看一下Event Scheduler
First create table eg. stock_dumps with fields
首先创建表，例如。带字段的 stock_dumps
itemcode, quantity, avgcost, ttlval,dump_date (DATETIME)
商品代码、数量、平均成本、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
Please follow instructions how to enable scheduler on link posted above. Note : Old versions of mysql don't have event scheduler
请按照说明如何在上面发布的链接上启用调度程序。注意：旧版本的 mysql 没有事件调度程序
2, Create cron job/windows scheduled job:
2、创建cron job/windows定时作业：
create sql file:
创建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;
schedule this command:
调度此命令：
mysql -uusername -ppassword < /path/to/sql_file.sql
回答by user2273519
There are generic tools available such as EyeShareor AutoSQL.netto do this. EyeShare is a more generic IT process automation tool and AutoSQL is a single purpose tool specifically to schedule queries and output them (to Excel or CSV).
有可用的通用工具（例如EyeShare或AutoSQL.net）来执行此操作。EyeShare 是一种更通用的 IT 流程自动化工具，而 AutoSQL 是一种单一用途的工具，专门用于安排查询并将其输出（到 Excel 或 CSV）。
