Hello, THIS is Yes.

Last Friday, one of my readers asked me a question that was at once familiar, obscure and interesting, so I shared it here.

The first is to create a table

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Copy the code

Table has no fancy head, primary key is ID, and then increment.

Execute an insert statement:

insert into t (id,c,d) values (1.1.1), (null.2.2), (7.7.7), (null.4.4);
Copy the code

The statement contains both the specified ID and the incremented ID that MySQL calculates itself.

Then, at this point, another SQL is executed

insert into t (id,c,d) values (null.9.9);
Copy the code

What ID do you think this insert should be?

You’d think it was 9, but the new record’s ID is 10.

The last record of the first insert statement is (null,4,4), and its ID is 8. Why does the next insert increment change its ID to 10 instead of 9?

Why is there a blank ID in the middle?

To tell the truth, I do not know, so I go to the official website looking and looking, as expected, heaven pays off.

A statement that contains a specified ID and inserts with an incrementing ID is referred to as mixed-mode inserts.

Then, there are three modes of self-increasing ID, which I have analyzed in previous articles:

So the default value is 1.

Then the default value is 1 and the inserts are mixed-mode inserts, sparking.

A simple machine:

The exception is “mixed mode insert”, where the user provides explicit values of the AUTO_INCREMENT column for some, but not all, of the multiple “simple insert” rows. For such inserts, InnoDB allocates more automatic increments than the number of rows to insert. However, all automatically allocated values are continuously generated (and therefore higher than) the automatic increment value generated by the last statement executed. “Redundant” numbers are lost.

If innodb_autoinc_lock_mode = 1, InnoDB will allocate the number of ids for the extra rows, and the extra ids will be discarded.

So it looks like the back insert is broken, like part of the ID has somehow been eaten.

So why is InnoDB doing this?

If you want to know this problem, that must see the source code, the cost is too big.

I’m going to take a blind guess. If you insert an ID and let InnoDB calculate the increment ID, it’s a little bit difficult to implement the code, and it’s a little bit expensive to make a perfect increment ID sequence.

In simple terms, the gain is not worth the loss, the increment of ID so much, less one or two does not matter, so simple implementation, a more prepared point, so that the insertion will not be wrong ~

Good, the website links: dev.mysql.com/doc/refman/…

That’s the end of this short ride.

I’m yes, from a little bit to a billion bits. See you next time.