ON DUPLICATE KEY + AUTO INCREMENT issue mysql

У меня есть структура таблицы, подобная этой

когда я вставляю строку в таблицу, я использую этот запрос:

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

когда я сначала вставляю данные без запуска ON DUPLICATE KEY, значение id увеличивается:

но когда триггеры ON DUPLICATE KEY и я INSERT A NEW ROW id выглядит странно для меня:

Как я могу сохранить auto increment, приращение правильно, даже если он вызывает ON DUPLICATE KEY?

Ответ 1

Это поведение задокументировано (абзац в скобках):

Если вы указываете ON DUPLICATE KEY UPDATE, и вставляется строка, приведет к дублированию значения в уникальном индексе или PRIMARY KEY, MySQL выполняет ОБНОВЛЕНИЕ старой строки. Например, если столбец объявлен как UNIQUE и содержит значение 1, следующие два заявления имеют аналогичный эффект:
    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;
(эффекты не идентичны для таблица InnoDB, где a - столбец с автоинкрементом. С автоинкрементный столбец, оператор INSERT увеличивает значение автоинкремента, но не UPDATE.)

Вот простое объяснение. MySQL пытается сначала выполнить вставку. Это когда идентификатор автоматически увеличивается. После увеличения он остается. Затем обнаруживается дубликат и происходит обновление. Но значение упускается.

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

Ответ 2

У меня было такое же разочарование, и у меня есть решение.

Позвольте мне сначала поговорить о "накладных расходах". Когда я впервые написал свой код обновления БД, он выполнил столько отдельных запросов, что занял 5 часов. Как только я поставил "ON DUPLICATE KEY UPDATE", я опустил его примерно до 50 секунд. Удивительно! В любом случае, способ, которым я решил, означает, что мне нужно сделать 2 запроса, поэтому вместо 1 минуты я увеличиваю его до 2 минут, что, я думаю, является справедливой ценой, учитывая

.Сначала я выполнил стандартный SQL-запрос с данными, которые имели обновления и вставки, но я включил "IGNORE", так что это просто обходит обновления и только вставляет новые вещи. "INSERT IGNORE INTO mytablename (stuff, stuff2) VALUES" -without при обновлении дубликата ключа. Это означает, что все новые записи вставляются. Эти новые записи еще не сломают auto_increment.

Затем я выполнил "ON DUPLICATE KEY UPDATE" версию этого оператора SQL, теперь это нарушает значение auto_increment, но все вставленные нами обновления и вставки являются идеальными значениями auto_incrememnt. Просто следующая новая запись, которую мы добавим, будет неправильной...

Итак, наконец, вы просто исправляете беспорядок этим sql: Msgstr "ALTER TABLE mytablename AUTO_INCREMENT =". ($ TableCount + 1);

не забудьте на самом деле установить $ TableCount в таблицу count, затем мы добавляем 1, и это готово для записи следующей записи.

Это дешево и грязно, но мне это нравится. Убедитесь, что вы не пишете в базу данных одновременно, используя другую функцию и т.д., Поскольку это может привести к ошибкам. Я думаю, что есть способ заблокировать стол? Но мне это не нужно в моем случае.

Ответ 3

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE material_item = data, ... hidden = data

Да удалите ID = ID, поскольку он будет автоматически добавлять туда, где PRIMARY KEY = PRIMARY KEY...