How to set initial value and auto increment in MySQL?

Question

How do I set the initial value for an "id" column in a MySQL table that start from 1001?

I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')";

Without specifying the initial value for the id column.

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

Answer 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'