В настоящее время у меня возникают проблемы с ID первичного ключа, который настроен на auto increment . Он продолжает увеличивать значение ON DUPLICATE KEY .
Например:
ID | field1 | field2 1 | user | value 5 | secondUser | value 86 | thirdUser | value
Из приведенного выше описания вы заметите, что у меня есть 3 входа в этой таблице, но из-за автоматического приращения при каждом обновлении идентификатор имеет 86 для третьего входа.
Есть ли вообще избежать этого?
Вот как выглядит мой mySQL-запрос:
INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2) ON DUPLICATE KEY UPDATE field1 = :value1, field2 = :value2
И вот как выглядит мой стол;
CREATE TABLE IF NOT EXISTS `table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` varchar(200) NOT NULL, `field2` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `field1` (`field1`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Вы можете установить innodb_autoinc_lock_mode конфигурации innodb_autoinc_lock_mode в "0" для «традиционного» режима блокировки автоинкремента, что гарантирует, что все операторы INSERT будут назначать последовательные значения для AUTO_INCREMENT .
Тем не менее, вы не должны зависеть от того, что идентификаторы автоматического инкремента являются последовательными в вашем приложении. Их цель – предоставить уникальные идентификаторы.
В настоящее время у меня возникают проблемы с
IDпервичного ключа, который настроен наauto increment. Он продолжает увеличивать значениеON DUPLICATE KEY
Один из нас, должно быть, неправильно понимает проблему, или вы неверно представляете ее. ON DUPLICATE KEY UPDATE никогда не создает новую строку, поэтому она не может увеличиваться. Из документов :
Если вы укажете ON DUPLICATE KEY UPDATE, и будет вставлена строка, которая приведет к дублированию значения в индексе UNIQUE или PRIMARY KEY, MySQL будет выполнять UPDATE старой строки.
Теперь, вероятно, произойдет автоматическое увеличение, когда вы вставляете и не обнаруживаете дублирующийся ключ. Если я предполагаю, что это то, что происходит, мой вопрос будет: почему это проблема?
Если вы абсолютно хотите контролировать значение вашего первичного ключа, измените структуру таблицы, чтобы удалить флаг auto-increment , но сохраните это обязательное, ненулевое поле. Это заставит вас предоставить ключи самостоятельно, но я бы поспорил, что это станет для вас большей головной болью.
Мне действительно интересно: почему вам нужно подключить все отверстия в значениях ID ?
Это поведение легко увидеть ниже с настройкой по умолчанию для innodb_autoinc_lock_mode = 1 («последовательный» режим блокировки). Также обратитесь к прекрасной странице руководства под названием AUTO_INCREMENT Handling in InnoDB . Изменение этого значения уменьшит параллелизм и производительность с установкой = 0 для режима блокировки «Tranditional», поскольку он использует блокировку AUTO-INC на уровне таблицы.
При этом ниже указано значение по умолчанию = 1.
Я собираюсь показать вам четыре примера того, насколько легко создавать пробелы.
Пример 1:
create table x ( id int auto_increment primary key, someOtherUniqueKey varchar(50) not null, touched int not null, unique key(someOtherUniqueKey) ); insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1; select * from x; +----+--------------------+---------+ | id | someOtherUniqueKey | touched | +----+--------------------+---------+ | 1 | dog | 2 | | 3 | cat | 1 | +----+--------------------+---------+
Пробел (id = 2 пропущен) обусловлен одной из нескольких операций и причуд и нервных подергиваний двигателя INNODB . В стандартном высокопроизводительном режиме параллелизма он выполняет распределение интервалов пробелов для различных запросов, отправленных ему. У кого-то есть хорошие причины изменить эту настройку, потому что это влияет на производительность. Похоже на то, что более поздние версии MySQL доставляют вам, и вы отключились из-за Hyper Focusing на пробелы в листах распечаток (и боссы, которые говорят «Почему у нас есть пробелы»).
В случае вставки в обновлении повторяющихся ключей ( IODKU ) он принимает 1 новую строку и выделяет для нее слот. Помните, что параллелизм и ваши сверстники выполняют одни и те же операции, возможно, сотни одновременно. Когда IODKU превращается в Update , ну, используется использование этой заброшенной и никогда вставленной строки с id = 2 для вашего соединения и для кого-либо еще.
Пример 2:
То же самое происходит во Insert ... Select From как показано в этом ответе . В нем я намеренно использую MyISAM из-за сообщения о подсчетах, min, max, в противном случае отклонение диапазона пробелов будет выделять и не заполнять все. И цифры выглядели бы странно, поскольку этот ответ касался фактических чисел. Таким образом, более старый движок ( MyISAM ) работал отлично для жестких не-пробелов. Обратите внимание, что в этом ответе я пытался сделать что-то быстро и безопасно, и эта таблица могла быть преобразована в INNODB с ALTER TABLE после факта. Если бы я сделал этот пример в INNODB для начала, было бы много пробелов (в режиме по умолчанию). Причина, по которой Insert ... Select From INNODB бы пробелы в этом ответе, если бы я использовал INNODB объяснялся неопределенностью подсчета, механизмом, который движок выбирает для безопасного (неопределенного) распределения диапазона. Механизм INNODB знает операцию, знает, что должен создать безопасный пул идентификаторов AUTO_INCREMENT , имеет параллелизм (другие пользователи думают), а пробелы процветают. Это факт. Попробуйте пример 2 с двигателем INNODB и посмотрите, что вы придумали для min, max и count. Макс не будет равным счетчику.
Примеры 3 и 4:
Существуют различные ситуации, которые вызывают INNODB задокументированные на веб-сайте Percona, поскольку они натыкаются на большее количество и документируют их. Например, это происходит во время неудачных вставок из-за ограничений внешнего ключа, наблюдаемых в этом изображении ошибки 1452 . Или ошибка основного ключа в этом изображении ошибки 1062 .
Помните, что промежутки INNODB существуют как побочный эффект производительности системы и безопасного движка. Это что-то действительно хочет отключить (производительность, более высокая степень удовлетворенности пользователей, более высокий параллелизм, отсутствие блокировок в сети), ради более жестких диапазонов идентификаторов? Диапазоны, в которых есть дыры при удалении. Я бы предложил не для моих реализаций, а значение по умолчанию с Performance просто отлично.