How to schedule a MySQL query?

Question

Can I schedule a task to run at a specified interval in 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.

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 

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

Answer 1

you have 2 basic options (at least):

1, Take a look at Event Scheduler

First create table eg. stock_dumps with fields

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 

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

2, Create cron job/windows scheduled job:

create sql file:

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