MariaDB - INNODB skipping the number sequence whil

问题:

I do not know if this is expected behavior with INNODB, but I really think it's totally weird.

If I use the same SQL statement using MYISAM, the behavior occurs as expected.

MYISAM

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id)
) ENGINE = MYISAM DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

Result (expected behavior):

Now if I use exactly the same statement, however, informing that the engine is INNODB.

INNODB

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

Result (weird result - Skipping the number sequence):

In fact, both engines are creating the expected 4096 records, but I got worried with behavior of INNO, because I'm migrating my databases from MYISAM to INNODB and I do not know how much that can impact my applications.

回答1:

The auto_increment mechanism is required to generate unique values, that are greater than any value it has generated previously. It does not guarantee to generate consecutive values.

There's some discussion about it here: https://bugs.mysql.com/bug.php?id=57643

There is little importance in generating consecutive values faithfully, because any value could be "lost" for other reasons:

• Your INSERT fails, for example because of violating a constraint like UNIQUE KEY or FOREIGN KEY.
• You roll back the transaction for your INSERT.
• You succeed and commit, but later the row is DELETEd by you or another session.

Auto-inc values are not returned to any kind of queue, because other concurrent sessions might have generated further id values in the meantime. It's not worth InnoDB maintaining a pool of unallocated id values, because that pool could become huge and wasteful.

Also, it might be appropriate to "lose" an ID value, or else someone would think the row they meant to DELETE somehow came back.

回答2:

To summarize the reason for this statement, it is a scheduling system that I have that uses this statement to create the calendar table.

Not totally in the scope of your question which was about missing id's.
But there are better ways to generate numbers and or calendar tables then repeating a INSERT ... SELECT multiple times.
All approaches can be used directly JOINed with a other table or used to fill up a (indexed) (temporary) table

For number generating.

If you MariaDB/MySQL version supports windows functions

SET SESSION cte_max_recursion_depth = 5000;

WITH RECURSIVE number_generator(number) AS (
  SELECT 0
  UNION ALL
  SELECT number + 1 FROM number_generator
  WHERE number BETWEEN 0 AND 4096
)
SELECT * FROM number_generator

For MariaDB/MySQL which does not support window functions.

SELECT 
  number_generator.number
FROM (

SELECT 
 @row := @row + 1 AS number
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row3
CROSS JOIN (
   SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row4
CROSS JOIN (
  SELECT @row := -1 
) init_user_params 
) AS number_generator
WHERE
 number_generator.number BETWEEN 0 AND 4096
ORDER BY 
 number_generator.number ASC

For generating a calendar

If you MariaDB/MySQL version supports windows functions

SET SESSION cte_max_recursion_depth = 5000;

WITH RECURSIVE number_generator(number) AS (
  SELECT 0
  UNION ALL
  SELECT number + 1 FROM number_generator
  WHERE number BETWEEN 0 AND 4096
)
SELECT CURRENT_DATE + INTERVAL number_generator.number DAY FROM number_generator

For MariaDB/MySQL which does not support window functions.

SELECT 
  CURRENT_DATE + INTERVAL number_generator.number DAY
FROM (

SELECT 
 @row := @row + 1 AS number
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row3
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row4
CROSS JOIN (
  SELECT @row := -1 
) init_user_params 
) AS number_generator
WHERE
 number_generator.number BETWEEN 0 AND 4096
ORDER BY 
 number_generator.number ASC

The CURRENT_DATE is just a example you can also use a fixed date in the past or future as example you can use '2019-03-01' .

Also the + INTERVAL number_generator.number DAY can also use a negative to generate a list into the past from that date and other values then DAY if you want monthes you can use MONTH, want years you use YEAR