[sql] Truncate with condition

truncate ->this resets the entire table, is there a way via truncate to reset particular records/check conditions.

For ex: i want to reset all the data and keep last 30 days inside the table.

Thanks.

You can simply export the table with a query clause using datapump and import it back with table_exists_action=replace clause. Its will drop and recreate your table and take very less time. Please read about it before implementing.

The short answer is no: MySQL does not allow you to add a WHERE clause to the TRUNCATE statement. Here's MySQL's documentation about the TRUNCATE statement.

But the good news is that you can (somewhat) work around this limitation.

Simple, safe, clean but slow solution using DELETE

First of all, if the table is small enough, simply use the DELETE statement (it had to be mentioned):

1. LOCK TABLE my_table WRITE;
2. DELETE FROM my_table WHERE my_date<DATE_SUB(NOW(), INTERVAL 1 MONTH);
3. UNLOCK TABLES;

The LOCK and UNLOCK statements are not compulsory, but they will speed things up and avoid potential deadlocks.

Unfortunately, this will be very slow if your table is large... and since you are considering using the TRUNCATE statement, I suppose it's because your table is large.

So here's one way to solve your problem using the TRUNCATE statement:

Simple, fast, but unsafe solution using TRUNCATE

1. CREATE TABLE my_table_backup AS
      SELECT * FROM my_table WHERE my_date>=DATE_SUB(NOW(), INTERVAL 1 MONTH);
2. TRUNCATE my_table;
3. LOCK TABLE my_table WRITE, my_table_backup WRITE;
4. INSERT INTO my_table SELECT * FROM my_table_backup;
5. UNLOCK TABLES;
6. DROP TABLE my_table_backup;

Unfortunately, this solution is a bit unsafe if other processes are inserting records in the table at the same time:

• any record inserted between steps 1 and 2 will be lost
• the TRUNCATE statement resets the AUTO-INCREMENT counter to zero. So any record inserted between steps 2 and 3 will have an ID that will be lower than older IDs and that might even conflict with IDs inserted at step 4 (note that the AUTO-INCREMENT counter will be back to it's proper value after step 4).

Unfortunately, it is not possible to lock the table and truncate it. But we can (somehow) work around that limitation using RENAME.

Half-simple, fast, safe but noisy solution using TRUNCATE

1. RENAME TABLE my_table TO my_table_work;
2. CREATE TABLE my_table_backup AS
     SELECT * FROM my_table_work WHERE my_date>DATE_SUB(NOW(), INTERVAL 1 MONTH);
3. TRUNCATE my_table_work;
4. LOCK TABLE my_table_work WRITE, my_table_backup WRITE;
5. INSERT INTO my_table_work SELECT * FROM my_table_backup;
6. UNLOCK TABLES;
7. RENAME TABLE my_table_work TO my_table;
8. DROP TABLE my_table_backup;

This should be completely safe and quite fast. The only problem is that other processes will see table my_table disappear for a few seconds. This might lead to errors being displayed in logs everywhere. So it's a safe solution, but it's "noisy".

Disclaimer: I am not a MySQL expert, so these solutions might actually be crappy. The only guarantee I can offer is that they work fine for me. If some expert can comment on these solutions, I would be grateful.

As a response to your question: "i want to reset all the data and keep last 30 days inside the table."

you can create an event. Check https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

For example:

CREATE EVENT DeleteExpiredLog
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM log WHERE date < DATE_SUB(NOW(), INTERVAL 30 DAY);

Will run a daily cleanup in your table, keeping the last 30 days data available