[ Поиск ] - [ Пользователи ] - [ Календарь ]
Полная Версия: Автоинкремент при INSERT DUPLICATE
Страницы: 1, 2
Arh
Столкнулся с такой странной штукой, когда делаю
INSERT INTO `test` (`id`, `value`) VALUES (1, 1)
ON DUPLICATE KEY UPDATE `value` = 2


То у записи меняется id, по сути INSERT DUPLICATE работает как REPLACE, который сначала удаляет запись, а потом добавляет по новой.

Поменял тип таблицы с InnoDB на MyISAM и всё стало ок.

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2
killer8080
Цитата (Arh @ 9.10.2017 - 14:17)
То у записи меняется id, по сути INSERT DUPLICATE работает как REPLACE, который сначала удаляет запись, а потом добавляет по новой.

как это меняется id когда у тебя в запросе один и тот же id ? smile.gif
Или ты про счётчик auto_increment ? Так это нормально для InnoDB
https://habrahabr.ru/post/156489/
Arh
killer8080
Цитата
как это меняется id когда у тебя в запросе один и тот же id ?

INSERT INTO `test` (`nid`, `value`) VALUES (1, 1)
ON DUPLICATE KEY UPDATE `value` = 2


Цитата
Или ты про счётчик auto_increment

Да. Такой вот неприятный, ненормальный сюрприз =)

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2
twin
Цитата (Arh @ 9.10.2017 - 10:57)
Такой вот неприятный, ненормальный сюрприз
Чем он так неприятен? И почему ненормален?

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
sergeiss
Arh, это абсолютно нормальная работа автоинкремента. Потому что при запуске ИНСЕРТа резервируется значение и автоинкрементное переменное поле увеличивается. Если оно не используется, то система забывает о нем навсегда.

_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
twin
sergeiss
Немного не так. Дело тут в блокировках. При работе с InnoDB блокировка идет на уровне строки, в MyISAM блокируется вся таблица. Потому InnoDB работает быстрее. Но она не может предусмотреть, сколько будет вставок, а сколько апдейтов. И потому при вставке берет значение last insert id и увеличивает на количество операций, не важно, инсерт это или апдейт.

Не ясно, чем это вызвало неудобство у Arh, ну если только он не использует репликации. А если использует, то лучше отказаться от ON DUPLICATE KEY UPDATE, либо поставить настройку
innodb_autoinc_lock_mode = 0
Что не так уж полезно, потому что снизит продуктивность.

Но возвращаться на MyISAM я бы не советовал.

А если репликаций нет, то и проблем нет. smile.gif

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
Arh
twin
Цитата
Чем он так неприятен? И почему ненормален?

Потому что по логике он должен обновить поле или создать если его нет. Это прям по запросу такие выводы напрашиваются. Вот потому что ждёшь от него такой логики, а он инкрементит, вот это не нормально, не логично что ли.

А неприятно, потому что опять же ждёшь одного, и как бы пишешь один и тот же запрос, можно сказать используешь один и тот же интерфейс для работы с таблицей, а он берёт и работает как совершенно другой интерфейс, а как должен (по логике) он не работает.

То есть по факту в innodb нет ON DUPLICATE KEY UPDATE и лучше бы он ругался ошибкой, чем скрыто делал бы совершенно другую операцию (DELETE+INSERT вместо UPDATE). Это ненормально на 2+2*2 делать (2+2)*2 и неприятно натыкаться на такую подставу)

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2
twin
Ты чего то не догоняешь. Этот запрос не изменяет первичный ключ существующей строки. Он просто деает "разрывы" а автоинкременте. Если у тебя логика построена на неразрывном диапазоне, значит меняй логику, пока не поздно.

