CODEWITHSUNDEEP
mysqlstored-procedurescronmysql-event
iamsam TheAlchemist
iamsam TheAlchemist

Reputation: 49

How to create a stored procedure and run it on a scheduled time (in MYSQL)

Need your expertise, I have a script in MySQL and I want it to create a stored procedure and run it on a scheduled time (let's say I need to run it on 7 am and 1 pm).

sample script:

update systemuser 
set defaultSiteCode =(select siteCode from site where isremote = 0)
where type in ('U', 'A');

Upvotes: 1

Views: 1588

Answers (2)

iamsam TheAlchemist
iamsam TheAlchemist

Reputation: 49

Here is the event that I have create and it runs as expected:

--

CREATE DEFINER=root@localhost EVENT daily_userupdate ON SCHEDULE EVERY 5 MINUTE STARTS '2020-05-01 00:44:00' ON COMPLETION PRESERVE ENABLE DO update systemuser set defaultSiteCode =(select siteCode from site where isremote = 0) where type in ('U', 'A');

--

Upvotes: 0

Tural Ali
Tural Ali

Reputation: 23250

As you didn't go into technical details about the technology stack and OS you use I will answer your question theoretically.

You have multiple approaches. One of them is to set up a cron task:

  1. Create a procedure on MySQL server and save
  2. Using any serverside programming language create an executable file with a code that executes the function on MySQL DB. Based on OS you use it might be shell or batch script as well.
  3. Set this script as a cronjob

Take a look at the answers to this question: Run a mySQL query as a cron job?

Another way might be to create a MySQL event. Read about MySQL events here: https://t.ly/wmvqv

And here is the comparison of these two options:

Cronjob or MySQL event?

Upvotes: 1

Related Questions