0

I have this table:

CREATE TABLE `test` (`id` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

| id | name |
| -- | ---- |

In which I insert the following two rows:

INSERT INTO `test`(`name`) VALUES ('Oscar'), ('Alba')

| id | name  |
| -- | ----- |
| 1  | Oscar |
| 2  | Alba  |

Now, I want to update the existing rows and create new ones:

INSERT INTO `test` (`id`, `name`) VALUES (1, 'Oscar'), (2, 'Nadia'), ('', 'Pedro') ON DUPLICATE KEY UPDATE `id` = VALUES (`id`), `name` = VALUES (`name`)

| id | name  |
| -- | ----- |
| 1  | Oscar |
| 2  | Nadia |
| 3  | Pedro |

If I update existing rows again and create new ones, this is what happens:

INSERT INTO `test` (`id`, `name`) VALUES (1, 'Oscar'), (2, 'Nadia'), (3, 'Lucas'), ('', 'Maria'), ('', 'Sergio') ON DUPLICATE KEY UPDATE `id` = VALUES (`id`), `name` = VALUES (`name`)

| id | name   |
| -- | ------ |
| 1  | Oscar  |
| 2  | Nadia  |
| 3  | Lucas  |
| 6  | Maria  |
| 7  | Sergio |

There is a jump in the auto increment id. I don't know why this happens. I would like to know if there is any way to avoid this.

Oscar VA
  • 45
  • 7
  • Does this answer your question? [How to ensure no gaps in auto\_increment numbers?](https://stackoverflow.com/questions/34564512/how-to-ensure-no-gaps-in-auto-increment-numbers) – danblack Feb 21 '21 at 03:11
  • 1
    Short answer, stop expecting them to be gapless. Be happy with uniqueness. – danblack Feb 21 '21 at 03:12

0 Answers0