Другими словами при апдейте id строки не изменится. При инсерте ид может присвоиться не по порядку. В чем тут проблема, не ясно.

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
S.Chushkin
Цитата (Arh @ 9.10.2017 - 20:34)
чем скрыто делал бы совершенно другую операцию (DELETE+INSERT

С чего ты взял, что он так делает?

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru
twin
Могу предположить, что у тебя на поле `nid` нет уникального ключа.

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
Arh
twin
Цитата
Этот запрос не изменяет первичный ключ существующей строки.

А точно. Попутал.

Цитата
Могу предположить, что у тебя на поле `nid` нет уникального ключа.

Почему? Есть конечно, по нему и проверяется есть ли уже такая запись.

S.Chushkin
Цитата
С чего ты взял, что он так делает?

С того что создалось такое впечатление, потому что было например 10 INSERT и 20 UPDATE, в таблице должны быть id с 1 по 10, а там фигак и с 1 по 30. Последняя запись под номером 30, а записей всего 10. Все равно что 30 раз сделать DELETE+INSERT.

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2
sergeiss
Цитата (twin @ 9.10.2017 - 18:34)
sergeiss
Немного не так. Дело тут в блокировках.

Категорически не согласен! Блокировки тут не при чем. Мускуль работает именно так, как я описал: для инсерта готовится новое значение уникального поля (следующее значение автоинкрементного поля) и сам автоинкремент увеличивается. Цель простая: во время выполнения вставки может придти еще несколько подобных запросов и каждый из них гарантированно получит уникальный айди (ну или как там программер его обзовёт). А уж что запрос с ним сделает - это его дело. То ли вставит новую строку, то ли проапдейтит существующую забудет про полученное число. Это единственный разумный алгоритм, предоставляющий уникальные числа из автоинкрементного поля.

Когда писал первый ответ, писал полностью по памяти. Сейчас залез в гугл, нашел ссылки на эту тему (там как раз описано всё так, как я сказал с самого начала):
https://habrahabr.ru/post/156489/
https://intsystem.org/sql/neyavnaya-problem...auto-incriment/

Если кому-то мало, можете поискать сами другие ссылки smile.gif Но не надо придумывать ничего "нового" (типа влияния блокировок), когда есть четкое описание определенного поведения.

Цитата (Arh @ 9.10.2017 - 20:56)
того что создалось такое впечатление, потому что было например 10 INSERT и 20 UPDATE, в таблице должны быть id с 1 по 10, а там фигак и с 1 по 30. Последняя запись под номером 30, а записей всего 10. Все равно что 30 раз сделать DELETE+INSERT.

А ты проделай то же самое, но только проверяй результат после каждой операции.

_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
twin
Цитата (Arh @ 9.10.2017 - 16:56)
Почему? Есть конечно, по нему и проверяется есть ли уже такая запись.

Ну потому что ты как то не так описал. smile.gif
Цитата (sergeiss @ 9.10.2017 - 17:26)
Категорически не согласен!
Ты описываешь обычный инсерт. Но запрс с ON DUPLICATE KEY UPDATE, это запрос mixed-mode insert. Другими словами мускул не знает, сколько и чего ему резервировать. Но в MyISAM таблица блочится вся, и по окончании вычислений автоинкремент берется по количеству инсертов. А в InnоDB лочатся только строки. Потому он тупо берет и резервирует номера для всех операций, не важно, будет вставка или нет. В итоге появляются "пробелы". Вставлено 3 строки, апдейтнуто пять, а счетчик инкремента увеличивается на 8. И после id=3 следующий будет id=9.

Если поставить режим innodb_autoinc_lock_mode = 0, то InnоDB будет вести себя как MyiSAM, и разрывов в автоинкременте не будет.

Обрати внимание на настройку - innodb_autoinc_lock_mode. А ты говоришь
Цитата (sergeiss @ 9.10.2017 - 17:26)
Блокировки тут не при чем


Цитата (sergeiss @ 9.10.2017 - 17:26)
Но не надо придумывать ничего "нового" (типа влияния блокировок), когда есть четкое описание определенного поведения.
Вот именно. smile.gif

UPD По твоей же ссылке:
Цитата
Суть в том что любое выполнение этого запроса, вне зависимости от того был INSERT или UPDATE, приводит к увеличению счетчика AUTO INCRIMENT (справедливо только для InnoDB).


Всё потому, что InnoDB работает с таблицами в оперативке. А там другие механизмы блокировок. Кстати, если не лень, можешь провести эксперимент. Сделай такой запрос и ребутни мускул. При следующем запросе пробелов не будет. Потому что в оперативку попадут данные из счетчика таблицы, а там стартовый автоинкремент другой. Как в MyISAM.

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
twin
Цитата (sergeiss @ 9.10.2017 - 17:26)
А ты проделай то же самое, но только проверяй результат после каждой операции.

Так эти все операции в одном запросе!
Впрочем понятно, в постгре нету ON DUPLICATE KEY UPDATE, ты наверно и не сталкивался.

_____________
Если вам недостаточно собственных заблуждений, можно расширить их мнениями экспертов.

Нужно уважать мнение оппонета. Ведь заблуждаться - его святое право.

Настаивал, настаиваю и буду настаивать на своем. На кедровых орешках.

user posted image
Arh
twin
Цитата
Впрочем понятно, в постгре нету ON DUPLICATE KEY UPDATE, ты наверно и не сталкивался.

А как там кстати это решается? В смысле самому проверить, а потом обновить или добавить, или есть какая то хитрая штука, или вообще забыть про эту фичу и не париться?

_____________
Промокод предоставляет скидку на заказ домена и/или хостинга reg.ru
BFCC-3895-8804-9ED2
Быстрый ответ:

 Графические смайлики |  Показывать подпись
Здесь расположена полная версия этой страницы.
Invision Power Board © 2001-2024 Invision Power Services, Inc.