CODEWITHSUNDEEP
phpmysqldefaultauto-increment
arun nair
arun nair

Reputation: 3683

how to auto-increment column value starting from value other than one in mysql?

i have an existing mysql table with the id column defined as primary, and auto-increment set as true. now, i would like to know if i can set the auto-increment to start from a predefined value, say 5678, instead of starting off from 1.

I would also like to know if i can set the steps for auto-incrementing, say increase by 15 each for each new record insertion (instead of the default increment value of 1).

Note- i am using phpmyadmin to play with the db, and i have many tables but only one db.

Thanks.

Upvotes: 3

Views: 3218

Answers (5)

Pranay Gondane
Pranay Gondane

Reputation: 123

You can see the example here.. http://pranaydac08.blogspot.in/2013/10/how-set-auto-increment-value-start-from.html

Upvotes: 0

Johan
Johan

Reputation: 76557

You can also use the server-system-variables:

auto_increment_increment

and

auto_increment_offset

This will allow you to increase the offset by other values than 1 (e.g. 15) each time. If you start from a different value using the same offset on a different server. This will allow you to keep tables on different servers that can be merged without keys overlapping.

e.g.

(inc = 15 offset = 1)          (inc=15 offset = 2)
table1 on server A             table1 on server B
-----------------------------------------------------
id     name                    id    name
1      bill                    2     john
16     monica                  17    claire 
....

This can be very useful.

Because the main usage is to have the same table on different servers behave in a different way, it is a server setting and not a table setting.

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86366

You can set the auto increment value using below command

ALTER TABLE tbl_name AUTO_INCREMENT = 5678;

And can update the auto_increment counter variable using below command

SET @@auto_increment_increment=15;

Loo at here for more info 

mysql> SET @@auto_increment_increment=15;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  16 |
|  31 |
|  46 |

Upvotes: 3

James Allardice
James Allardice

Reputation: 165971

ALTER TABLE tbl AUTO_INCREMENT = 5678 will set the auto increment to 5678 for that table. Have a look at the detailed information here.

Upvotes: 5

Shadikka
Shadikka

Reputation: 4276

ALTER TABLE whatever AUTO_INCREMENT=5678 - alternatively in phpMyAdmin, go to the "Operations" tab of the table view and set it there. For the increment step, use the setting auto_increment_increment.

Upvotes: 1

Related Questions