Member Avatar for shashigowda

i want to clear all data in databse automatically for everymonth.iam using xampp.

  • 2 Contributors
  • 6 Replies
  • 323 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

was right to recommend a timestamp column, and has already completed steps 1–2. The remaining step is to run a delete query automatically. Two reliable options: use MySQL’s Event Scheduler (runs inside the server) or an OS scheduler that calls the mysql client (cron / Task Scheduler). Always test with a SELECT first and keep a recent backup.

Test and manual delete (always run the SELECT first):

SELECT COUNT(*) FROM my_table
 WHERE created_at < NOW() - INTERVAL 1 MONTH;

DELETE FROM my_table
 WHERE created_at < NOW() - INTERVAL 1 MONTH;

Quick Event Scheduler setup (temporary enable + event that runs daily):

SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS ev_purge_my_table
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM my_db.my_table
   WHERE created_at < NOW() - INTERVAL 1 MONTH;

If the table is large, delete in batches to avoid long locks (example stored procedure called by an event):

DELIMITER //
CREATE PROCEDURE purge_old_rows()
BEGIN
  REPEAT
    DELETE FROM my_db.my_table
     WHERE created_at < NOW() - INTERVAL 1 MONTH
     LIMIT 1000;
  UNTIL ROW_COUNT() = 0 END REPEAT;
END//
DELIMITER ;

Then schedule an event to CALL purge_old_rows().

Notes and cautions: make sure the created_at column is indexed for good performance; verify foreign-key behavior or cascading deletes; prefer testing on a copy before running on production; enable the scheduler permanently in XAMPP by adding under [mysqld] in my.ini: 

event_scheduler=ON

Creating events requires appropriate MySQL privileges and the server’s event scheduler to be enabled. For small setups, an OS-level cron/Task Scheduler job that runs the DELETE command is an easier alternative.

Recommended Answers

All 6 Replies

Member Avatar for pritaeas

Add a timestamp in every table and use that to determine whether or not it should be deleted.

Member Avatar for shashigowda

sorry....will you please write one example query

Member Avatar for pritaeas

It's not that simple.

  1. Add a timestamp column to the tables you want to do this for.
  2. Make sure that when you insert a new record this date gets filled.
  3. Create a query to run at regular intervals to remove all records with old dates.
Member Avatar for shashigowda

thnk you.

Member Avatar for shashigowda

thank you so much
i have finishd 1st & 2nd step.
but strugling in 3rd step (i,e Create a query to run at regular intervals to remove all records with old dates.).bcoz i dont no hoew to write a query tat automaticaly execute

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.