How to change the auto-increment number in MySQL?

auto_increment is a default attribute that automatically increments newly added records.

By 1. Use the alter command to change the starting number.

First, use the insert command to create a table. The specific operations are as follows −

mysql> CREATE table AutoIncrementTable
-> (
-> id int auto_increment,
-> name varchar(200),
-> Primary key(id)
-> );
Query OK, 0 rows affected (0.70 sec)

After creating the table, you can insert records into the table through the insert command

The content given is as follows −

mysql> INSERT into AutoIncrementTable(name) values('Carol');
Query OK, 1 row affected (0.19 sec)

mysql> INSERT into AutoIncrementTable(name) values('Bob');
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into AutoIncrementTable(name) values('John');
Query OK, 1 row affected (0.18 sec)

Now, you can see the records in the table through the select command. This is given as As follows -

mysql> SELECT * from AutoIncrementTable;

The output obtained from the above query is as follows -

+----+-------+
| id | name  |
+----+-------+
| 1  | Carol |
| 2  | Bob   |
| 3  | John  |
+----+-------+
3 rows in set (0.00 sec)

Now 3 records have been inserted into the table, and the id is incremented by 1 each time. Now the auto-increment has been changed so that the id of the next record starts from 1000.

Change the syntax of auto_increment as follows.

alter table yourTableName auto_increment=startingNumber;

The above syntax is used to increase auto_increment by 1000. As shown below −

mysql> alter table AutoIncrementTable auto_increment = 1000;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

After successfully modifying auto_increment, more records are inserted into the table. This is The display is as follows −

mysql> INSERT into AutoIncrementTable(name) values('Taylor');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into AutoIncrementTable(name) values('Sam');
Query OK, 1 row affected (0.17 sec)

Now, use the select statement to view the table records. You can see the 4th record

Quantities start at 1000.

mysql> SELECT * from AutoIncrementTable;

The following is the output

+------+--------+
| id   | name   |
+------+--------+
| 1    | Carol  |
| 2    | Bob    |
| 3    | John   |
| 1000 | Taylor |
| 1001 | Sam    |
+------+--------+
5 rows in set (0.00 sec)

The above is the detailed content of How to change the auto-increment number in MySQL?. For more information, please follow other related articles on the PHP Chinese website!