ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Холостые инкрементации суррогатного ключа
6 сообщений из 6, страница 1 из 1
Холостые инкрементации суррогатного ключа
    #39078182
vitkorob
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitkorob
Гость
Здравствуйте!

У меня возникла проблема в том, что происходят холостые, нежелательные, необъяснимые (пока) инкрементации суррогатно ключа. Надеюсь на помощь форума.

Я использую 10.0.21-MariaDB-log MariaDB Server

Есть таблица-список тегов к статьям с суррогатным AUTO_INCREMENT ключом `tag_id` .

Код: sql
1.
2.
3.
4.
5.
6.
CREATE TABLE `tags` (
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



Дальше происходит наполнение этой таблицы тегами из свежедобавленной статьи:

Код: sql
1.
2.
3.
4.
INSERT INTO `tags` (`name`) VALUES ('one');
INSERT INTO `tags` (`name`) VALUES ('two');
INSERT INTO `tags` (`name`) VALUES ('two');
INSERT INTO `tags` (`name`) VALUES ('three');



Видно, что третий запрос не выполнится из-за требования уникальности поля `name` . Посмотрим на значение счетчика автоинкремента:

Код: sql
1.
SHOW TABLE STATUS FROM `testdatabase` LIKE 'tags';



Оно равно 5-и. Верно! Так и должно быть 1 + 4 = 5 , потому что БД проверяет ограничения полей и выполняет действия инкрементирования по порядку полей. В третьем запросе сначала произошел инкремент счетчика, потом проверка на уникальность `name` , проверка вернула отрицательный результат -> запрос не выполнился, тем не менее счетчику декрементиться не позволено -> он вырос в холостую (без вставки соответствующего значения). Это еще не моя основная проблема, моя основная проблема ниже, прошу прочитать.

Такое поведение ключа объяснимо, но нежелательно ! Хочется не иметь таких пропусков в суррогатном ключе. Поэтому я нашел в интернете совет воспользоваться временной таблицей - http://stackoverflow.com/a/11276648 при заполнении основной таблицы.

Внимание, уважаемые, код таков:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TEMPORARY TABLE `temp` LIKE `tags`;

INSERT INTO `temp` (`name`) VALUES ('five');
INSERT INTO `temp` (`name`) VALUES ('four');
INSERT INTO `temp` (`name`) VALUES ('three');

INSERT INTO `tags` (`name`) SELECT `temp`.`name` FROM `temp` WHERE `temp`.`name` NOT IN (SELECT `tags`.`name` FROM `tags`);

DROP TEMPORARY TABLE `temp`;



Да, значение 'three' специально повторяется.

После прошлых INSERT мы оставили значение счетчика равным 5, то после такого финта с временной таблицей хочется увидеть его равным 5 + 2 = 7, но нет! Он будет иметь значение 8. При том, что если бы мы сделали следующее:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TEMPORARY TABLE `temp` LIKE `tags`;

INSERT INTO `temp` (`name`) VALUES ('four');
INSERT INTO `temp` (`name`) VALUES ('three');
INSERT INTO `temp` (`name`) VALUES ('two');

INSERT INTO `tags` (`name`) SELECT `temp`.`name` FROM `temp` WHERE `temp`.`name` NOT IN (SELECT `tags`.`name` FROM `tags`);

DROP TEMPORARY TABLE `temp`;



То значение предсказуемо будет 5 + 1 = 6. Как так происходит? Буду благодарен за объяснение поведения и другую схему обхода холостых инкрементаций при дублировании. Спасибо!
...
Рейтинг: 0 / 0
16.10.2015, 02:46:23
| Ответить | Цитировать | Написать  
Холостые инкрементации суррогатного ключа
    #39078207
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
vitkorobХочется не иметь таких пропусков в суррогатном ключе.А мне хочется стать императором Земли, да вот беда - схему боевых треног никак не получается разработать...
А если серьёзно - зачем вам это? Не бывает так, что просто "хочется", "хочется" всегда почему-то. Если "почему-то"="мне нужна непрерывная последовательность порядковых номеров", то заводите под них отдельное поле и нумеруйте записи уже после их реального появления в таблице.
PS. Кстати, в той же статье автор честно предупреждает, что "I haven't tested this for performance".
PPS.vitkorobПосле прошлых INSERT мы оставили значение счетчика равным 5, то после такого финта с временной таблицей хочется увидеть его равным 5 + 2 = 7, но нет! Он будет иметь значение 8.Ну я хз, что у вас происходит, у меня получилось 7.
...
Рейтинг: 0 / 0
16.10.2015, 06:53:29
| Ответить | Цитировать | Написать  
Холостые инкрементации суррогатного ключа
    #39078450
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
vitkorob,
это ожидаемое поведение, задокументированное и полезное, как ни странно.

не обращай внимание.
...
Рейтинг: 0 / 0
16.10.2015, 11:50:17
| Ответить | Цитировать | Написать  
Холостые инкрементации суррогатного ключа
    #39078983
vitkorob
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitkorob
Гость
tanglirНу я хз, что у вас происходит, у меня получилось 7.

На какой версии проверяли? Я сейчас протестировал еще и на версии _5.5.44-0+deb8u1_, которая поставляется с Debian 8, там инкрементация происходит тоже до 8.

MasterZivэто ожидаемое поведение, задокументированное и полезное, как ни странно.

MasterZiv, расскажите, пожалуйста, подробнее алгоритм поведения БД. Просто я пока не понимаю как ожидать ( = предсказать ) такой результат. Я не смог найти место в документации, где о таком поведении говорится. Будьте добры, укажите место в документации.
...
Рейтинг: 0 / 0
17.10.2015, 00:41:07
| Ответить | Цитировать | Написать  
Холостые инкрементации суррогатного ключа
    #39079051
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
vitkorob,
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-traditional.html
...
Рейтинг: 0 / 0
17.10.2015, 09:58:31
| Ответить | Цитировать | Написать  
Холостые инкрементации суррогатного ключа
    #39079053
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Просто я пока не понимаю как ожидать ( = предсказать ) такой результат.

ожидать -всегда. предсказать невозможно.
просто нужно не закладываться на то, что сгенерированные значения строго последовательны.
строго говоря, даже без этого "дефекта" автоинкремента на это надеяться нельзя - есть другие сессии, они могут вклиниться в твою последовательность.
...
Рейтинг: 0 / 0
17.10.2015, 10:03:19
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Холостые инкрементации суррогатного ключа
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39078182&tid=1832604]:
 0ms
get settings:
 6ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 40ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 31ms
get tp. blocked users:
 1ms
others: 200ms
total:  301ms
созд. / загр.: 301ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]