#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL Events and Event Scheduler: Automate SQL Tasks

An app that sends login OTPs ends up with a table full of codes that expired minutes after they were created. Somebody has to delete them, every few minutes, forever. You could write a cron job on a Linux server, but MySQL can do it by itself: an event is a named SQL task that the server's Event Scheduler runs at a set time or on a repeating interval. Think of it as cron that lives inside the database.

Step 1: check that the scheduler is on

SHOW VARIABLES LIKE 'event_scheduler';
Variable_nameValue
event_schedulerON

In MySQL 8.0 the default is ON. If you see OFF, run SET GLOBAL event_scheduler = ON; (needs an admin privilege) and add event_scheduler=ON under [mysqld] in my.cnf / my.ini so it survives a restart. If it shows DISABLED, the server was started with the scheduler disabled and it cannot be switched on at runtime. To create events you also need the EVENT privilege on the schema.

Step 2: a recurring event

CREATE TABLE otp_codes (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  phone      VARCHAR(15),
  code       CHAR(6),
  expires_at DATETIME NOT NULL
);
INSERT INTO otp_codes (phone, code, expires_at) VALUES
  ('9876500001', '482913', NOW() - INTERVAL 20 MINUTE),
  ('9876500002', '730155', NOW() - INTERVAL 2 MINUTE),
  ('9876500003', '118204', NOW() + INTERVAL 5 MINUTE);

CREATE EVENT ev_purge_expired_otp
  ON SCHEDULE EVERY 10 MINUTE
  COMMENT 'Delete OTP codes that have expired'
  DO
    DELETE FROM otp_codes WHERE expires_at < NOW();

After the event has fired once (we tested it with EVERY 2 SECOND so we did not have to wait), only the unexpired code remains:

SELECT id, phone, code FROM otp_codes;
idphonecode
39876500003118204

A recurring event without STARTS fires for the first time as soon as it is created, then once every interval.

Step 3: a multi-statement event at a fixed time

A common real job: every night at 01:00, write yesterday's order totals into a summary table so the morning dashboard does not scan the whole orders table. A body with several statements needs BEGIN ... END, and in the mysql client you change the delimiter so the inner semicolons do not end the CREATE EVENT early (Workbench handles this the same way in its SQL editor).

CREATE TABLE daily_sales_summary (
  summary_date DATE PRIMARY KEY,
  orders  INT,
  revenue DECIMAL(12,2)
);

DELIMITER $$
CREATE EVENT ev_daily_sales_summary
  ON SCHEDULE EVERY 1 DAY
  STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR)   -- tomorrow 01:00
  ON COMPLETION PRESERVE
  DO
  BEGIN
    INSERT INTO daily_sales_summary (summary_date, orders, revenue)
    SELECT * FROM (
      SELECT DATE(order_date) AS d, COUNT(*) AS n, SUM(amount) AS total
      FROM orders
      WHERE order_date >= CURRENT_DATE - INTERVAL 1 DAY
        AND order_date <  CURRENT_DATE
      GROUP BY DATE(order_date)
    ) AS s
    ON DUPLICATE KEY UPDATE orders = s.n, revenue = s.total;
  END$$
DELIMITER ;

Running the body by hand on 16 November 2026, with two orders (₹1,200 and ₹800) placed the previous day and one placed today, produced:

summary_dateordersrevenue
2026-11-1522000.00

The ON DUPLICATE KEY UPDATE makes the job safe to re-run: running it twice on the same night updates the row instead of failing on the primary key. (The derived table s is used instead of the older VALUES() function, which is deprecated from MySQL 8.0.20.)

One-time events

CREATE EVENT ev_end_festive_sale
  ON SCHEDULE AT '2026-11-20 23:59:59'
  DO UPDATE products SET discount_pct = 0 WHERE campaign = 'festive';

A one-time (AT) event is dropped automatically after it runs, because the default is ON COMPLETION NOT PRESERVE. Add ON COMPLETION PRESERVE if you want to keep its definition for reference. The same rule applies to a recurring event with an ENDS time.

Managing events

SHOW EVENTS;                                       -- events in the current schema
SELECT EVENT_NAME, EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STATUS, LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();

ALTER EVENT ev_purge_expired_otp DISABLE;          -- pause
ALTER EVENT ev_purge_expired_otp ENABLE;           -- resume
ALTER EVENT ev_purge_expired_otp ON SCHEDULE EVERY 5 MINUTE;
DROP EVENT IF EXISTS ev_purge_expired_otp;

The information_schema query on our test server (2-second test interval, LAST_EXECUTED column left out here):

EVENT_NAMEEVENT_TYPEINTERVAL_VALUEINTERVAL_FIELDSTATUS
ev_purge_expired_otpRECURRING2SECONDENABLED

LAST_EXECUTED is the quickest way to confirm that an event is actually running.

Common mistakes

Event or trigger?

A trigger fires because a row changed; an event fires because the clock says so. Use an event for clean-up, summaries and periodic maintenance; use a trigger to react to a specific INSERT, UPDATE or DELETE. For the date arithmetic used in schedules, see MySQL date and time functions.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →