How to set initial value and auto increment in MySQL?

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'

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.