How to reset auto increment column of a table in MySQL

Question

I have a table and it's first column sl is auto incrementing. After populating my table, I removed first two rows, and the first entry is having sl 1. Is it possible to reset it to 1 maintaining AI? I am using PHP MyAdmin.

Answer 1

ALTER TABLE tablename AUTO_INCREMENT = 1;

Answer 2

I'm not sure if i got your question but if you want your column sl to be renumbered do ALTER TABLE your_table DROP sl and then ALTER TABLE your_table ADD sl your_definitions

Answer 3

If you just want to reset the auto-number back to 1 you can use either:

DBCC CHECKIDENT (tablename, RESEED, 0)

or

TRUNCATE TABLE tablename

The first option will simply reset your auto-number counter while the second will clear your table of all data and reset the counter.

Is this what you were asking?

Answer 4

Try using ALTER TABLE tableName AUTO_INCREMENT=0;. The next record will be entered with index 1.