Home >>MySQL Tutorial >MySQL auto increment

MySQL auto increment

MySQL auto_increment

auto_increment is keyword whose values are generated by system itself . every time a new record is entered in the database , the value of this field is incremented by one. for example: consider a employee table tat contain four fields name, emp_id, email, mobile number in this table emp_id is defined as auto_increment. for any new user only three fields need to provide value , the fourth field is auto generated. This field must be defined as integer.

How to rearrange the auto_increment field(Emp_id) after being deleted row 4

Emp_id Name Email Mobile
1 devesh devesh@gmail.com 9910099100
2 deepak deepak@gmail.com 9210053520
3 ravi ravi@gmail.com 9810098100
5 nitin nitin@gmail.com 9015501234

Query rearrange the auto_increment value

	Alter table emp drop Emp_id;

	Alter table emp auto_increment=1;

	Alter table emp ADD Emp_id bigint unsigned not null  auto_increment primary key first;

Output :
Emp_id Name Email Mobile
1 devesh devesh@gmail.com 9910099100
2 deepak deepak@gmail.com 9210053520
3 ravi ravi@gmail.com 9810098100
4 nitin nitin@gmail.com 9015501234

In above example suppose we deleted entry 4 from the table , then we have to rearrange the records in the table and change the value of auto_incremented variable accordingly.

Initialize auto_increment field(Emp_id) from 1000 at the place of 1.

Emp_id Name Email Mobile
1 devesh devesh@gmail.com 9910099100
2 deepak deepak@gmail.com 9210053520
3 ravi ravi@gmail.com 9810098100
4 nitin nitin@gmail.com 9015501234
	Alter table emp drop Emp_id;

	Alter table emp auto_increment=1000;

	Alter table emp ADD Emp_id bigint unsigned not null
  auto_increment primary key first;
Output :
Emp_id Name Email Mobile
1000 devesh devesh@gmail.com 9910099100
1001 deepak deepak@gmail.com 9210053520
1002 ravi ravi@gmail.com 9810098100
1003 nitin nitin@gmail.com 9015501234

The value of auto_increment is 1 by default. if we want to start the value from any different values say 1000 then we can do it with the help of above mentioned query.


No Sidebar ads