Menu
I am looking for a way to automatically clean table in MySQL once per day. Is this possible without using cron? The best solution would be a trigger, but any solution is applicable.
904
TRUNCATE always removes all the rows from a table, leaving the table empty and the table structure intact whereas DELETE may remove conditionally if the where clause is used. The rows deleted by TRUNCATE TABLE statement cannot be restored and you can not specify the where clause in the TRUNCATE statement.
TRUNCATE TABLE always locks the table and page. However, it doesn't lock each row. The WHERE condition can not be used. The command removes all the data.
Now what is Auto-Truncate Mode: This means your DB is still working as if it is in SIMPLE Recovery Model. or in other words, the log file will be truncated every time a CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals.
You cannot ROLLBACK TRUNCATE Simply, you cannot rollback a transaction if it is already committed but you can do something else to get the data back (or at least some parts of it). When you execute the TRUNCATE statement, your data is still in the MDF file.
One option is MySQL's EVENT scheduler:
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
STARTS '2014-02-23 05:00:00' -- Time to start
COMMENT 'Descriptive comment'
DO
TRUNCATE yourtable;
How to enable event scheduler
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
