Search code examples
mysqlsqlauto-increment

Mysql Irregular Auto_Increment increment

The table is as follows. Interestingly, the 'id' column with Auto_Increment is increasing irregularly and high.


SELECT * FROM `numbers_api` ORDER BY `id` DESC

 id         |  email                |  name
--------------------------------------------
  562984864 | bla[at]blabla.com     |  test
  562956541 | bla1[at]blabla.com    |  test1
  562944637 | bla2[at]blabla.com    |  test2
  562944634 | bla3[at]blabla.com    |  test3

Table structure for id:

enter image description here

I am doing the insert operation into this table as follows ($Querys[] inside a loop) :

$Querys[] = "INSERT IGNORE INTO numbers_api (email, name) VALUES ('" . $customerEmail . "', '" . $name . "');";

$this->db->query(implode("\r\n", $Querys));
Solution

  • There is probably a unique index on email, name or their combination.

    Everytime you try to insert a row, an ID gets created. But only when INSERT IGNORE succeeds, i.e. when no unique constraint gets violated, will this ID be used.

    With

    create table numbers_api (id int auto_increment, email varchar(100), name varchar(100));

create unique index idx on table numbers_api (email, name);

INSERT IGNORE INTO numbers_api (email, name) VALUES ('A', 'A');

INSERT IGNORE INTO numbers_api (email, name) VALUES ('A', 'A');
INSERT IGNORE INTO numbers_api (email, name) VALUES ('B', 'B');

INSERT IGNORE INTO numbers_api (email, name) VALUES ('A', 'A');
INSERT IGNORE INTO numbers_api (email, name) VALUES ('B', 'B');
INSERT IGNORE INTO numbers_api (email, name) VALUES ('C', 'C');

    The table will have three rows:

    id email name
    1 A A
    3 B B
    6 C C

    because the second insert on A|A fails, thus wasting the ID 2, and the attempts to insert A|A and B|B after inserting the first B|B also fail, wasting IDs 4 and 5.

    Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ca09b5c09ed10116bf27d83935f3e608