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.

Events

An Event is a SQL statement that can be coded to run at a predefined schedule.

Syntax

  CREATE EVENT eventname
    ON SCHEDULE schedule
      DO
        event body here

Example

  CREATE EVENT insertEveryday
    ON SCHEDULE EVERY 1 DAY
      DO
        INSERT INTO tableName (field1, field2) VALUES (value1, value2);

Cron job

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.

Syntax

Stored Procedures

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.

Syntax

  DELIMITER
  CREATE PROCEDURE procedure_name
  BEGIN
    // Query 1
    // Query 2
  END
  DELIMITER ;

Calling the procedure

CALL procedure_name;