Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set an AUTO_INCREMENT field with to start with the value 6000 in mysql?

How to set a field auto increment without auto increment key in mysql or how set a field auto increment with start value 6000 in mysql?

like image 406
Ajay Avatar asked Aug 26 '10 21:08

Ajay


People also ask

How do you set a field as auto increment in MySQL?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.

Can you set auto increment?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

How does the AUTO_INCREMENT work in MySQL?

Auto-increment allows a unique number to be generated automatically whenever a new record is inserted into a table. This feature is especially useful in the primary key field so that the key can be set automatically every time a new record is inserted.


2 Answers

... how set a field auto increment with start value 6000 in mysql?

If your table already exists:

ALTER TABLE your_table AUTO_INCREMENT = 6000;

If you are creating your table from scratch:

CREATE TABLE your_table () AUTO_INCREMENT = 6000;

Source and further reading:

  • MySQL 5.1 Reference Manual :: Using AUTO_INCREMENT

Test case:

CREATE TABLE users (
   user_id  int NOT NULL, 
   name     varchar(50),
   PRIMARY KEY (user_id)
);

INSERT INTO users VALUES (1, 'Bob');
INSERT INTO users VALUES (2, 'Joe');
INSERT INTO users VALUES (3, 'Paul');

ALTER TABLE users MODIFY user_id int NOT NULL AUTO_INCREMENT;
ALTER TABLE users AUTO_INCREMENT = 6000;

INSERT INTO users (name) VALUES ('Keith');
INSERT INTO users (name) VALUES ('Steve');
INSERT INTO users (name) VALUES ('Jack');

SELECT * FROM users;
+---------+-------+
| user_id | name  |
+---------+-------+
|       1 | Bob   |
|       2 | Joe   |
|       3 | Paul  |
|    6000 | Keith |
|    6001 | Steve |
|    6002 | Jack  |
+---------+-------+
6 rows in set (0.01 sec)
like image 78
Daniel Vassallo Avatar answered Sep 23 '22 02:09

Daniel Vassallo


ALTER TABLE tbl_name AUTO_INCREMENT = 6000

but be aware you should have no PK lager 6000 in this table !

like image 45
Rufinus Avatar answered Sep 22 '22 02:09

Rufinus