How to delete a MySQL record after a certain time
You can try using this condition:
WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)
So that the whole SQL script looks like this:
CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO BEGIN
DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;
However, on your place I would solve the given problem with a simple cron script. The reasons to do this is simple: it's easier to maintain the code, no ugly SQL workarounds, integrates smoothly with your system.
AUTO-DELETE ROW 1 Day After Inserted (MySQL)
Here's your scripts.
Insert into table1 (id, name, timestamp) values (1, 'test', now())
after insert
Delete from table1 WHERE timestamp < now() - interval 1 day;
Delete a row from a database table after a certain time period
You could use an event eg:
CREATE EVENT deleteEachMonth
ON SCHEDULE EVERY 1 DAY STARTS '2017-05-01 00:00:00'
-- !!! no *END*: will continue until you explicitly drop the event
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(curdate(), INTERVAL 1 MONTH);
in your case
CREATE EVENT deleteEachMonth
ON SCHEDULE EVERY 1 DAY STARTS '2017-05-01 00:00:00'
DO
DELETE FROM client WHERE registrationDate < DATE_SUB(curdate(), INTERVAL 1 MONTH)
Deleting records before a certain date
DELETE FROM table WHERE date < '2011-09-21 08:21:22';
Is it possible to delete records from MySQL based upon the date they were inserted
No, that's not possible.
You need to include a created date in your table definition (and maintain its values) in order to delete rows depending on their creation.
Amendment to address to OP's edit about deleting by id:
If you have an incrementing id you can easily delete n rows in descending order. Just ORDER and LIMIT your DELETE query.
E.g., to delete the 100 rows with highest id:
DELETE FROM table ORDER BY id DESC LIMIT 100
Delete records in table after specific period of time in MySQL
multiple ways
- you can opt for
setTimeout
in js, the problem is when service goes down the last set of records won't be deleted - you can try scheduling inside mysql itself as well https://dev.mysql.com/doc/refman/8.0/en/events-syntax.html
MYSQL: Delete records from a certain date
Seems you do not have correct date format. The correct mysql format is yyyy-MM-dd
DELETE FROM client_update_history WHERE DATE(date_history) < '2015-11-01'
Do note that if you are using date_history
as an index you are better of doing
DELETE FROM client_update_history WHERE date_history < '2015-11-01'
so that this index can be used.
Related Topics
Regex to Filter for Numers With and Without Dots
Subtract Values from Two Columns in SQL Query
Sql Get Parent Where Children Have Specific Values
How to Directly View Blobs in MySQL Workbench
Mysql - How to Use Like on Multiple Columns
Phpmyadmin Says No Privilege to Create Database, Despite Logged in as Root User
How to Insert an Image in Sqlite Database(Table)
How Select Max(Salary) of Employee Each Department With Employee_Id and Emp_Name
SQL Server Dynamic PIVOT Query
Iif Statement With Multiple Conditions
How to Make a Join of 3 Tables in Jpa
How to Modify Query to Remove Double-Quotes from All Columns
Sql Server Query to Find All Permissions/Access for All Users in a Database
Duplicate Rows When Joining Tables
Find Out Where MySQL Is Installed on MAC Os X