Слишком много автоматических приращений с ON DUPLICATE KEY UPDATE

У меня есть базовая таблица со столбцами:

  • id (основной с ИИ)
  • имя (уникальное)
  • и т.д

Если уникальный столбец не существует, ВСТАВЬТЕ строку, в противном случае ОБНОВИТЕ строку....

INSERT INTO pages (name, etc)
VALUES
  'bob',
  'randomness'
ON DUPLICATE KEY UPDATE
 name = VALUES(name),
 etc = VALUES(etc)

Проблема в том, что если он выполняет UPDATE, значение auto_increment в столбце id увеличивается. Так что, если выполняется целая куча ОБНОВЛЕНИЙ, id auto_increment проходит через крышу.

Очевидно, это была ошибка: http://bugs.mysql.com/bug.php?id=28781

... но я использую InnoDB на mySQL 5.5.8 на виртуальном хостинге.

Другие люди, у которых возникли проблемы, не имеющие решения несколько лет назад: предотвратить автоинкремент при вставке дубликатов MYSQL и Почему автоинкремент MySQL увеличивается при неудачных вставках?

Идеи по исправлению? Возможно, я как-то неправильно структурировал базу данных?

******EDIT****: Кажется, что добавление innodb_autoinc_lock_mode = 0 в ваш файл my.ini устраняет проблему, но какие у меня есть варианты для виртуального хостинга?

****** РЕДАКТИРОВАТЬ 2 *******: ОК, я думаю, что мой единственный вариант — перейти на MyISAM в качестве механизма хранения. Я надеюсь, что, будучи мега новичком в MySQL, это не вызовет много проблем. Ага?


mysql auto-increment on-duplicate-key
person Nathan Waters    schedule 08.02.2012    source источник
comment
Одна большая проблема с MyISAM заключается в том, что он не поддерживает определение отношений базы данных. InnoDB поддерживает это.   -  person bakkerjoeri    schedule 04.08.2012
comment
innodb_autoinc_lock_mode = 0 у меня сработало. Благодарность   -  person Uğur Özpınar    schedule 06.04.2013
comment
Я переключился на Aria (MyISAM в MariaDB), что предотвращает auto_increment   -  person Daniel W.    schedule 20.06.2013
comment
innodb_autoinc_lock_mode = 0 замедляет работу concurente INSERT из-за использования table-lock для поля auto-inc. Это может быть плохо.   -  person Enyby    schedule 12.08.2016

Ответы (2)


arrow_upward
15
arrow_downward

Я не думаю, что есть способ обойти это поведение INSERT ... ON DUPLICTE KEY UPDATE.

Однако вы можете поместить два оператора, один UPDATE и один INSERT, в одну транзакцию< /а>:

START TRANSACTION ;

UPDATE pages
SET etc = 'randomness'
WHERE name = 'bob' ;

INSERT INTO pages (name, etc)
SELECT 
      'bob' AS name
    , 'randomness' AS etc 
FROM dual 
WHERE NOT EXISTS
      ( SELECT *
        FROM pages p
        WHERE p.name = 'bob'
      ) ;

COMMIT ;
person ypercubeᵀᴹ    schedule 08.02.2012
comment
Спасибо чувак. В итоге я пошел по этому пути. Эта проблема с автоинкрементом - боль в заднице. Не нужно имя AS и части AS и т. д. - person Nathan Waters; 08.02.2012
comment
Нет, эти псевдонимы действительно не нужны. - person ypercubeᵀᴹ; 08.02.2012
comment
@NathanWaters: добавлена ​​ссылка на документацию MySQL о транзакциях. - person ypercubeᵀᴹ; 08.02.2012
comment
Не могли бы вы также сделать это? INSERT IGNORE INTO pages (name, etc) SELECT 'bob' AS name , 'randomness' AS etc FROM dual - person Timo Huovinen; 27.09.2013
comment
@TimoHuovinen INSERT IGNORE записывает идентификатор так же, как ON DUPLICATE KEY UPDATE. - person Damian Yerrick; 20.08.2015
comment
@tepples да. Кроме того, в зависимости от настроек, он также будет игнорировать другие ошибки. Что обычно не есть хорошо. - person ypercubeᵀᴹ; 20.08.2015

arrow_upward
-3
arrow_downward

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

person PFY    schedule 08.02.2012
comment
это не так быстро, судя по тому, что я вижу - person Timo Huovinen; 27.09.2013