6
I have a Mysql database with a field id that is AUTO_INCREMENT. Has some configuration problem in it because when doing auto increment 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 to exit normally: 1,2,3,4...
6
First check if you are incrementing from 1 in 1 with the following command:
SHOW VARIABLES LIKE 'auto_inc%'
if it achieves this result:
is doing the increment process correctly, if the value of the auto_increment_increment is different from 1 this is the problem and to solve do:
SET GLOBAL auto_increment_increment=1; // mudará globalmente
SET @@SESSION.auto_increment_increment=1; // mudará a sessão atual se não houver reinicio
and repeat the start to check if it has changed and is equal to the figure.
Reference: Data Administrator - Mysql Auto_increment going 2 by 2
4
This is a basic configuration, commonly used when you have Dbs divided into more than one machine (so that they can be joined without conflict, or for identification of the machine responsible for each data group)
The two variables that control that are:
auto_increment_offset
sysvar_auto_increment_increment
Where the former defines the initial reference number, and the latter the interval.
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 the auto_increment_increment:
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
Checking the 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 in 1, which is the auto_increment_offset, and "jumps" every 10, which is auto_increment_increment.
The offset determines the starting point of the auto-increment column. See 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 entering 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
That is 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, what changed was the offset, i.e., the "jumps" continued from 10 to 10, but as if they had started from number 5. Note that what was already in DB was not changed, only the following data.
https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_offset
Browser other questions tagged mysql sql mysqli auto-increment
You are not signed in. Login or sign up in order to post.
Hello diego, could you kindly edit the question and include the version of Mysql and the engine used? Also include the exit of the query
SHOW VARIABLES LIKE 'auto_inc%'– Anthony Accioly