By bbtang


2009-09-28 06:18:36 8 Comments

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.

8 comments

@Laxmikant Bhumkar 2019-03-26 08:14:57

Alternatively, or you are lazy to write SQL query. Then this solution is just for you. enter image description here

  1. Open phpMyAdmin
  2. Select desired Table
  3. Click on Operations tab
  4. Set your desired initial Value for AUTO_INCREMENT
  5. Done..!

@ospider 2018-05-12 23:24:37

You could also set it in the create table statement.

`CREATE TABLE(...) AUTO_INCREMENT=1000`

@Arun Kasyakar 2017-05-10 08:00:40

For this you have to set AUTO_INCREMENT value

ALTER TABLE tablename AUTO_INCREMENT = <INITIAL_VALUE>

Example

ALTER TABLE tablename AUTO_INCREMENT = 101

@Bojan Hrnkas 2013-10-11 06:50:39

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.

@Pacerier 2015-02-03 15:19:46

Are you talking about phpMyAdmin?

@Saturnian 2015-03-22 13:46:32

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

@Bojan Hrnkas 2015-03-23 15:05:54

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

@tekagami 2019-03-26 17:09:47

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

@Mostafa Fallah 2015-04-18 15:42:17

Also , in PHPMyAdmin , you can select table from left side(list of tables) then do this by going there.
Operations Tab->Table Options->AUTO_INCREMENT.

Now, Set your values and then press Go under the Table Options Box.

@John 2014-02-18 04:40:23

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

@Eric Leschinski 2014-07-24 17:53:52

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'

@William Hu 2015-03-26 07:33:28

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

@hellcode 2016-04-15 08:22:22

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)

@Anatoliy 2009-09-28 06:26:30

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

@Michael Hoffmann 2016-10-18 00:02:51

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 2016-10-20 22:32:19

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 2016-10-21 00:50:17

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 2016-11-14 20:08:01

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 2019-02-24 23:24:29

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.

@Rafael M 2019-06-27 17:17:38

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

Related Questions

Sponsored Content

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

11 Answered Questions

[SOLVED] How to 'insert if not exists' in MySQL?

6 Answered Questions

17 Answered Questions

[SOLVED] MySQL error code: 1175 during UPDATE in MySQL Workbench

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

8 Answered Questions

[SOLVED] MySQL: Large VARCHAR vs. TEXT?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

11 Answered Questions

24 Answered Questions

[SOLVED] How do you set a default value for a MySQL Datetime column?

  • 2008-10-03 20:24:30
  • Brian Boatright
  • 1200579 View
  • 849 Score
  • 24 Answer
  • Tags:   mysql datetime

7 Answered Questions

[SOLVED] Change auto increment starting number?

  • 2009-06-09 15:01:47
  • John Jones
  • 287766 View
  • 254 Score
  • 7 Answer
  • Tags:   mysql

Sponsored Content