Insert new row with id 1

Question

Is it possible to delete all the data in the table and insert new rows with id starting at 1

Delete command helps to remove data from the table when we try to insert a new row that will be the id of the last row added id + 1 (if the last inserted identifier is 5, then the new identifier should be 6), but I want to keep this identifier as 1

any suggestions other than truncate command, thanks in advance

Answer 1

After deleting all entries, do

ALTER TABLE tablename AUTO_INCREMENT = 1 

Note

From MySQL Docs : Link

You cannot reset the counter to a value less than or equal to any that has already been used. For MyISAM, if the value is less than or equal to the maximum value that is currently in the AUTO_INCREMENT column, reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, an error does not occur and the current value of the sequence does not change.

Answer 2

Use this TRUNCATE TABLE tableName reset auto increment value:

Answer 3

I come from your description that "id" is an identity column?

In this case

 TRUNCATE TABLE tablename; 

and delete all lines, and reset the identifier field, which will be filled with 1 again.

Answer 4

if you use the TRUNCATE command, it will delete all lines and reset the auto-increment value:

 TRUNCATE tablename; 

Answer 5

You will need to execute two commands in the following sequence :

Delete all data from the table first.

 delete TABLE_NAME 

Then set the ID to 1

 insert into TABLE_NAME(ID) values(1) 

Answer 6

How @mhasan answers

 ALTER TABLE `tablename` AUTO_INCREMENT = 1 

is a way to do it.

Another approach is to drop the identifier column and recreate it.

 ALTER TABLE `tablename` DROP `id`; ALTER TABLE `tablename` AUTO_INCREMENT = 1; ALTER TABLE `tablename` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; 

But if your table has relationships with other tables, your will is not a good solution.