0

I have an application with a MySQL Database. The application is currently under testing. After testing I want to delete all testing data in the database(but keep all tables as it is). I want to refresh all tables in the DB. If i just delete values from the DB, the AUTO_INCREMENT columns do not start from 1.

How do I refresh the DB so all values are gone?

charak
  • 187
  • 3
  • 15
  • 1
    why not just drop and recreate the database ? – Sudipta Mondal Apr 26 '19 at 07:55
  • _the AUTO_INCREMENT columns do not start from 1..._ => `ALTER TABLE tablename AUTO_INCREMENT = 1` – B001ᛦ Apr 26 '19 at 07:55
  • What about Standard docs ? it says, if you truncate the table, the auto increment starts from 1 [Link to Standard docs](https://oracle-base.com/articles/mysql/mysql-how-truncate-table-affects-auto-increment) – Sudipta Mondal Apr 26 '19 at 07:57
  • I agree with @SudiptaMondal: just drop and recreate the database. Much faster. Also _"If i just delete values from the DB, the AUTO_INCREMENT columns do not start from 1"_ is a big red flag. Why would you care at which value your auto increment starts? – RobIII Apr 26 '19 at 08:06

1 Answers1

4

You just need to truncate all your tables. after truncating all your Auto Increment starts from 1 instead of where you left. here is the link for more info

TRUNCATE TABLE table_name;
danish-khan-I
  • 776
  • 6
  • 15
  • 2
    Truncate won't always work (especially when there's other tables relying on the truncated table via FK's). – RobIII Apr 26 '19 at 08:05
  • Agree with you,you cannot truncate a table which has an FK constraint on it. [here](https://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint) – danish-khan-I Apr 26 '19 at 13:14