How to Set Initial Value & Auto Increment in MySQL

MySQL allows you to set primary key that has an initial value of 1 and auto increments by 1 every time you insert a new record to your table. For example, if you have table names(id, first_name, last_name) then ID column’s first value will be 1 and it will increment every time by 1. But sometimes you may need to set initial value and auto increment in MySQL to something else. In this article, we will learn how to do this.

How to Set Initial Value & Auto Increment in MySQL

By default, when you set a primary key as auto increment then its default value is set to be 1 and it is automatically incremented by 1 every time a new row is inserted.

But if you want the auto increment to start from 501 instead of 1 then you can do so using ALTER TABLE statement as shown below.

ALTER TABLE users AUTO_INCREMENT=501;

For example, let us say you have the following MySQL table with ID column set to auto increment.

create table students(
  id       int(16) auto_increment, 
  name     varchar(40),
  PRIMARY KEY (id)
)

Next, we set the initial value of auto increment column to be 501.

ALTER TABLE students AUTO_INCREMENT=501;

Now we add a couple of rows into your table.

insert into students(name) values('John Doe','Jane Doe','Jim Doe');

Let us run a select query to fetch inserted records. You will see that the primary key value starts from 501 and not 1.

select * from students
id, name
501, John Doe
502, Jane Doe
503, Jim Doe

In the above example, we have set initial value of auto increment column to be 501. If you also want to change the increment value from 1 to something else, then you can run the following command to reset it.

If you want to change initial value of all auto increment fields to the same value (e.g.100) then you can run the following SQL query for this purpose.

SET GLOBAL auto_increment_offset=100;

Once you run the above query, whenever you create a new auto increment field thereafter, its initial value will be 100, instead of 1. But it will not affect existing auto increment fields. Also, this change will be present only till your session lasts. In your next session, again the initial value will be set to 1. It means thereafter if you create any new auto increment field, its initial value will be 1 and not 100. In this case also, it will not affect any existing auto increment fields, but only new ones created thereafter.

If you want to permanently change the initial value of all auto increment fields open MySQL configuration file my.cnf/my.ini and add the following line to it.

auto-increment-offset = 100

Save and close the file. And then restart MySQL server to apply changes. Thereafter all new auto increment columns will have initial value of 100. On the other hand, it will not affect the existing auto increment columns.

Here is the SQL query to change the increment value to 5 instead of 1.

SET GLOBAL auto_increment_increment=5;

Once you run the above query, all auto increment fields will increment 5 at a time(e.g. 1, 6, 11, 16, …), instead of 1. But please note, this will last only till your current session. Once you start a new session, all auto increment fields will once again increment only by 1 at a time.

If you want to permanently change the auto increment value, open MySQL configuration file my.cnf/my.ini and add the following line to it.

auto-increment-offset = 5

Save and close the file. Restart MySQL server to apply changes.

In this article, we have learnt how to change initial value and increment value of auto increment columns in MySQL.

How to Set Initial Value & Auto Increment in MySQL

How to Set Initial Value & Auto Increment in MySQL

Related posts:

Leave a Reply Cancel reply