How can we change MySQL AUTO_INCRMENT starting number?

MySQL AUTO_INCRMENT value starts from 1 but we can change it in following two ways-

With the help of ALTER TABLE query

We can use ALTER The TABLE query changes the starting value of AUTO_INCRMENT as follows - The Chinese translation of

ALTER TABLE table_name AUTO_INCREMENT = value;

Example

is:

Example

Suppose we have created a table having column 'id' as AUTO_INCREMENT. Now if we will insert the values ​​in it then the sequence number would start from 1 as you can see this in following queries −

mysql> Create Table EMP(id int NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10));
Query OK, 0 rows affected (0.07 sec)

mysql> Insert Into EMP(Name) Values('Aryan');
Query OK, 1 row affected (0.02 sec)

mysql> Insert Into EMP(Name) Values('Yash');
Query OK, 1 row affected (0.04 sec)

mysql> Select * from EMP;
+----+-------+
| id | NAME  |
+----+-------+
| 1  | Aryan |
| 2  | Yash  |
+----+-------+
2 rows in set (0.00 sec)

Now if we want to change the sequence number afterwards, we need Use ALTER TABLE query to change the value of AUTO_INCREMENT as shown below −

mysql> Alter table emp auto_increment = 10;
Query OK, 2 rows affected (0.25 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Insert Into EMP(Name) Values('Daksh');
Query OK, 1 row affected (0.03 sec)

mysql> Insert Into EMP(Name) Values('Shayra');
Query OK, 1 row affected (0.06 sec)

mysql> Select * from EMP;
+----+--------+
| id | NAME   |
+----+--------+
| 1  | Aryan  |
| 2  | Yash   |
| 10 | Daksh  |
| 11 | Shayra |
+----+--------+
4 rows in set (0.00 sec)

The above query has changed the value of AUTO_INCRMENT to 10, so after inserting the new value, we will get the sequence number starting from 10.

With the help of CREATE TABLE query< /h2>

We can also change the AUTO_INCRMENT value when creating the table. This can be accomplished by using the CREATE TABLE query to specify the value of AUTO_INCRMENT, as shown below - The Chinese translation of

CREATE TABLE (Column1 INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Column2 data type) AUTO_INCREMENT = value;

Example

is:

Example

mysql> Create Table EMP1(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10)) AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.11 sec)

The above query is executed when creating the table Specify the value of AUTO_INCREMENT as 100. Now if we insert the value into it, the sequence number will start from 100 instead of the default value 1 as shown below −

mysql> Insert into emp1(name) values('Sohan');
Query OK, 1 row affected (0.04 sec)

mysql> Insert into emp1(name) values('Harshit');
Query OK, 1 row affected (0.05 sec)

mysql> Select * from emp1;
+-----+---------+
| id  | NAME    |
+-----+---------+
| 100 |   Sohan |
| 101 | Harshit |
+-----+---------+
2 rows in set (0.00 sec)

The above is the detailed content of How can we change MySQL AUTO_INCRMENT starting number?. For more information, please follow other related articles on the PHP Chinese website!