Resetting data in MySql database

Question

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.

not clear what you want. please post sample input (/before) data and desired output (/after) data — amdixon, Jul 18 '15 at 11:50
As strawberry said. It will reset autoincs back to 1 for you too — Drew, Jul 18 '15 at 11:52

score 1 · Answer 1 · answered Jul 18 '15 at 11:51

1

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

answered Jul 18 '15 at 11:51

Mark

5,437
2
19
29

score 1 · Accepted Answer · edited May 23 '17 at 10:26

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?

Resetting data in MySql database

2 Answers2