How to set initial value and auto increment in MySQL?

Solution 1:

Use this:

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

Solution 2:

MySQL - Setup an auto-incrementing primary key that starts at 1001:

Step 1, create your table:

create table penguins(
  my_id       int(16) auto_increment, 
  skipper     varchar(4000),
  PRIMARY KEY (my_id)
)

Step 2, set the start number for auto increment primary key:

ALTER TABLE penguins AUTO_INCREMENT=1001;

Step 3, insert some rows:

insert into penguins (skipper) values("We need more power!");
insert into penguins (skipper) values("Time to fire up");
insert into penguins (skipper) values("kowalski's nuclear reactor.");

Step 4, interpret the output:

select * from penguins

prints:

'1001', 'We need more power!'
'1002', 'Time to fire up'
'1003', 'kowalski\'s nuclear reactor'