How to set initial value and auto increment in MySQL?

MySQL Workbench

If you want to avoid writing sql, you can also do it in MySQL Workbench by right clicking on the table, choose "Alter Table ..." in the menu.

When the table structure view opens, go to tab "Options" (on the lower bottom of the view), and set "Auto Increment" field to the value of the next autoincrement number.

Don't forget to hit "Apply" when you are done with all changes.

PhpMyAdmin:

If you are using phpMyAdmin, you can click on the table in the lefthand navigation, go to the tab "Operations" and under Table Options change the AUTO_INCREMENT value and click OK.

First you need to add column for auto increment

alter table users add column id int(5) NOT NULL AUTO_INCREMENT FIRST

This query for add column at first. Now you have to reset auto increment initial value. So use this query

alter table users AUTO_INCREMENT=1001

Now your table started with 1001

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);

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'