Reset the primary key to 1 after deleting all the data in MySQL?

To reset the primary key to 1 after deleting the data, use the following syntax

alter table yourTableName AUTO_INCREMENT=1;
truncate table yourTableName;

After doing the above two steps, you will get the primary key beginning from 1.

To understand the above concept, let us create a table. The query to create a table is as follows

mysql> create table resettingPrimaryKeyDemo
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY
   -> );
Query OK, 0 rows affected (0.66 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.15 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.08 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from resettingPrimaryKeyDemo;

The following is the output

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
4 rows in set (0.00 sec)

Here is the query to reset the primary key to 1

mysql> alter table resettingPrimaryKeyDemo AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> truncate table resettingPrimaryKeyDemo;
Query OK, 0 rows affected (0.89 sec)

Check the records from the table. The query is as follows −

mysql> select *from resettingPrimaryKeyDemo;
Empty set (0.00 sec)

Insert some records from the table using insert command. The query is as follows −

mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.12 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)

Now check the table primary key beginning from 1. The query is as follows −

mysql> select *from resettingPrimaryKeyDemo;

The following is the output

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.00 sec)