CODEWITHSUNDEEP
mysql
Eugene Stamp
Eugene Stamp

Reputation: 158

Resetting data in MySql database

I am faced with a scenario where I would like to remove all existing data from one of my tables and then reuse the table again and fill it with new data.

My Question: Should I destroy/recreate the exact same table to do this? Or is there an easier way? -Side Note: I am storing user id's and would like for the id's to be set back again as opposed to continuing at the number in which last data was stored.

I'm using PhpMyAdmin.

Upvotes: 1

Views: 566

Answers (2)

Shalev Shalit
Shalev Shalit

Reputation: 1963

If you use TRUNCATE TABLE.

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances. ... it resets any AUTO_INCREMENT counter to zero

So if you want to keep the counter use DELETE FROM

If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM.

You can also look here: How to prevent mySQL from resetting auto increment value?

Upvotes: 1

Mark
Mark

Reputation: 5767

You have to truncate your table, see documentation here: https://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

In phpmyadmin there's a button in the table list called: Empty

enter image description here

Upvotes: 1

Related Questions