I have a MySQL database with a id field that is AUTO_INCREMENT . You have some configuration problem in it because at the time of making the auto increment it is coming out exactly in this sequence:
ID: 2
ID: 12
ID: 22
ID: 32
ID: 42
I would like to know how to configure my db so that it normally exits: 1,2,3,4 ...
First check that you are incrementing from 1 to 1 with the following command:
SHOW VARIABLES LIKE 'auto_inc%'
If you get this result:
isdoingtheincrementalprocesscorrectly,ifthevalueofauto_increment_incrementisdifferentfrom1thisistheproblemandtosolvedo
SETGLOBALauto_increment_increment=1;//mudaráglobalmenteSET@@SESSION.auto_increment_increment=1;//mudaráasessãoatualsenãohouverreinicioandrepeatthestarttocheckifithaschangedandisequaltothefigure.
Reference: Data Administrator - MySQL Auto_increment going 2 by 2
This is a basic configuration, usually used when you have DBs divided into more than one machine (so they can be joined without conflict, or to identify the machine responsible for each data group)
The two variables that control this are:
auto_increment_offset
sysvar_auto_increment_increment
Since the first defines the initial reference number, and the second the range.
This option controls the interval between increments. The default for an installation is this:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
See what happens when you change auto_increment_increment :
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
Checking variables:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
And entering the data:
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 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
The value started at 1, which is auto_increment_offset , and "skips" from 10 to 10, which is auto_increment_increment .
offset determines the starting point of the auto-increment column. Here's what happens when you change the value to 5, continuing from the previous example:
mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
And inserting some more data:
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Here's the result:
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
| 35 |
| 45 |
| 55 |
| 65 |
+-----+
8 rows in set (0.00 sec)
In this case, the offset changed, ie the "jumps" continued 10 by 10, but as if they had started from number 5. Note that what was already in the DB was not changed, only the following data.