Menu
I am attempting to clean out a table but not get rid of the actual structure of the table. I have an id column that is auto-incrementing; I don't need to keep the ID number, but I do need it to keep its auto-incrementing characteristic. I've found delete and truncate but I'm worried one of these will completely drop the entire table rendering future insert commands useless.
How do I remove all of the records from the table so that I can insert new data?
867
To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE . It's faster than DELETE . To remove an entire table, including its structure and data, use DROP TABLE .
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
The TRUNCATE command is faster than both the DROP and the DELETE command. Like the DROP command we also can't rollback the data after using the this command.
Delete, Truncate or Drop to clean out a table in MySQL. However, it's better to use truncate over delete because delete removes the data by each row, thus having a performance hit than truncate. However, truncate will not work on InnoDB tables where referential integrity is enforced unless it is turned off before the truncate command is issued.
TRUNCATE will reset your auto-increment seed (on InnoDB tables, at least), although you could note its value before truncating and re-set accordingly afterwards using alter table: Drop will do just that....drop the table in question, unless the table is a parent to another table.
TRUNCATE is faster than DELETE, as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE.
DROP deletes the entire table, including all data and the definitions of the table columns. So, after you have carried out a DROP command, the whole table will be gone and can not be recovered. In contrast, DELETE and TRUNCATE are only deleting the data from the table, while the table definitions are remaining.
drop table will remove the entire table with data
delete * from table will remove the data, leaving the autoincrement values alone. it also takes a while if there's a lot of data in the table.
truncate table will remove the data, reset the autoincrement values (but leave them as autoincrement columns, so it'll just start at 1 and go up from there again), and is very quick.
193
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
