How to Change Auto Increment Value of MySQL / MariaDB Database

»»»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.

Note
Running the “ALTER TABLE” query may cause the rebuilding of entire table for some MySQL versions, so use with care for large database.

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.

By | 2017-08-06T08:53:01+00:00 August 6th, 2017|Categories: Databases|Tags: , |0 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.

Connect with LK through Tech Journey on Facebook, Twitter or Google+.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!