How to Change Auto Increment Value of MySQL / MariaDB Database

Almost all database tables have a unique column which serves as identifier and has a value that auto_increment by database engine for each new record. The auto increment value is stored in the tables’ schema of the database and is normally not modifiable. The next auto increment sequence numbers is calculated automatically by database and inserted into the table together with the records.

This tutorial shows you how to modify the auto_increment value to a number which you desired in MySQL or MariaDB. For example, to start the auto increment value from a bigger number of 100 instead 1 (default for new table).

Method 1: Alter Table

Run the following query:

ALTER TABLE tbl_name AUTO_INCREMENT = x;

Replace tbl_name with actual table name and x with a number you prefer, such as 7.

Method 2: Insert Auto Increment Value

Run the following query:

BEGIN WORK;
INSERT INTO tbl_name (ID) VALUES (x);
ROLLBACK;

Replace tbl_name with actual table name, ID with actual column name which has auto_increment attribute, and x with a number you prefer, such as 7.

The queries above tricks the MySQL or MariaDB database to use a forced auto_increment value, which it will remember as the current and latest auto_increment sequence to use for next transaction. Begin and rolling back make sure the transaction is not inserted into the database at all. Note that you will also need to add other mandatory columns and values to the “INSERT INTO” query above for it to work.