SQL Events, procedures and Cron job.
I am working as a full stack developer in CodewizardHQ platform. It is my first project in vue and php. Since MYSQL was not new to me, but I am still learning a lot about it. Few days ago, I got introduced with SQL stored procedures and events. While I was studying about the SQL events, I started comparing it with cron job and it gave me an idea to write some basics about it.
The task in the project was to insert some data in a table every day based on students' performance in their class. A cron job to schedule the execution of query once a day can get the work done. But alternative to this, we tried using SQL events. Let's get a basic idea of what a SQL event is and its syntax.
An Event is a SQL statement that can be coded to run at a predefined schedule.
CREATE EVENT eventname ON SCHEDULE schedule DO event body here
CREATE EVENT insertEveryday ON SCHEDULE EVERY 1 DAY DO INSERT INTO tableName (field1, field2) VALUES (value1, value2);
Cron job on the other hand can be used for the same purpose, i.e., to run a task at a predefined schedule, but it is not a database statement. Cron job is a unix utility and can be scripted using any server side scripting language such as php. The syntax for the cron job includes five parts each of which represent a chronological operator followed by the full path and command to execute.
Another interesting feature of MySQL is Stored Procedure. A stored procedure is a set of queries which can be executed by simply calling the procedure.
DELIMITER CREATE PROCEDURE procedure_name BEGIN // Query 1 // Query 2 END DELIMITER ;
Calling the procedure