Resetting the primary key to 1 after deleting all the data

So i have MySql and i have a table user with a user_id column and it is the primary key and auto incremented. Now when i delete all my d开发者_如何学运维ata from the table and add the new one, the user_id does not start from 1 but from the number it had before deletion. What if i want to reset it without dropping the whole table and creating it again.

ALTER TABLE some_table AUTO_INCREMENT=1

So some_table would be the table you want to alter.

You could also use:

TRUNCATE TABLE some_table

This will reset the Auto Increment on the table as well as deleting all records from that table.

The code below is best if you have some data in the database already but want to reset the user_id to 1 without deleting the data. Copy and run in SQL command

ALTER TABLE members DROP user_id;
ALTER TABLE members AUTO_INCREMENT = 1;
ALTER TABLE members ADD user_id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

you can use DBCC check identity to reset your Primary key. here is the Sytax:

DBCC CHECKIDENT(TableName,RESEED,0)