Author: Francis Ndungu
Last Updated: Thu, Jul 29, 2021MySQL scheduled events are time-based SQL statements executed by the scheduler thread at a specific time, usually repetitively. These tasks are like cron jobs in Linux.
In a real-life scenario, you would use a MySQL scheduler to automate business logic. For instance, when designing a bank application, you can use a scheduler to post daily savings interests into a database table or schedule a job to apply a roll-over fee to loan defaulters.
This guide explains creating a sample database and implementing a per-minute billing application using a MySQL scheduled event.
To complete this tutorial, make sure you have:
Log in to MySQL as root.
$ sudo mysql - u root -p
MySQL uses a thread to run scheduled events in the background. This behavior is controlled by a global variable named event_scheduler
To check if the feature is enabled, run the command below.
mysql> SELECT @@event_scheduler;
The feature is ON or OFF.
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)
If the feature is turned OFF, enable it by executing the command below.
mysql> SET GLOBAL event_scheduler = 1;
After the scheduler is running, create a sample_db database.
mysql> CREATE DATABASE sample_db;
Select your new sample_db database.
mysql> USE sample_db;
Define a products table.
This table holds information about the different plans offered in your hypothetical company. Each plan has a unique product_id and a distinguishable product_name going for a specific price which is stored in a billing_per_minute column. Use the DECIMAL data type for this column to take advantage of its precision since you're dealing with very low floating values which need accuracy.
Create the products table by running the following command.
mysql> CREATE TABLE products (
product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
billing_per_minute DECIMAL(17, 6)
) ENGINE = InnoDB;
Insert some items into the products table.
For demonstration purposes, enter two products. This guide assumes you're offering tutorials-as-a-service and you're billing your customers on a per-minute basis. Customers can subscribe to your PYTHON TUTORIAL at a rate of $0.003472 per minute($5 per day) or the C# TUTORIAL that goes for $0.006944 per minute($10 per day).
mysql> INSERT INTO products (product_name, billing_per_minute) VALUES('PYTHON TUTORIAL', '0.003472');
INSERT INTO products (product_name, billing_per_minute) VALUES('C# TUTORIAL', '0.006944');
Create a customers table.
This table holds information about your clients. Each customer has a unique primary key defined by the customer_id column. Also, capture the customers' full names using the first_name and last_name fields.
mysql> CREATE TABLE customers (
customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
) ENGINE = InnoDB;
Add a few clients to the customers table.
mysql> INSERT INTO customers (first_name, last_name) VALUES('JOHN', 'DOE');
INSERT INTO customers (first_name, last_name) VALUES('SMITH', 'JANE');
INSERT INTO customers (first_name, last_name) VALUES('MARY', 'MIKE');
Define a subscriptions table.
This table provides a many-to-many relationship to the customers and the products tables, because a single customer can subscribe to many different products, and a single product can have multiple subscribers.
You'll identify each unique subscription with a subscription_id which is the primary key in this case. Next, you'll insert customer_id's and the subscribed product_id's depending on what each client has subscribed to. Finally, because customers might cancel their subscriptions at any time, include an is_active field which you can either activate or deactivate at any time using the flags Y and N.
mysql> CREATE TABLE subscriptions (
subscription_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
is_active CHAR(1)
) ENGINE = InnoDB;
Enter some data into the subscriptions table.
mysql> INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(1, 1, 'Y');
INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(2, 2, 'Y');
INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(3, 1, 'Y');
Confirm the customers' subscriptions by executing the SQL statement below, which joins three tables to retrieve the customer names and associated product names to which they subscribe.
mysql> SELECT
customers.customer_id,
customers.first_name,
customers.last_name,
products.product_name,
products.billing_per_minute
FROM subscriptions
LEFT JOIN customers
ON subscriptions.customer_id = customers.customer_id
LEFT JOIN products
ON subscriptions.product_id = products.product_id
ORDER BY customers.customer_id;
As you can confirm from the output below, JOHN DOE and MARY MIKE have subscribed to the PYTHON TUTORIAL while SMITH JANE has opted for the C# tutorial. In all cases, you're using the power of the MySQL JOIN statement to retrieve the cost of each subscription per minute.
+-------------+------------+-----------+-----------------+--------------------+
| customer_id | first_name | last_name | product_name | billing_per_minute |
+-------------+------------+-----------+-----------------+--------------------+
| 1 | JOHN | DOE | PYTHON TUTORIAL | 0.003472 |
| 2 | SMITH | JANE | C# TUTORIAL | 0.006944 |
| 3 | MARY | MIKE | PYTHON TUTORIAL | 0.003472 |
+-------------+------------+-----------+-----------------+--------------------+
3 rows in set (0.00 sec)
Define a billings table.
You'll use the power of MySQL scheduled events to log billing minutes for each client depending on their subscription in this table.
mysql> CREATE TABLE billings (
billing_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
subscription_id BIGINT NOT NULL,
time DATETIME,
amount DECIMAL(17, 6)
) ENGINE = InnoDB;
Your database and all the required tables are now in place. Next, you'll schedule an event to execute your billing logic after each minute.
MySQL comes with an elegant syntax for creating scheduled events, as shown below.
mysql> CREATE EVENT IF NOT EXISTS SAMPLE_EVENT_NAME
ON SCHEDULE
SAMPLE_SCHEDULE
DO
SAMPLE_EVENT_BODY
;
Before you create a scheduled event for your billing application, familiarize yourself with the syntax as explained below.
SAMPLEEVENTNAME:. This is a name for your event, and it must be unique in your database. For instance, billing_event. See the example below.
CREATE EVENT IF NOT EXISTS billing_event
...
SAMPLE_SCHEDULE:. This is the exact timing when the event will fire. Use the AT keyword to queue a one-time event, as shown below.
ON SCHEDULE
AT SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
For instance, to execute an event immediately at the current timestamp based on your MySQL server's timezone, use the syntax below.
...
ON SCHEDULE
AT CURRENT_TIMESTAMP
...
To create a recurring event like in this guide's per-minute billing software, use the EVERY keyword.
...
ON SCHEDULE
EVERY SAMPLE_INTERVAL
...
For instance, to run an event after every 1 minute, use the syntax below.
...
ON SCHEDULE
EVERY 1 MINUTE
...
Also, you can specify when a recurring event begins and stops by using the STARTS and ENDS keywords as shown below.
...
STARTS
SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
ENDS
SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
...
For instance, to start a recurring event one hour after the current time and end it after one year, use the syntax below.
...
STARTS
CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ENDS
CURRENT_TIMESTAMP + INTERVAL 1 YEAR
...
Here is a list of all the intervals that you can use to time recurring events.
YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
Some intervals combine two timing periods. For instance, MINUTE_SECOND defines both minutes and seconds. In such a case, a '10 minutes and 4 seconds` interval can be expressed as follows.
+ INTERVAL '10:4' MINUTE_SECOND
Now that you're familiar with the syntax of creating scheduled events in MySQL, create an event for your billing software in the next step.
For this demonstration, you'll create a billing software scheduled event that logs per-minute subscription activities into the billings database table.
Create the billing_event by running the command below.
mysql> CREATE EVENT IF NOT EXISTS billing_event
ON SCHEDULE
EVERY 1 MINUTE
DO
INSERT INTO billings(
subscription_id,
time,
amount
)
SELECT
subscriptions.subscription_id,
NOW(),
products.billing_per_minute
FROM subscriptions
LEFT JOIN products
ON subscriptions.product_id = products.product_id
WHERE subscriptions.is_active = 'Y';
In the above code, you've instructed MySQL to run the event after every 1 minute using the keyword ON SCHEDULE EVERY 1 MINUTE. Next, you're using the MySQL INSERT INTO SELECT command to copy subscription_id's and the associated billing_per_minute amount from the subscriptions and products tables, respectively. You're also using the NOW() function to record the current timestamp for each billing record. The statement `WHERE subscriptions.is_active = 'Y" ensures that you're only billing active subscriptions.
Ensure your event was successfully created in the database by executing the command below.
mysql> SHOW EVENTS FROM sample_db;
Your billing_event should be listed in the list as shown below.
+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| sample_db | billing_event | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2021-07-08 12:41:01 | NULL | ENABLED | 1 | cp850 | cp850_general_ci | utf8mb4_0900_ai_ci |
+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Your event is now scheduled to run after every single minute. In the next step, you'll confirm whether the logic is working as expected.
Wait for a few minutes for the scheduler to insert a few records into the billings table and then run the following SELECT statement against the table.
mysql> SELECT
billing_id,
subscription_id
time,
amount
FROM billings;
As time progresses, the list from the billings table grows as follows.
+------------+-----------------+---------------------+----------+
| billing_id | subscription_id | time | amount |
+------------+-----------------+---------------------+----------+
| 1 | 1 | 2021-07-08 12:41:01 | 0.003472 |
| 2 | 3 | 2021-07-08 12:41:01 | 0.003472 |
| 3 | 2 | 2021-07-08 12:41:01 | 0.006944 |
| 4 | 1 | 2021-07-08 12:42:01 | 0.003472 |
| 5 | 3 | 2021-07-08 12:42:01 | 0.003472 |
| 6 | 2 | 2021-07-08 12:42:01 | 0.006944 |
| 7 | 1 | 2021-07-08 12:43:01 | 0.003472 |
| 8 | 3 | 2021-07-08 12:43:01 | 0.003472 |
| 9 | 2 | 2021-07-08 12:43:01 | 0.006944 |
...
+------------+-----------------+---------------------+----------+
n rows in set (0.00 sec)
This confirms that the MySQL event scheduler is working as expected. Retrieve the total billed amount for each customer's subscription by executing the command below.
mysql> SELECT
customers.customer_id,
CONCAT(customers.first_name, ' ', customers.last_name) as customer_full_name,
sum(amount) as total_due
FROM billings
LEFT JOIN subscriptions
ON billings.subscription_id = subscriptions.subscription_id
LEFT JOIN customers
ON customers.customer_id = subscriptions.customer_id
GROUP BY billings.subscription_id;
You should now see a detailed report as shown below. Your figures should differ depending on how long your scheduled event has run.
+-------------+--------------------+-----------+
| customer_id | customer_full_name | total_due |
+-------------+--------------------+-----------+
| 1 | JOHN DOE | 0.010416 |
| 2 | SMITH JANE | 0.020832 |
| 3 | MARY MIKE | 0.010416 |
+-------------+--------------------+-----------+
3 rows in set (0.00 sec)
Next, you'll learn how to drop a scheduled event.
You can drop a scheduled event in MySQL by executing the command below. Replace SAMPLE_EVENT_NAME with the name of your event.
mysql> DROP EVENT SAMPLE_EVENT_NAME;
For instance, to drop the event billing_event, run the command below.
mysql> DROP EVENT billing_event;
Once you delete an event, it will stop executing immediately.
In this guide, you've set up a sample database and learned the basic syntax for creating and scheduling events in MySQL. You've also experimented with the working process of scheduled jobs using a hypothetical billing application. You can use the knowledge in this guide to create any database application that requires recurring jobs to process some business logic.