Неожиданное поведине AUTOINCREMENT с INSERT IGNORE и UNIQUE

352

13 сентября 2017, 12:20

Проверял на MySQL 5.7.19 и MariaDB 10.2.8 результат аналогичный.

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `code` INT UNSIGNED NOT NULL UNIQUE,
    `title` VARCHAR(16) NOT NULL
)
ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT IGNORE INTO `test`(`code`,`title`) VALUES (1, 'Title 1');
INSERT IGNORE INTO `test`(`code`,`title`) VALUES (1, 'Title 1');
INSERT IGNORE INTO `test`(`code`,`title`) VALUES (1, 'Title 1');
INSERT IGNORE INTO `test`(`code`,`title`) VALUES (2, 'Title 2');
SELECT * FROM `test`;

Вместо ожидаемого:

1, 1, Title 1
2, 2, Title 2

Получаю:

1, 1, Title 1
4, 2, Title 2

То есть INSERT IGNORE, даже если не вставляет данные, всё равно инкрементирует автоинкремент.

Это документированное поведение?

Почему так?

Что с этим можно сделать, кроме как поместить все INSERT-ы в 1 запрос, так оно работает ожидаемо, но не решает очевидную проблему, и конечно же, кроме, как проверять на отсутствие данных перед вставкой отдельным запросом SELECT.

P.S. На крайний случай, меня бы даже устроило, если можно было проверку на отсутствие данных и вставку этих данных поместить в 1 запрос, но в первую очередь интересует, почему так вообще происходит и баг это или фича?

Answer 1

1 Да, это фича по дизайну innodb-auto-increment-lock-modes.

2 Потому что MySQL.

3 Заменил IGNORE этим костылём, имитирующим WHERE у INSERT.

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `code` INT UNSIGNED NOT NULL UNIQUE,
    `title` VARCHAR(16) NOT NULL
)
ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `test`(`code`,`title`) SELECT s.* FROM (SELECT 1,'Title 1')`s` WHERE NOT EXISTS(SELECT NULL FROM `test` WHERE `code`=1);
INSERT INTO `test`(`code`,`title`) SELECT s.* FROM (SELECT 1,'Title 1')`s` WHERE NOT EXISTS(SELECT NULL FROM `test` WHERE `code`=1);
INSERT INTO `test`(`code`,`title`) SELECT s.* FROM (SELECT 1,'Title 1')`s` WHERE NOT EXISTS(SELECT NULL FROM `test` WHERE `code`=1);
INSERT INTO `test`(`code`,`title`) SELECT s.* FROM (SELECT 2,'Title 2')`s` WHERE NOT EXISTS(SELECT NULL FROM `test` WHERE `code`=2);
SELECT * FROM `test`;

Создание интернет-магазина игрушек в ABCname

ПОСЛЕДНИЕ ВОПРОСЫ

Все вопросы

ТОП-10

Все популярные вопросы

Visual Studio не подключается к базе данных

База написана при помощи Entity Framework(к MySQL)Изначально написана в одном проекте, но я захотел зайти со второго, скачал EF, начал подключаться на уже...

385

Java