Truncate with condition - 조건 주고 TRUNCATE 하기

Truncate with condition

up vote3down votefavorite 2	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. sql mysql truncate share|improve this question edited Oct 3 '10 at 17:55 Michael Petrotta 31.3k861115 asked Sep 13 '10 at 22:16 Sharpeye500 1,49062054

2 Answers

activeoldestvotes

up vote6down voteaccepted	No, TRUNCATE is all or nothing. You can do a DELETE FROM <table> WHERE <conditions> but this loses the speed advantages of TRUNCATE. share|improve this answer answered Sep 13 '10 at 22:19 ceejayoz 75.1k16109166
	Thanks, assuming that condition, how do i keep last 10 days data and delete the rest (for say the table has 10 million records), in an optimized manner. – Sharpeye500 Sep 13 '10 at 22:20 The speed from TRUNCATE is because it doesn't write to the logs – OMG Ponies Sep 13 '10 at 22:21

up vote4down vote

The short answer is no: MySQL does not allow you to add a WHERE clause to the TRUNCATEstatement. 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 theTRUNCATE 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 thatlimitation 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 tablemy_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. share|improve this answer answered Dec 7 '11 at 14:20 MiniQuark 7,36234473

출처 : http://stackoverflow.com/questions/3704834/truncate-with-condition