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:
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.