Geek asks and answers

How to automatically increment a column value starting from a value other than the value in mysql?

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

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

Note. I use phpmyadmin to play with db and I have many tables, but only one db.

Thank.

+3
source share
5 answers

server-system-variables:

auto_increment_increment

auto_increment_offset

, 1 (, 15). . , .

.

(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 
....

.

, -, , .

+1

ALTER TABLE tbl AUTO_INCREMENT = 5678 5678. .

+5

,

ALTER TABLE tbl_name AUTO_INCREMENT = 5678;

And can update the counter variable auto_increment using the 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 |
+3
source

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

+1
source

More articles:


All Articles