MySQL on duplicate key update. Как избавиться от увеличения ID?

Question

[racheev]

Как сделать что бы ID не увеличивалась при обновлении? Вариант не работает, или я не понимаю что происходит внутри.
Насколько я понимаю когда запись вставлена в базу, она получает свой айди, и если попадается запись с таким же хешем, то просто идет обновление хеша а айди остается тем же и счетчик не увеличивается. Но сейчас я вижу что айди с дырками… идет 1 2 3 4 5 8 20 23 24 56 70 и так далее.. Может надо какой-то еще уникальный индекс типа хеша сделать?

INSERT INTO posts (channel_id, message_id, message, hash) 
VALUES 
(:channel_id, :message_id, :message, :hash)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), hash = VALUES(hash)

Comments

[Дмитрий]

зачем вам id без дырок? и зачем вам при duplicate key пытаться менять id?

[racheev]

Дмитрий, без дырок, потому что хочется знать как это сделать. А менять айди я как раз и не хочу.

[Дмитрий]

racheev,
1. ну если не брать варианты не включающие в себя полный и клинический идиотизм - никак.
2. Тогда зачем вам id = LAST_INSERT_ID(id)

[racheev]

Дмитрий, то есть при обновлении любой записи счетчик будет увеличиваться по-любому? А 2 насколько я вычитал не даст обновиться айди. Или я совсем ничего не понял?

[Дмитрий]

racheev, счетчик будет увеличиваться при попытке вставке. по какому полю у вас уникальность проверяется?

[Дмитрий]

https://sqlize.online/sql/mysql57/d2e57d6b9b3ca359...

[racheev]

Дмитрий,

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT 0,
  `pub` int(1) DEFAULT 0,
  `message_id` int(11) DEFAULT 0,
  `message` text DEFAULT NULL,
  `created` timestamp NULL DEFAULT current_timestamp(),
  `hash` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `HASH` (`hash`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3436 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

[Дмитрий]

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

[racheev]

Дмитрий, ну то есть на это заморачиваться не стоит?

[Дмитрий]

racheev, база данных это не excel а id это не номер строки. И просто объясните ЗАЧЕМ ЭТО вам?

[racheev]

Дмитрий, просто интересно, почему они скачут:)))
Ладно, понял! Заморачиваться не буду!
Спасибо за ответ.

[Дмитрий]

racheev, ну представьте себе что у вас с таблицей не один пользователь работает - а 100. и попробуйте реализовать соблюдение порядка в auto_incrementе и так что бы у вас какой нибудь пользователь не столкнулся с тем что он пытается вставить запись уже с существующем id.

После того как реализуете - посмотрите на код, посчитаете сколько оно ресурсов жрет, почешите голову и отправите этот код в корзину.

[Ипатьев]

А зачем здесь вообще on duplicate?

Answer 1

[mayton2019]

Дырки могут быть связаны с механикой кеширования sequence для каждого сеанса mysql.
По сути если 10 сеансов хотят быстро и без конкуренции вставлять в таблицу то они должны
прочитать пачку номеров. Например первый сеанс с 1 по 10 и второй с 11 по 20 и так далее.
В разных БД это реализовано по разному но это в целом - механика оптимизации скорости.
И если сеанс закрылся и недочитал 7, 8, 9 номер то и хер с ним. Диапазон уже был использован
как туалетная бумага.

У тебя-же нет желания доставать из ведра грязную бумагу и еще раз ее использовать?