MySQL DROP TABLE and TRUNCATE not fully resetting AUTO-INCREMENT-CodePudding

I have a table in a MySQL database called Events that is defined like this:

CREATE TABLE IF NOT EXISTS `TrackIt`.`Events` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `EventType` INT NULL,
  `timestamp` TIMESTAMP NULL,
  `Comment` TEXT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Events_EventTypes_idx` (`EventType` ASC) VISIBLE,
  CONSTRAINT `fk_Events_EventTypes`
    FOREIGN KEY (`EventType`)
    REFERENCES `TrackIt`.`EventTypes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Once in a while, I desire to reset the auto-increment column so that all of the id's are sequential without any gaps. But it seems that no matter which way I accomplish this (either by drop table or truncate), adding another record to the reset table makes the id field much larger than the last id... for example:

If I reset the id field using this method:

CREATE TABLE NewEvents LIKE Events;
INSERT INTO NewEvents (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM Events ORDER BY id;
DROP TABLE Events;
CREATE TABLE Events LIKE NewEvents;
INSERT INTO Events (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM NewEvents ORDER BY id;
DROP TABLE NewEvents;

Then here is what I get from a SELECT, which is expected:

SELECT id FROM Events ORDER BY id DESC;

450
449
448
447
...

HOWEVER, If I then do this, look at the results:

INSERT INTO Events(EventType) VALUES(1);
SELECT id FROM Events ORDER BY id DESC;

512
450
449
448
447
...

So when I do the drop table thing and copy the records over, it correctly re-numbers the id field so that everything is sequential. But when I then add another record, its like it knows there were a bunch of records that were gapped and it somehow just skips that number of records and picks up where it once left off..

At least this is what it looks like is happening to me.

Is there some setting in MySQL that will cause this behavior? Or is there some other command that I can execute between the dropping of the table and the re-creation of it that will fully reset the counter? I'm not understanding what's happening here.

CodePudding user response：

I'm guessing INSERT...SELECT is batching the incrementing of the auto increment column somehow, resulting in the auto_increment value being higher than the number of rows. mysql is not designed to guarantee sequential ids from auto_increment, so I don't find this surprising. I strongly suggest you reconsider whatever is causing you to want them to be sequential.

That said, doing ALTER TABLE Events AUTO_INCREMENT=0; after doing the INSERT...SELECT will reset it so a following insert will use the next available id.

fiddle