How to start auto increment from a specific point in MySQL - MySQL Developer Tutorial

How to  start auto increment from a specific point in MySQL

When we create a column in table with Auto Increment in MySQL, it start the Auto Increment value from 1 and then keep increment by one on each new insert. Let's say if you want to start the Auto Increment with some specific number, you can do that.

In below example, I want to start the Auto increment from 100 and then increment will happen after 100.

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL auto_increment,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  Constraint pk_idcustomer Primary key (idcustomer)

) auto_increment=100;

Set Auto Increment for Existing Table: 

If you have already created the table and you would like to set the Auto Incremental point, you can do by using below statement in MySQL. I am setting the Auto incremental to 500 in customer table.

Alter table customer 
Auto_increment =500;

Starting auto increment from specific point in MySQL or MariaDB