PDO после вставки одной и той же записи с одинаковым уникальным полем растет автоинкремент, в чем проблема?

Question

[Анна]

Скармливаю запрос через prepare на вставку в таблицу. Дальше специально вставляю дублирующиеся записи раз 10 подряд! Ну с одинаковым email. Отлично в БД попадает одна запись. Но автоинкремент не 2 а уже 10. Почему так и как это исправить?

$ct = $pdo->prepare('INSERT INTO `users`(`email`, `pass`) VALUES (:email,:pass)');
		$result = $ct->execute(
			array(
				':email' => $params['email'],
				':pass'  => $params['pass'],
			)
		);

Answer 1

[AlikDex]

недавно столкнулся с подобной проблемой, но в транзакциях. При INSERT IGNORE INTO получались большие пробелы в id'ах с автоинкрементом. Нагуглил в конфиг настройку для такого:
innodb_autoinc_lock_mode = 0
С этой опцией не повышается счетчик автоинкремента в транзакциях при использовании уникального индекса, попробуйте, может получится.

Comments

[Анна]

Спасибо реально помогло...

Answer 2

[Виталий Инчин ☢]

Патамушта.

Результаты могут показаться противоестественным, потому что ряд AUTO_INCREMENT значений вычисляется без учета каких-либо значений, уже присутствующими в колонне, и последующее значение вставлена ​​наименьшее значение в серии, который больше, чем максимальный существующее значение в AUTO_INCREMENT колонке. Другими словами, серия рассчитывается следующим образом:

auto_increment_offset + N × auto_increment_increment

где N является положительным целым числом, значение в серии [1, 2, 3, ...].

Номер ячейки уникален? Уникален. А значит все верно:

Атрибут AUTO_INCREMENT может использоваться для генерации уникального идентификатора для новых строк

А хотите четкий порядок, назначайте id , исходя из
COUNT(*)

Comments

[Анна]

Это не верно лишний запрос к БД на mysql было проще не было роста инкремента

Answer 3

[Rsa97]

Ну 10, и что? Уже имеющаяся запись своего id не меняет, так? Этого вполне достаточно, чтобы id были уникальными, а непрерывность значений колонки с AUTO_INCREMENT никто и не гарантирует.

Comments

[Анна]

В случает атаки) если пробьъються то за 5 минут у меня пропустяться ытсячи нужных мне айдишников, в случае очень хорошей атаки перезабьется автоинкремент полностью

[Анна]

До это mysql сколько раз юзал небыло такого...

[Анна]

Разница в том, что там не было prepare

[Rsa97]

Сергей Мурко: Привязка к последовательности id - косяк в базе. При правильной структуре их можно генерировать хоть случайным образом, лишь бы они были уникальными. AUTO_INCREMENT - это всего-навсего самый простой способ обеспечить уникальность.
Для колонки id никто не мешает задать тип и BIGINT(20), ну и при правильной организации FOREIGN KEY никто не мешает перенумеровать все записи и сдвинуть счётчик инкремента.

[Анна]

Rsa97: я планирую после каждого инсерта привязать процерудуру кторая будет проверять id последней записи и автоинкремент если что его обновлять на нужный. Как думаете верное решение? просто почему у меня id идут последовательно?) просто я по ним сортирую и очень большое количество выборок идет по id скажем так это как временная переменная

[Виталий Инчин ☢]

Сергей Мурко: зачем какие-то процедуры, если id можно ставить прямо при INSERT?

[Rsa97]

Сергей Мурко: Сортировка по искусственному id - в корне неверное решение, это нарушение первой нормальной формы

Нет упорядочивания строк сверху вниз (другими словами, порядок строк не несет в себе никакой информации).

Вообще, если есть возможность использовать естественный ключ, то лучше его и использовать. Автоинкрементный целочисленный id используют когда естественный ключ оказывается слишком длинным. Но и в этом лучше использовать id для связи таблиц и идентификации строки, но не для сортировки строк.

[Анна]

Rsa97: я лучше потеряю нормальную форму зато запросы буду выполнять быстрей, вообще этот термин нормальная форма, не к чему не нужен я так считаю, мне важно быстрей получить данные и память не забывать, а то что какой-то дядя написал мол надо делать так, я это не считаю правильным. Если всем напишут спрыгнуть с крыши все же не побегут прыгать с крыши. Я уже не раз сталкивался, когда денормализация приводила к оптимизации

[Анна]

Виталий Инчин: Ну хочется мне чтоб все id записей шли по порядку)

[Виталий Инчин ☢]

Сергей Мурко:

INSERT INTO `users`(`email`, `pass`, id) VALUES (:email,:pass, COUNT(*));

[Виталий Инчин ☢]

Кавычки не поставил*

[Анна]

не count при каждом запросе напряжно) получаеться ему все придеться считать если записей в базе миллион) я просто после вставки вызываю процедуру, которая дергает последний id и его переношу во вспомогательную таблицу

[Анна]

Виталий Инчин: ответ выше)

[Rsa97]

Сергей Мурко: По уму, денормализация выполняется в последнюю очередь, уже по результатам анализа работы реальной базы. На этапе проектирования и разработки базы стараются сделать нормализованными.
Что касается строго последовательных id, то здесь проблема с транзакционностью. В MySQL по умолчанию каждый вызов оператора SQL - отдельная транзакция. Поскольку счётчик автоинкремента один на таблицу, то перед попыткой добавления строки значение счётчика увеличивается, а если строка не добавилась, то уменьшать это значение нельзя - другая транзакция могла, в свою очередь, также использовать этот счётчик, увеличив его значение. С точки зрения СУБД это выглядит примерно так:

транзакция 1 - начать
транзакция 1 - увеличить счётчик, получить новое значение (99)
транзакция 2 - начать
транзакция 2 - увеличить счётчик, получить новое значение (100)
транзакция 1 - захватить таблицу, попытка добавления строки
транзакция 1 - ошибка добавления строки, отпустить таблицу
транзакция 2 - захватить таблицу, попытка добавления строки
транзакция 1 - завершить
транзакция 2 - строка добавлена, отпустить таблицу
транзакция 2 - завершить

[Анна]

Rsa97: Спасибо за совет про вторую транзакцию я то и не подумал, но в итоге остановился на том, что именно в этой таблице я буду делать все по уму) это единственная таблица где я хочу красоту) все остальные напрямую от нее зависят)

[Анна]

Rsa97: Не думаю что у меня за секунду прилетит миллион регистраций) Все остальные записи уже можно шалам балам делать) как хочет нормализация)

[Анна]

Rsa97: Я щас вообще переписываю все) хочу php только проверять входные параметры и потом эти параметры передавать процедуре mysql)