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.
TRUNCATE cannot be executed with a WHERE clause means that all records will be removed from the TRUNCATE / statement.
The truncate command removes all rows of a table. We cannot use a Where clause in this. It is a DDL command.
Truncate is a DDL command. Truncate table always lock the table and page but not each row.As it removes all the data. Cannot use Where condition. It Removes all the data.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
No, TRUNCATE
is all or nothing. You can do a DELETE FROM <table> WHERE <conditions>
but this loses the speed advantages of TRUNCATE
.
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.
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