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.

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'

Solution 3

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.

Solution 4

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

Solution 5

With CREATE TABLE statement

CREATE TABLE my_table (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) AUTO_INCREMENT = 100;

or with ALTER TABLE statement

ALTER TABLE my_table AUTO_INCREMENT = 200;

View more solutions

Comments

[Pacerier]

Are you talking about phpMyAdmin?

[Saturnian]

No, I think he's talking about the MySQL Workbench.

[Bojan Hrnkas]

@Saturnian, yes you are right, I meant the Workbench. I will edit my Post to clarify that.

[William Hu]

How should i do if i want to set a id < 1000 for special penguins?

[hellcode]

You can insert any free id, just put it in the column list: insert into penguins (my_id, skipper) values(999, "explicit id"); (when using 0 instead of 999 the auto increment value will be inserted)

[Michael Hoffmann]

I realize this was 7 years ago, but... Can I get an explanation of this answer's parts? What does ADD INDEX do here? Do I have to add it in the same statement as the id column, or can I define the id column in the CREATE TABLE block and then ADD INDEX(id)?

[Anatoliy]

Heh, time passes by... Sure, define it in CREATE TABLE if you are able to do that. The second "ALTER TABLE" part of answer implies that you have already created a table, and it is probably already deployed somewhere without proper index, which is required for first "ALTER TABLE" to work as intended. I hope this explanation helps.

[Michael Hoffmann]

Yes, that does help. I was looking at some samples that used these statements in a similar way, and they make more sense now. Thank you.

[djsumdog]

The reason for the ADD INDEX is because without it, if you already have a primary key on an existing table, you'll get there can be only one auto column and it must be defined as a key. You need the index so it will be a MUL key.

[Solomon Closson]

I tried this on a table that was just created without any rows added yet. Seems this does not work unless there has been atleast 1 row added to the table first.

[tekagami]

phpMyAdmin solved an autoincrement issue in one step, I tried going the code route and it wasnt working.

[Rafael M]

Also if you want to do this on a single line you can use this idiom: ` ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 1001, ADD INDEX (id);`

[Hayden Thring]

hint, if you set it to 1 it will default to be just above the highest existing id

[Vladislav Ladicky]

Good point / trick